Efficient function for reading a delimited file into DataTable

Question

I was wondering if anyone knew of an efficient c# function for reading a tab delimited file into a datatable?

Thanks

Pericles

Answer 1

This currently uses the LINQ methods .First() and .Skip() both are easy to recreate if you need to use this on .Net 2.0

//even cooler as an extension method
static IEnumerable<string> ReadAsLines(string filename)
{
    using (var reader = new StreamReader(filename))
        while (!reader.EndOfStream)
            yield return reader.ReadLine();
}

static void Main()
{
    var filename = "tabfile.txt";
    var reader = ReadAsLines(filename);

    var data = new DataTable();

    //this assume the first record is filled with the column names
    var headers = reader.First().Split('\t');
    foreach (var header in headers)
        data.Columns.Add(header);

    var records = reader.Skip(1);
    foreach (var record in records)
        data.Rows.Add(record.Split('\t'));
}

Pantin

Answer 2

public System.Data.DataTable GetDataTable(string strFileName)
{
    System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + System.IO.Path.GetDirectoryName(strFileName) + ";Extended Properties = \"Text;HDR=YES;FMT=TabDelimited\"");
    conn.Open();
    string strQuery = "SELECT * FROM [" + System.IO.Path.GetFileName(strFileName) + "]";
    System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn);
    System.Data.DataSet ds = new System.Data.DataSet("CSV File");
    adapter.Fill(ds);
    conn.Close();
    return ds.Tables[0];
 }

Russell

Is there a way to do this without Jet? For those of us with machines where Access is forbidden. – Immortalize

Answer 3

Here's one way to do it...

        var dt = new DataTable();
        dt.Columns.Add(new DataColumn("Column1", typeof(string)));
        dt.Columns.Add(new DataColumn("Column2", typeof(string)));
        dt.Columns.Add(new DataColumn("Column3", typeof(string)));

        var lines = File.ReadAllLines(@"c:\tabfile.txt");
        foreach( string line in lines )
            dt.Rows.Add(line.Split('\t'));

Mendive

if the file is really big you are creating 2 copies of the same big file in memory – Russell

Yeah, I should have mentioned that. I wanted to keep the example simple. A stream reader would be more appropriate if the files would be large. – Mendive

this is perfect i used this for my own project – Poll

Answer 4

DataTable ConvertToDataTable(string filePath, int numberOfColumns)
{
    DataTable tbl = new DataTable();

    for (int col = 0; col < numberOfColumns; col++)
        tbl.Columns.Add(new DataColumn("Column" + (col + 1).ToString()));


    string[] lines = System.IO.File.ReadAllLines(filePath);

    foreach (string line in lines)
    {
        var cols = line.Split(' ');

        DataRow dr = tbl.NewRow();
        for (int cIndex = 0; cIndex < 3; cIndex++)
        {
            dr[cIndex] = cols[cIndex];
        }

        tbl.Rows.Add(dr);
    }

    return tbl;
}

Pacian

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Scorecard