Fog Creek Software
Discussion Board




Welcome! and rules

Joel on Software

OLEDB Truncates CSV Columns to 255 Chars?

I'm using OLEDB to read from a CSV file and process it like a DBMS table. Any columns it reads, though, are truncated to 255 characters -- this is quite frustrating and not at all clear why this is happening. Switching the provider to Excell still results in the same truncation (even with the little hints that Google seems to tell me will make it work).

Here's my connection string and query:
OleDbConnection conn = new OleDbConnection( @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\foo.csv;Extended Properties=Text;" ); 
OleDbCommand     cmd = new OleDbCommand( "SELECT * FROM [foo.csv]", conn );
OleDbDataReader dr = cmd.ExecuteReader( );

while( dr.Read( ) )
{
  // stuff
} // end while

Any ideas?

Thanks!!

Captain McFly
Tuesday, September 07, 2004

s/Excell/Excel

Captain McFly
Tuesday, September 07, 2004

The problem is that the Jet driver sees the columns as being the Text data type, and Text in Jet is limited to 255 characters. You need to tell Jet to treat the columns in question as Memo columns instead, and the way to do this is to include a schema.ini file in the same directory as the file that you're importing. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp will show you how to construct such a file.

Mike Gunderloy
Tuesday, September 07, 2004

Ran into this same exact problem before. Exactly, use Memo type.

Li-fan Chen
Thursday, September 09, 2004

Thanks for the reply. I'll experiment with the schema.ini -- the problem is that the file changes randomly and I guess I'll have to change the schema.ini at that time as well.

Captain McFly
Friday, September 10, 2004

*  Recent Topics

*  Fog Creek Home