CSV Files and MS Text Driver Error

[SteveG]

Has any one had any problems, using the Microsoft Text Driver to parse a CSV file.

This driver seems to work ok, until it hits a certain row (same row everytime), and then ADO will not get a value from a field in the row in the CSV. I have checked the watch window, and ADO is certain that the value is not there, and replaces it with NULL. If however I move this row to the beginning of the CSV file, and run the program again, it all works fine.

We have loaded the CSV file into notepad, and there appears to be no funny characters around it.

Has anyone had this problem before, or know of a fix for it.


oConnFile.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=c:\steve\;" & _
"Extensions=asc,csv,tab,txt;"

oRsFile.CursorLocation = adUseClient
oRsFile.Open "Select * From db1.csv", oConnFile, , , adCmdText

Any help would be great.

Steve

[mel_flynn]

Try opening it in Wordpad. I was having a similar problem before and noticed that Wordpad picked up foreign characters (e.g. Carriage return character) and note pad didn't.

[SteveG]

Ok, I have finally fixed this problem, just thought I would share it with everyone, incase other people have the same problem.

The problem is that the Text Driver, scans the first 25 rows of the csv file (setting in the Reg), and from this it determines what each column's data type is.

Now the problem I had was that the first 80 odd rows contained a numeric value in this particular column, and so instead of ADO falling over when it found the string it just inserts NULLs, so that is why by sorting the csv file into a different order the problem was fixed.

Now the best fix for this is to use a schema.ini file in the same directory as the CSV file. I built one of these using the file.dsn but where it has a field for Maxrows=25 (this is the field that tells it how many rows to scan to determine the column datatype), set this to 0 and it scans all rows and builds you up a good schema.ini file.

I hope this will help some people out and stop them pulling their hair out.

Steve.