CSV column data is being truncated

Question

I am parsing a CSV file using Jet with the connection string

@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + directory + ";Extended Properties='text;HDR=YES;FMT=Delimited';"

with a Schema.ini containing:

[SelfCatering.csv]
Format=CSVDelimited
ColNameHeader=True
MaxScanRows=0

In my c# code I have predefined the column data types as string but for some reason extended string columns are being truncated at 255 characters. If I quick watch the column data itself is it only 255 letters long

Where else would it be getting truncated?

Thanks

Answer 1

You should try Datatype LongChar I had success with it.

Answer 2

Dont use Jet OLEDB 4.0 driver, it will truncate your long text to 255 chars and it will read only 255 columns/fields. If you have more than 255 columns/fields you will not be able to read them all.

Other people on StackOverflow suggests to use: A Fast CSV Reader

Answer 3

Have you tried specifying the field types in your Schema.ini file:

<SelfCatering.csv>
   You can add comments here
</SelfCatering.csv>

<schema.ini>
[SelfCatering.csv]
ColNameHeader=TRUE
MaxScanRows=0
Format=CSVDelimited
Col1=ColName1 Text Width 50
Col2=ColName2 Text Width 50
Col3=ColName3 Text Width 500
</schema.ini>

Answer 4

Don't specify MaxScanRows=0. It is not working for some reason. Tried MaxScanRows=1000. It is working. or alternatively try edit registry

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Text] "MaxScanRows"=dword:00001000