Archive for December, 2009
Before you use DataAdapter, there is a catch – the driver (Jet.OLEDB) has default value for delimiter, in my case, it is comma, it will not recognize tab delimited file even you specify “FMT=TabDelimeted” in OLEDB connection string,
To change it, you can edit windows registry (see the url reference in this post), but sometimes you need to read comma delimited file too, so this is not a real solution.
Another solution is to add a reference to “schema.ini” file, which is located in the file uploaded folder (same folder as the file you are going to parse).
Format will be:
You can add multiple file references, so that will become:
For those Comma Delimited files, you can just skip this step.
Code to add file reference
// If text file is Tab Delimited, add file reference to “schema.ini” file
if (rblDelimitedOption.SelectedValue == “1”)
cs.AddTXTFileSchema(saveFilePath, “schema.ini”, saveFileName);
public void AddTXTFileSchema(string filepath,string schemafilename, string filename)
FileStream fs = new FileStream(filepath + “/” + schemafilename, FileMode.Append, FileAccess.Write);
StreamWriter sw = new StreamWriter(fs);
sw.WriteLine(“[” + filename + “]”);
Now you can retrieve the dataset from Tab or Comma delimited file
OLEDB to fill in dataset:
/// Parse a TXT file, retrive all content and return as dataset. It is assumed the first row of csv file is the header.
public DataSet ParseTXTFileWithHeader(string filepath, string filename)
DataSet ds = new DataSet();
conn_csv_str = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + filepath + “;Extended Properties=’text;FMT=Delimited;HDR=YES'”;
obj_oledb_con = new OleDbConnection(conn_csv_str);
sql_select = “select * from [” + filename + “]”;
obj_oledb_da = new OleDbDataAdapter(sql_select, obj_oledb_con);