Null Value in first row causes data lost in whole dataset – Provider=Microsoft.Jet.OLEDB.4.0

I have a Comma delimited text file wit no header, the first row is the first data record. I am using OleDB to receive a dataset

conn_csv_str = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + filepath + “;Extended Properties=’text;FMT=Delimited;HDR=No’;


I found if I have null value in the first row, for example in addressline column, the rest of the records will be null in addressline column as well!

After some reseach I found the answer on MS website

The Oledb driver is trying to determine the data type of each column, it must get confused by null value in the first row, so it ignore values for the rest of the rows.

The solution is to create a schema.ini file in the same directory with your text file, and before you retrive dataset, add configuration lines into schema.ini file. 

for example, if my file is called helloworld.txt, there are 4 columns in my file: firstname, lastname, adressline,age

I will call below method:




publicvoid AddTXTFileSchema(string filepath, string schemafilename, string

FileStream fs = newFileStream(filepath + “/” + schemafilename, FileMode.Append, FileAccess

StreamWriter sw = newStreamWriter
sw.WriteLine(“[“ + filename + “]”
sw.WriteLine(“Col1=c_firstname Text”
sw.WriteLine(“Col2=lastname Text”
sw.WriteLine(“Col3=addressline Text”
sw.WriteLine(“Col4=age Short”);






notice you can also specify the delimiter (tab, csv, customer etc), after [filename] line, it is very handy if you are using customer delimiter for example  pipe “|”.

The content of schema.ini will keep grouping as you handle more files, it is also a good idea to do housekeeping to clear schema.ini content once a while.

After you add entries to schema.ini file, you can use oledb to retrieve dataset .





publicDataSet ParseTXTFileWithNoHeader(string filepath, string


DataSet ds = newDataSet



conn_csv_str;conn_csv_str = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + filepath + “;Extended Properties=’text;FMT=Delimited;HDR=No'”;






obj_oledb_da;obj_oledb_con = newOleDbConnection
sql_select = “select * from [“ + filename + “]”


obj_oledb_da =

newOleDbDataAdapter (sql_select, obj_oledb_con); 



return ds;



// Parse Text File with No Header

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s