When parsing CSV using OleDbConnection, trailing spaces are trimmed

[Seth]

First off, my apologies if this is in the wrong newsgroup, but I hope
I'm close enough.

I'm trying to do some parsing of a CSV file using OleDbConnection, but
for some reason, when I populate my DataSet, it is trimming the trailing
spaces. Anybody know why?

Here is my code:

System.Data.OleDb.OleDbConnection connection = null;

try
{
string CSVFile = @"C:\Temp\CSVTest.csv";

connection = new System.Data.OleDb.OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
System.IO.Path.GetDirectoryName( CSVFile ) + @";Extended
Properties=""Text;HDR=YES;FMT=Delimited""" );

System.Data.OleDb.OleDbCommand command = new
System.Data.OleDb.OleDbCommand( "SELECT * FROM " + CSVFile, connection );

System.Data.OleDb.OleDbDataAdapter adapter = new
System.Data.OleDb.OleDbDataAdapter( command );

System.Data.DataSet dataSet = new System.Data.DataSet();

connection.Open();
adapter.Fill( dataSet, "TableName" );

for( int i = 0; i < dataSet.Tables[ "TableName" ].Rows.Count; i++ )
{
System.Data.DataRow currentRow = dataSet.Tables[ "TableName"
].Rows[ i ];

for( int j = 0; j < currentRow.ItemArray.Length; j++ )
{
System.Diagnostics.Trace.WriteLine( String.Format( "[{0}][{1}] =
|{2}|", i, j, currentRow[ j ] ) );
}
}
}
catch( Exception exception )
{
MessageBox.Show( exception.ToString() );
}
finally
{
if( connection != null )
{
connection.Close();
}
}

Here is my CSVTest.csv:

1,2,3,4,5,6
" ""z""y"" ","b,",c,"\
",\,"""\"""
d," e ", f ,,,

Here is my output:

[0][0] = | "z"y"|
[0][1] = |b,|
[0][2] = |c|
[0][3] = |\
|
[0][4] = |\|
[0][5] = |"\"|
[1][0] = |d|
[1][1] = | e|
[1][2] = |f|
[1][3] = ||
[1][4] = ||
[1][5] = ||

Here is the output I would expect:

[0][0] = | "z"y" | <-- trailing space on end
[0][1] = |b,|
[0][2] = |c|
[0][3] = |\
|
[0][4] = |\|
[0][5] = |"\"|
[1][0] = |d|
[1][1] = | e | <-- 2 trailing spaces on end
[1][2] = | f | <-- 2 trailing spaces on beginning and end
[1][3] = ||
[1][4] = ||
[1][5] = ||

 

[Paul Clement]

¤
¤ First off, my apologies if this is in the wrong newsgroup, but I hope
¤ I'm close enough.
¤
¤ I'm trying to do some parsing of a CSV file using OleDbConnection, but
¤ for some reason, when I populate my DataSet, it is trimming the trailing
¤ spaces. Anybody know why?
¤

I believe the Text driver strips leading and trailing spaces during conversion.
You can prevent it from trimming leading spaces if you define the file format in
a schema.ini file (and the width of the field).

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

I'm not aware of any method for saving trailing spaces.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Seth]

I believe the Text driver strips leading and trailing spaces during conversion.

Is there a different driver I could use?

You can prevent it from trimming leading spaces if you define the file format in
a schema.ini file (and the width of the field).

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

Unfortunately, I don't think this is an option as the CSV I will
eventually be parsing will have variable width fields.

I'm not aware of any method for saving trailing spaces.

Bummer. Wonder why that is...

 

[Paul Clement]

¤ Paul Clement wrote:
¤ > I believe the Text driver strips leading and trailing spaces during conversion.
¤
¤ Is there a different driver I could use?

Not that I am aware of. The alternative would be to use the System.IO namespace and string functions
to split on the command separator.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)