The Old Joel on Software Forum: Part 3 (of 5) - .NET OLEDB Data Reader Datatype is VARCHAR(255)?

.NET OLEDB Data Reader Datatype is VARCHAR(255)?

Hi,

I'm writing a C# application which processes CSV files. In order to keep things simple I don't want to write my own parsing logic to deal with the CSV format.

So, I use JET/OLEDB to "SELECT *" from the CSV file then use a datareader to go through each row/column and do stuff with it. The problem that I'm seeing is that the datatype appears to be VARCHAR( 255 ) -- any columns larger than 255 characters are silently truncated. How can I fix this?

Abridged code below:
OleDbConnection conn = new OleDbConnection( @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\foo;Extended Properties=Text;" );
OleDbCommand cmd = new OleDbCommand( "SELECT * FROM [bar.csv]", conn );

conn.Open( );

OleDbDataReader dr = cmd.ExecuteReader( );

while( dr.Read( ) )
{
for( int i = 0; i < dr.FieldCount - 1; i++ )
MessageBox.Show( dr[ i ].ToString( ) );
} // end while

if dr[ i ] was greater than 255 characters in the CSV file it is truncated to 255.

MR
Thursday, December 11, 2003

MR,

It seems you have to in this case, to overcome the 255 char limitation. Judging from what you are telling in your post the limitation seems to be within the DataReader object.

But then again CSV files are easily parsed.

Patrik
Thursday, December 11, 2003

I don't think it is a limitation of Datareader, it is a limitation if the Jet engine. I recall importing text files into MS Access and the column max is 255.

DJ
Thursday, December 11, 2003