Archive

Posts Tagged ‘ADO.NET’

Reading leading Zeros from Text files with OLEDB

OLEDB allows us to read large text files, exactly like reading tables from DataBase. For example, using this connection string, you can simply create connection to CSV file:

@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=""[Your Directory]"";Extended Properties=""text;HDR=No;FMT=Delimited"";

and then reading it with this command:

"SELECT * FROM [FileName]"

Not only .csv files applicable with this Format, you can also set the delimiter to other values, as Tab.

Suppose you have this simple .csv file:

ColumnA,ColumnB,ColumnC
0001,12,13
00002,15,16

When reading this file to DataTable using OleDbDataAdapter, the leading zeros for the first column will be omitted, since the DataTable recognize this column as an Integer column, and there is no meaning for leading zeros. In order to tell the DataTable to treat this column as a string column, just add file called schema.ini in the same directory of your .csv file, with the following content:

[YourFileName.csv]
ColumnA= Text
ColumnB=Integer
ColumnC=Integer

Now the DataTable will read the first column as string. More options for the schema.ini file you can find here: Much ADO About Text Files

Advertisements
Categories: ADO.NET, C# Tags: , ,
%d bloggers like this: