Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VB6 & Reading CSV file with data having spaces

Status
Not open for further replies.
sam4help

sam4help

Programmer
Jul 22, 2011
74
AE
Dear All,

I am into trouble while reading .csv file having spaces.

I am reading csv file with below code;
SAMPLE INPUT FILE:

AAAA,43438894322,USA,BASIC
BBBB,73738 9393 939,INDIA,BASIC
CCCC,00293993020,UK,PREMIUM

all is well until i get any file with data having space like
01 0002 020929
65650002 543

Code: 
Dim connCSV As New ADODB.Connection
Dim rsCSV As New ADODB.Recordset
     
connCSV.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
     & Path & ";Extended Properties='text;HDR=" & hasHeader & ";FMT=Delimited'"
    
rsCSV.Open "Select * From " & fileName, _
    connCSV, adOpenStatic, adLockReadOnly, adCmdText
          Do While Not rsCSV.EOF()
                 cntRows = cntRows + 1
                     With MSFlexGrid1
                          currRow = .Rows - 1
                         
                         .TextMatrix(currRow, 1) = IIf(IsNull(rsCSV.Fields(0).Value), "", rsCSV.Fields(0).Value)
                         .TextMatrix(currRow, 2) = rsCSV.Fields(1).Value
                         .TextMatrix(currRow, 3) = Format(rsCSV.Fields(2).Value, "####.000")
                         .TextMatrix(currRow, 4) = PadRight(Left(rsCSV.Fields(3).Value, 8), "XXXXXXXX")
                         .TextMatrix(currRow, 5) = IIf(IsNull(rsCSV.Fields(4).Value), "", rsCSV.Fields(4).Value)
                         .TextMatrix(currRow, 6) = rsCSV.Fields(5).Value
                         .TextMatrix(currRow, 7) = rsCSV.Fields(6).Value
                         
                         .Rows = .Rows + 1
                     End With
             rsCSV.MoveNext
             Loop

The place where its 01 0002 020929 it is returning NULL where as there is data available. Same if its alphanumeric like OXFORD UNIVERSITY it returns correct, but in case of numbers it returns NULL.

Any help will be appreciated,

Best Regards,

Sam
 
Sort by date Sort by votes
Try changing this:

[tt]connCSV.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& Path & ";Extended Properties='text;HDR=" & hasHeader & ";FMT=Delimited'"[/tt]

to this:

Code: 
connCSV.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
     & Path & ";Extended Properties='text;HDR=" & hasHeader & ";FMT=Delimited'[!];IMEX=1[/!]"

IMEX=1 indicates that columns with mixed data types should be treated as strings.

The problem here is that some of your data in that column can be interpreted as numeric, but not all of it. So, the Jet engine assumes that the column is numeric. When it encounters data that is not numeric (like your numbers with spaces), it cannot convert it so it returns NULL.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Thanks George for your reply,

After changing It gives error Could not find installable ISAM.
 
Upvote 0 Downvote
Sorry. The formatting was a little wonky. Try this:

Code: 
connCSV.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
     & Path & ";Extended Properties='text;HDR=" & hasHeader & ";FMT=Delimited;IMEX=1'"

Basically, the single-quote needed to be at the end, after IMEX instead of before it.

For more info, take a look here:
-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
now the error has gone but the NULL issue is still there, it is returning NULL again :(

Best Regards,

Sam
 
Upvote 0 Downvote
and yes, the link you gave me was for excel is that any problem?
 
Upvote 0 Downvote
There is a registry setting buried deep that indicates the number of rows to look at when "guessing" the data type for each column. The default (I think) is to look at the first 8 rows. I also think you can override this setting in the connection string, like this:

Code: 
connCSV.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
     & Path & ";Extended Properties='text;HDR=" & hasHeader & ";FMT=Delimited;IMEX=1[!];ImportMixedTypes=Text;TypeGuessRows=0;[/!]'"

Note that setting the TypeGuessRows to 0 actually causes the provider to scan all of the rows to determine the data type, and then goes back through them to load the data. If your CSV file is very large, setting TypeGuessRows=0 will cause a noticeable performance delay.

I suppose it's worth a try.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
and yes, the link you gave me was for excel is that any problem?
Click to expand...

Nope. No problem at all. The problems you are having with the CSV file are the same issues you would have with an excel file.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
I tried but, it gives following error:
Format of the initialization string does not conform to the OLE DB specification.

I removed ; from last making it FMT=Delimited;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=0'

I get again the NULL error.

Best Regards,

Sam
 
Upvote 0 Downvote
Hi George the problem I see is its not able override the registry setting. I went to Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Text and changed the [bold]ImportMixedTypes[/bold] entry to [bold]Text[/bold]. Now it works fine, any idea how to override this setting without editing registry ?

Best Regards,

Sam
 
Upvote 0 Downvote
Sorry Formatting Problem :)

