How to split string data and push to data table with specific format using asp.net c#?

Question

Maybe it's a bad day or I am a stupid. I was trying to create a data table from a file upload (.txt).

10|00309|IN3136|EKM|110|13-12-2019|V1.1|||||
20|1|01|02|13122019120157_1||Please Enter Mother's First Name|
20|2|01|02|13122019120157_2||Please Enter Mother's First Name|
20|3|01|02|13122019120157_3||Please Enter Mother's First Name|

is the data inside the text file.

var data = File.ReadAllText(FilePath);
string[] strArr = null;
int count = 0;
char[] splitchar = { '|' };
strArr = data.Split(splitchar);
DataTable dt2 = new DataTable();
dt2.Clear();
dt2.Columns.Add("Col0");
dt2.Columns.Add("Col1");
dt2.Columns.Add("Col2");
dt2.Columns.Add("Col3");
dt2.Columns.Add("Col4");
dt2.Columns.Add("Col5");
dt2.Columns.Add("Col6");

for (count = 0; count <= strArr.Length - 1; count++)
 {
   if (count >= 11)
   {
    DataRow _dr2 = dt2.NewRow();
    for (int i = 0; i <= 6; i++)
    {
         if (i == 0)
              _dr2["Col0"] = strArr[count+i];
         else if (i == 1)
              _dr2["Col1"] = strArr[count+i];
         else if (i == 2)
              _dr2["Col2"] = strArr[count+i];
         else if (i == 3)
               _dr2["Col3"] = strArr[count+i];
         else if (i == 4)
               _dr2["Col4"] = strArr[count+i];
         else if (i == 5)
                _dr2["Col5"] = strArr[count+i];
          else if (i == 6)
                _dr2["Col6"] = strArr[count+i];

     }
   dt2.Rows.Add(_dr2);
  }
}

I am trying to make it like this

my loops are completely stupid, I know, please point me in the right direction..

Hi there! One easy thing you can do is remove all those if statements and just have _dr2[$"Col{i}"] = strArr[count+i];, this uses string interpolation and gets rid of the bug you've introduced on the last one :) — Moo-Juice
– Moo-Juice, Commented Jan 17, 2020 at 9:03
@Moo-Juice i tried and it's throwing a 'Message = "Index was outside the bounds of the array."' ..!! concept of 2nd loop itself was a bad idea I think.. don't know how to fix it. — FIN WIN
– FIN WIN, Commented Jan 17, 2020 at 9:10
In production I would use CsvReader as @panagiotiskanavos said. For education you can read all file lines like File.ReadAllLines that will give you string[] for each line in file. However reading all file in memory can take all memory for large files so you can check the StreamReader.ReadLine() to process file line by line. Then you can split each line by | and create table row — oleksa
– oleksa, Commented Jan 17, 2020 at 9:58

Panagiotis Kanavos · Accepted Answer · 2020-01-17 09:24:27Z

3

That's essentially a CSV file using | as the field separator. Instead of writing your own code, you could use a library like CsvHelper with a custom field separator. You can even parse string values directly into numbers or dates.

CsvHelper offers the CsvReader for reading individual fields or full records and CsvDataReader to load the CSV file as an IDataReader that can be used to load a DataTable or import the data into the database using eg SqlBulkCopy

Borrowing from the documentation example :

using (var reader = new StreamReader("path\\to\\file.csv"))
using (var csv = new CsvReader(reader))
{
    // Do any configuration to `CsvReader` before creating CsvDataReader.
    csv.Configuration.Delimiter="|";
    using (var dr = new CsvDataReader(csv))
    {        
        var dt = new DataTable();
        dt.Load(dr);
    }
}

answered Jan 17, 2020 at 9:24

Panagiotis Kanavos

134k16 gold badges211 silver badges270 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

FIN WIN Over a year ago

The file I am dealing with is in .txt format is that gonna be an issue for reading?

Panagiotis Kanavos Over a year ago

@FINWIN there's no txt format. It's just a text file. CSV files are also text files where the Values are Separated by Commas. That's where the letters CSV come from. It's a loose definition and people often use different separators

FIN WIN Over a year ago

Thanks, It's new information to work with. I will try it for sure. ;)

