Reading Comma Delimited Text File to C# DataTable, columns get truncated to 255 characters

Question

We are importing from CSV to SQL. To do so, we are reading the CSV file and writing to a temporary .txt file using a schema.ini. (I'm not sure yet exactly why are are writing to this temporary file, but that's how the code currently works). From there, we are loading a DataTable via OleDB using the following connection string (for ASCII files).

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sPath + ";Extended Properties=\"text;HDR=Yes;FMT=Delimited\"";

The problem we are having is that fields with more than 255 characters get truncated. I've read online about this problem and it seems that by default, text fields get truncated thusly.

I set my registry settings ImportMixedTypes=Majority Type and TypeGuessRows=0 in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel , hoping that mycolumns will no longer be interpreted as text. After doing that, the temporary txt file is being written correctly from the CSV file, but when I call dataAdapter.Fill, the resulting DataTable still has a truncated value.

Here is the column definition in question. CommaDelimited#txt Notes 2 false 234 true 130 0 0

Any help would be appreciated. At this time, I'm not interested in using any 3d party code to solve this problem, there must be a way using built in tools.

Here is the table definition:

<Columns> 
    <TABLE_NAME>CommaDelimited#txt</TABLE_NAME> 
    <COLUMN_NAME>Notes</COLUMN_NAME> 
    <ORDINAL_POSITION>2</ORDINAL_POSITION> 
    <COLUMN_HASDEFAULT>false</COLUMN_HASDEFAULT> 
    <COLUMN_FLAGS>234</COLUMN_FLAGS> 
    <IS_NULLABLE>true</IS_NULLABLE> 
    <DATA_TYPE>130</DATA_TYPE> 
    <CHARACTER_MAXIMUM_LENGTH>0</CHARACTER_MAXIMUM_LENGTH> 
    <CHARACTER_OCTET_LENGTH>0</CHARACTER_OCTET_LENGTH> 
</Columns>

Thanks,

Greg

---

I tried editing the schema.ini specifying text with a width, and that did not help (it was set to memo before)

[CommaDelimited.txt] Format=CSVDelimited DecimalSymbol=. Col1=Notes Text Width 5000

Solution

The Jet database engine truncates memo fields if you ask it to process the data based on the memo: aggregating, de-duplicating, formatting, and so on.

http://allenbrowne.com/ser-63.html