Hi George the problem I see is its not able override the registry setting. I went to Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Text and changed the ImportMixedTypes entry to Text. Now it works fine, any idea how to override this setting without editing registry ?

Best Regards,

Sam
 
Upvote 0 Downvote
Unfortunately, I'm not too sure. We changed the connection string to use ImportMixedTypes=Text, so I am surprised that changing the registry value made any difference.

Personally, I would try to avoid any solution that requires a change to the registry because "there be dragons". There are security problems with changing the registry on modern operating systems. It's best to avoid this if possible.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Understandable, but again thats what I am looking at if we can do this without editing the registry. But, if not then I need to go with the registry editing thing as solution has to be provided where this data import is required.

Best Regards,

Sam
 
Upvote 0 Downvote
The alternative that I would pursue is to open the file without using ADO or recordsets. Instead, you could open the file, read line-by-line, parse each line on the commas, and load in to your flex grid.

This will likely require a little more code, and performance may be affected, but probably not too much.

For this, you'll want to look at the FreeFile function, Open, Input Line, and also the SPLIT function (to split each line on the comma).

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
ok, but the problem i will face in this is I need few criteria based queries for the recordset and some manipulation which will b problematic in reading line by line instead into recordset.

Best Regards,

Sam
 
Upvote 0 Downvote
You need a schema.ini file

Create a another text file in the same location as the file you are importing. Rename it schema.ini, and then insert something like

[example.csv]
ColNameHeader=False
Format=CSVDelimited
Col1=F1 Text
Col2=F2 Text
Col3=F3 Text
Col4=F4 Text

into it (assuming the file your are importing from is called example.csv), and change

connCSV.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& Path & ";Extended Properties='text;HDR=" & hasHeader & ";FMT=Delimited'"

to

connCSV.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& Path & ";Extended Properties='text'"


Should do the trick

 
Upvote 0 Downvote
I have used code like that shown below with no problems

First of all create sample CSV file
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\WINDOWS\system32>cd c:\qtcaps

C:\qtcaps>type tom.csv
field1,field2
1 2 3 4 , Line 1
Test Line 2 , Line 2
5 6 7 , Line 3
C:\qtcaps>

I created a DSN called Text Files based on the Microsoft text Driver (*.txt; *.csv)

Next opened XL and created the following macro

Sub test()
Set dbcomboindex = New Connection
dbcomboindex.CursorLocation = adUseClient
dbcomboindex.Open "PROVIDER=MSDASQL;dsn=Text Files;uid=;pwd=;database=;"

Set adocomboindex = New Recordset
adocomboindex.Open "select field1,field2 from c:\qtcaps\tom.csv", dbcomboindex, adOpenStatic, adLockOptimistic

If adocomboindex.RecordCount < 1 Then
MsgBox "Data error reading CSV data, please contact support"
Exit Sub
End If


adocomboindex.MoveFirst
For i = 0 To adocomboindex.RecordCount - 1
Debug.Print adocomboindex.Fields.Item(0).Value & "," & adocomboindex.Fields.Item(1).Value
adocomboindex.MoveNext
Next

End Sub

Output was as expected:-


1 2 3 4,Line 1
Test Line 2,Line 2
5 6 7,Line 3




In order to understand recursion, you must first understand recursion.
 
Upvote 0 Downvote
The problem there is that your file doesn't suffer the same problermm sd as the OP#s's original file, and therefore doesn't need special handling (the OP's code quite happily reads your csv with no problems with nulls). On the other hand your code doesn't work with CSV examples that the OPs code doesn't work with.

Of course, if you select the Define Format option when creating (or modifying) your DSN you can get it to work. But that's because it creates a schema.ini ...
 
Upvote 0 Downvote
Er ... where the solution was a schema.ini file. Which we had already suggested here.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

2009luca
  • Locked
  • Question
graph with access database - im on vb6 2
Replies
10
Views
392
strongm
strongm
sal21
  • Locked
  • Question
DOWNLOAD csv file from web
Replies
5
Views
163
strongm
strongm
Ken01
  • Locked
  • Question
I have a program, developed in VB6
Replies
6
Views
346
Ken01
Ken01
Swi
  • Locked
  • Question
API - multipart/form-data
Replies
3
Views
330
Swi
Swi
gmmastros
  • Locked
  • Question
VB6 Post to webservice with MSXML2 error
Replies
4
Views
559
dilettante
dilettante

Part and Inventory Search

Sponsor

Back
Top