How to Read text file to DataTable

Question

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?

The only tag you've included in the question has nothing to do with the question you've asked. What is a `DataTable`? What language are you programming in? What attempt have you made to solve this? — David, Dec 31 '13 at 16:16
I'm not sure what you want to do. Do you want to read the -file- and insert the data into a database, or do you want to read the -database- and write the data into a file? I'm asking this because you say you want to extract data from a file, but your code sample appears like your writing to a file — Ricardo Appleton, Dec 31 '13 at 17:33

score 18 · Accepted Answer · answered Dec 31 '13 at 17:47

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

If the text file has more than 3 columns then line "for(int cIndex=0; cIndex < 3; cIndex++)" needs to be changed to "for(int cIndex=0; cIndex < numberOfColumns; cIndex++)" otherwise columns 4,5, etc. will always be empty — user797717, Mar 30 '19 at 15:25

score 4 · Answer 2 · edited May 23 '17 at 10:29

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.

I have a question. What components should be included to deploy a solution using this code please, assuming the deployed-to machine has no office software installed? Thank you. — NoChance, Mar 15 '20 at 08:14

score 1 · Answer 3 · answered Dec 31 '13 at 17:39

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...

score 0 · Answer 4 · answered Dec 31 '13 at 17:39

The below will execute the entire process as you outlined.

var table = new DataTable();

var fileContents = File.ReadAllLines("yourFile");

var splitFileContents = (from f in fileContents select f.Split(':')).ToArray();

int maxLength = (from s in splitFileContents select s.Count()).Max();

for (int i = 0; i < maxLength; i++)
{
    table.Columns.Add();
}

foreach (var line in splitFileContents)
{
    DataRow row = table.NewRow();
    row.ItemArray = (object[])line;
    table.Rows.Add(row);
}

How to Read text file to DataTable

4 Answers4

Linked