Gowri Pranith Kumar · Accepted Answer · 2020-01-17 09:12:16Z

2

Instead of using File.ReadAllText ,use File.ReadAllLines which is easy to traverse between all the lines as each line is one record .

 var lines = File.ReadAllLines(**@Path to the File**);
            for (int i = 0; i < lines.Length; i++)
            {
                var str = lines[i];
                var strarray = str.Split('|');
            }

answered Jan 17, 2020 at 9:12

Gowri Pranith Kumar

1,6852 gold badges12 silver badges22 bronze badges

Comments

Shaikh Farooque · Accepted Answer · 2020-01-17 10:19:21Z

1

The below code will give you the expected result: Data table filled with the lines from CSV file.

DataTable dt2 = new DataTable();
dt2.Columns.Add("Col0");
dt2.Columns.Add("Col1");
dt2.Columns.Add("Col2");
dt2.Columns.Add("Col3");
dt2.Columns.Add("Col4");
dt2.Columns.Add("Col5");
dt2.Columns.Add("Col6");

var lines = File.ReadAllLines(@"file path"); //C:\ToBeDeleted\test.txt
for (int i = 0; i < lines.Length; i++)
{
    var str = lines[i];
    var strarray = str.Split('|');

    DataRow drow = dt2.NewRow();
    drow["Col0"] = strarray[0];
    drow["Col1"] = strarray[1];
    drow["Col2"] = strarray[2];
    drow["Col3"] = strarray[3];
    drow["Col4"] = strarray[4];
    drow["Col5"] = strarray[5];
    drow["Col6"] = strarray[6];
    dt2.Rows.Add(drow);
}

To display your expected result use the below console code:

//Displaying as a table.
for (int i = 0; i < dt2.Columns.Count; i++)
{
    Console.Write(dt2.Columns[i].ColumnName + " \t |");
}
Console.WriteLine();
for (int j = 0; j < dt2.Rows.Count; j++)
{
    for (int i = 0; i < dt2.Columns.Count; i++)
    {
        Console.Write(dt2.Rows[j].ItemArray[i] + " \t | ");
    }
    Console.WriteLine();
}

Below is the output of the above code.

answered Jan 17, 2020 at 10:19

Shaikh Farooque

2,6402 gold badges22 silver badges36 bronze badges

2 Comments

FIN WIN Over a year ago

I think I cant use 'strarray[6]' because there will be more values in the file so I won't be able to use the position number directly. Thanks anyway I got the idea. :)

Shaikh Farooque Over a year ago

This is just an example. please mark as answer if you it answers your question.

FIN WIN · Accepted Answer · 2020-01-17 11:58:51Z

Finally, I end up doing this. It's working for me (at least for now :D)

   private DataTable ConvertToDataTable(string FilePath)
    {
        var lines = File.ReadAllLines(FilePath);

        DataTable dt2 = new DataTable();
        dt2.Clear();
        dt2.Columns.Add("Col0");
        dt2.Columns.Add("Col1");
        dt2.Columns.Add("Col2");
        dt2.Columns.Add("Col3");
        dt2.Columns.Add("Col4");
        dt2.Columns.Add("Col5");
        dt2.Columns.Add("Col6");

        for (int i = 0; i < lines.Length; i++)
        {
            var str = lines[i];
            if (i > 0)
            {
                var strarray = str.Split('|');
                DataRow _dr2 = dt2.NewRow();
                for (int count = 0; count <= strarray.Length - 1; count++)
                {
                    if (count == 0)
                        _dr2["Col0"] = strarray[count];
                    else if (count == 1)
                        _dr2["Col1"] = strarray[count];
                    else if (count == 2)
                        _dr2["Col2"] = strarray[count];
                    else if (count == 3)
                        _dr2["Col3"] = strarray[count];
                    else if (count == 4)
                        _dr2["Col4"] = strarray[count];
                    else if (count == 5)
                        _dr2["Col5"] = strarray[count];
                    else if (count == 6)
                        _dr2["Col6"] = strarray[count];

                }
                dt2.Rows.Add(_dr2);
            }
        }
        return dt2;
    }

Collectives™ on Stack Overflow

How to split string data and push to data table with specific format using asp.net c#?

4 Answers 4

3 Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related