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

http://msdn.microsoft.com/en-us/library/ms709353(v=vs.85).aspx

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:

AddTXTFileSchema(“C:\my_file_directory\”,”schema.ini”,”helloworld.txt”)

 

 

publicvoid AddTXTFileSchema(string filepath, string schemafilename, string
filename)
{

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

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

 

 

sw.Close();
}

 

 

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

filename){

DataSet ds = newDataSet

(); 

string

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

string

sql_select; 

OleDbConnection

obj_oledb_con; 

OleDbDataAdapter

obj_oledb_da;obj_oledb_con = newOleDbConnection
(conn_csv_str);
obj_oledb_con.Open();
sql_select = “select * from [“ + filename + “]”

; 

obj_oledb_da =

newOleDbDataAdapter (sql_select, obj_oledb_con); 

obj_oledb_da.Fill(ds);

obj_oledb_con.Close();

return ds;

}

 

// Parse Text File with No Header
 
 
 
Advertisements
  1. Leave a comment

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: