how to read the text file with pipe delimited in c# and put data in datatable

Question

User2038048451 posted

HI ,

I have text file with below data.

id|name|age|city|state|country
1|ravi|28|Bangalore||karnataka|India
2|kumar|30|Bangalore||

I want to read this text file and get this data into datatable using c#. How this can be done.

Thanks

Answer 1

User-821857111 posted

You can use the TextFieldParser class in the Microsoft.VisualBasic.FileIO namespace:

var file = @"path_to_your_file";
var parser = new TextFieldParser(file);
var dt = new DataTable();
parser.SetDelimiters("|");
while(!parser.EndOfData)
{
    var currentRow = parser.ReadFields();
    if(headerRow)
    {
        foreach(var field in currentRow)
        {
            dt.Columns.Add(field, typeof(object));
        }
            headerRow = false;
    }
    else
    {
        dt.Rows.Add(currentRow);
    }
}

You will probably need to add a reference to Microsoft.VisualBasic, and a using statement for Microsoft.VisualBasic.FileIO to your file. They only seem to provide VB examples on MSDN, but you can use C# too: https://msdn.microsoft.com/en-us/library/microsoft.visualbasic.fileio.textfieldparser(v=vs.110).aspx

Answer 2

User-271186128 posted

Hi Ravikumar,

As for this issue, I suggest you could try to use the StreamReader.ReadLine Method to read the text files.You could refer to the following code:

The text file:

id|name|age|city|state|country
 1|ravi|28|Bangalore|karnataka|India
 2|kumar|30|Bangalore|karnataka|India

Code:

            //According to the first line in the text file, create a dataTable
            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[6] { new DataColumn("ID"), new DataColumn("Name"), new DataColumn("Age"), new DataColumn("City"), new DataColumn("State"), new DataColumn("Country") });

            StringBuilder sb = new StringBuilder();
            List<string> list = new List<string>();
            using (StreamReader sr = new StreamReader(@"D:\MyFolder\Test.txt"))
            {
                while (sr.Peek() >= 0)
                {
                    list.Add(sr.ReadLine()); //Using readline method to read text file.
                }
            }
            //ignore the fist line (title line).
            for (int i = 1; i < list.Count;i++ )
            {
                string[] strlist = list[i].Split('|'); //using string.split() method to split the string.
                dt.Rows.Add(strlist[0], strlist[1], strlist[2], strlist[3], strlist[4], strlist[5]);

                //If you want to insert it into database, you could insert from here.
            }
            GridView1.DataSource = dt;
            GridView1.DataBind();

The output screenshot:

Best Regards,
Dillion