ADO recordset returning only nulls

[stefankemp]

Hi, I am trying to query a csv file using ADO. The procedure worked fine in the past, but I have recently received a new csv file that it doesn't want to read from. The only difference I can find between the old and new csv file is that if I open the old file in Notepad, the text just wraps around with no line breaks. So it would look something like this:

Line1Item1;Line1Item2;Line1Item3Line2Item1;Line2Item2;Line2Item3

Whereas the new file looks like this:

Line1Item1;Line1Item2;Line1Item3
Line2Item1;Line2Item2;Line2Item3

As you can see this is semi-colon delimited so I have used a schema.ini file to specify it. I find it strange that it reads the old file fine but not the new one. For the new file it returns a recordset with twice the actual number of records, and all the values are null.

Has anybody come across this behaviour before? Any ideas?

 

[jimrward]

If you import the second file into excel, does it show any strange character at end of line, you could test the character using the ASC function to get the ASCII code

Can you post your schema file definition that you use

 

[stefankemp]

If you import the second file into excel, does it show any strange character at end of line, you could test the character using the ASC function to get the ASCII code

Can you post your schema file definition that you use

Hi, here it is:

[Filename.csv]
Col1=SomeField Long
Col2=SomeField Text
Col3=SomeField Text
Col4=SomeField Text
Col5=SomeField Text
Col6=SomeField Text
Col7=SomeField text
Col8=SomeField Text
Col9=SomeField text
Col10=SomeField Long
Format=Delimited(

I can open the file manually in Excel without any problem.

 

[jimrward]

providing the data is not sensitive, can you email me the 2 text files, I will send you my email address via personal message, I can only think it is the end of line characters

 

[xenou]

^^ Twas my thought too. If you view the file in a good text editor you can view the EOL characters.

For example, with NotePad++

• Open the file
• then choose View | Show Symbol | Show End of Line and that would display either CRLF, CR, or LF

Notepad++ would also, by the way, let you to convert the file to a different line ending, which might fix this (again using np++):

• Open the file
• Choose Edit | EOL Conversion and try either Windows or Unix depending on what the file already is

Edit: that is, unless your end of line characters have gone missing: Line1Item1;Line1Item2;Line1Item3Line2Item1;Line2Item2;Line2Item3 doesn't look right at all but it may be that your program just isn't reading them.