How to Read text file to DataTable [closed]

I would like to extract data from a txt file and put it into a DataTable. The content in the txt file is in the following format:

sometext1:sometext2:sometext3
sometext4:sometext5:sometext6
sometext7:sometext8:sometext9
...

Each line represents a row and every column is separated with ":" character.

I tried doing this:

            DataTable tbl = new DataTable();

            using (StreamWriter sw = File.CreateText(path))
            {
                string[] rows = content.Split('\n');
                foreach (string s in rows)
                {
                    string[] columns = s.Split(':');
                    foreach (string t in columns)
                    {
                        sw.WriteLine(t);
                    }

                }
            }

How can I read this file and add it to DataTable?

Solution 1:

This is a simple method to do your job

public 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;
}

Solution 2:

Here's an excellent class that will copy CSV data into a datatable using the structure of the data to create the DataTable:

http://www.codeproject.com/Articles/11698/A-Portable-and-Efficient-Generic-Parser-for-Flat-F

This topic has also been discussed here: How to read a CSV file into a .NET Datatable

However, if you are willing to write the code yourself, there is an example:

Dim csvFileFolder As String = "C:\YourFileFolder"
Dim csvFileName As String = "YourFile.csv"

'Note that the folder is specified in the connection string,
'not the file. That's specified in the SELECT query, later.
Dim connString As String = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" _
    & csvFileFolder & ";Extended Properties=""Text;HDR=No;FMT=Delimited"""
Dim conn As New Odbc.OdbcConnection(connString)

'Open a data adapter, specifying the file name to load
Dim da As New Odbc.OdbcDataAdapter("SELECT * FROM [" & csvFileName & "]", conn)
'Then fill a data table, which can be bound to a grid

Dim dt As New DataTableda.Fill(dt)

It's in VB.NET, if you can't translate it to C# let me know.

Regards.

Solution 3:

Well, you presumably have your data right here:

string[] rows = content.Split('\n');
foreach (string s in rows)
{
    string[] columns = s.Split(':');
    foreach (string t in columns)
    {
        // each data element
    }
}

If you have a DataTable defined, you can add rows to it with a very similar construct. I don't know the structure of your table, but essentially you can do this:

string[] rows = content.Split('\n');
foreach (string s in rows)
{
    string[] columns = s.Split(':');
    var dataRow = someDataTable.NewRow();

    dataRow["someColumnName"] = columns[0];
    dataRow["anotherColumnName"] = columns[1];
    dataRow["someOtherColumnName"] = columns[2];
    // and so on...

    someDataTable.Rows.Add(dataRow);
}

If the DataTable's columns aren't strongly named, you can also add items by index:

dataRow.Item[0] = columns[0];
dataRow.Item[1] = columns[1];
// and so on...