Archive for December, 2009

C# Read Tab or Comma Delimited File to Dataset

Schema File:

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:

[filename.txt]

Format=TabDelimited

You can add multiple file references, so that will become:

[myfile1.txt]

Format=TabDelimited

[myfile2.txt]

Format=TabDelimited

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 + “]”);

        sw.WriteLine(“Format=TabDelimited”);

        sw.Close();

    }

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();

        string conn_csv_str;

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

        string sql_select;

        OleDbConnection obj_oledb_con;

        OleDbDataAdapter obj_oledb_da;

        obj_oledb_con = new OleDbConnection(conn_csv_str);

        obj_oledb_con.Open();

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

        obj_oledb_da = new OleDbDataAdapter(sql_select, obj_oledb_con);

        obj_oledb_da.Fill(ds);

        obj_oledb_con.Close();

        return ds;

    }

Reference

http://social.msdn.microsoft.com/Forums/en/vblanguage/thread/f4abe22c-db28-4f3b-8c6d-50bb05b2e583

Leave a comment