Home > ADO.NET, C# > Reading leading Zeros from Text files with OLEDB

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: , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: