Read CSV file in C# and store in DataTable

Question

I am using following code to read csv file and store in to C#. But my requirement is that, I want to skip the first line in csv file, since it is not structured, hence take the second row as column. How to do it in the exiting code.

private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
{    
   DataTable csvData = new DataTable();

    try
    {

    using(TextFieldParser csvReader = new TextFieldParser(csv_file_path))
        {
            csvReader.SetDelimiters(new string[] { ";" });
            csvReader.HasFieldsEnclosedInQuotes = true;
            string[] colFields = csvReader.ReadFields();
            foreach (string column in colFields)
            {
                DataColumn datecolumn = new DataColumn(column);
                datecolumn.AllowDBNull = true;
                csvData.Columns.Add(datecolumn);
            }

            while (!csvReader.EndOfData)
            {
                string[] fieldData = csvReader.ReadFields();
                //Making empty value as null
                for (int i = 0; i < fieldData.Length; i++)
                {
                    if (fieldData[i] == "")
                    {
                        fieldData[i] = null;
                    }
                 }
                     csvData.Rows.Add(fieldData);
             }
        }
   }
   catch (Exception ex)
   {
   }
   return csvData;
}

My CSV file looks like

a;;{b;g;t{}
firstname;lastname;age;locaion
peter;vela;28;denver
sasi;kiran;38;colorado
sastri;miro;texas

Tried my answer with your sample data and it works. Note however that you use as field separator the comma, while the file has a semicolon between fields — Steve
– Steve, Commented Jun 27, 2016 at 21:56

Steve · Accepted Answer · 2016-06-27 21:57:47Z

1

The TextFieldParser class has a constructor that can receive a stream.
So the idea is: Open a StreamReader on your file, read the first line and discard it, then build the TextFieldParser with the opened StreamReader positioned on the second line where your column names exist.

I have tested it and it seems to work

using (StreamReader reader = new StreamReader(csv_file_path))
{
    // Discard the first line... (add checking here)
    reader.ReadLine();
    using(TextFieldParser csvReader = new TextFieldParser(reader))
    {
       csvReader.SetDelimiters(new string[] { ";" });
       .....

I have also set the delimiter to a semicolon instead of a comma according to your sample data.

edited Jun 27, 2016 at 21:57

answered Jun 27, 2016 at 21:26

Steve

217k22 gold badges242 silver badges296 bronze badges

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

Comments

Dennis · Accepted Answer · 2016-06-27 22:22:08Z

0

This should work:

int rowsToSkip = 1;
int position = 0;
while (!csvReader.EndOfData)
{
    string[] fieldData = csvReader.ReadFields();
    if(position >= rowsToSkip)
    {

        //Making empty value as null
        for (int i = 0; i < fieldData.Length; i++)
        {
            if (fieldData[i] == "")
            {
                fieldData[i] = null;
            }
        }
        csvData.Rows.Add(fieldData);
    }
    position++;
}

edited Jun 27, 2016 at 22:22

answered Jun 27, 2016 at 21:29

Dennis

3463 silver badges14 bronze badges

3 Comments

peter Over a year ago

what about columns?,Here file second row is column rather than first row

Dennis Over a year ago

Not sure I understand. You need to skip the first row and read the headers from the second? I think it would be easier if you could post the first three lines of your file.

Dennis Over a year ago

You could just check if position == 1, which is where your headers are, and populate data set columns.

Tabish Matin · Accepted Answer · 2016-06-28 01:39:31Z

0

If you want to make your life easy and ready to spend some money. Flexcel will make your life easy.

This thing will be done in 2 lines of code.

Thanks

answered Jun 28, 2016 at 1:39

Tabish Matin

951 silver badge12 bronze badges

Comments

Mustafa Özçetin · Accepted Answer · 2023-12-28 19:21:07Z

0

string[] Lines = File.ReadAllLines(this.INPUTTFILE);
string[] Fields;
for (int i = 1; i < Lines.GetLength(0); i++)
{
    DataRow Row = Dt_Input_With_ColoumName.NewRow();
    Fields = Lines[i].Split(new char[] { ',' });
    for (int f = 0; f < Dt_Input_With_ColoumName.Columns.Count; f++)
    {
        Row[f] = Fields[f];
    }
    Dt_Input_With_ColoumName.Rows.Add(Row);
}

edited Dec 28, 2023 at 19:21

Mustafa Özçetin

3,2324 gold badges23 silver badges22 bronze badges

answered Feb 13, 2023 at 9:44

Kamal Kumar chanchal

11 silver badge3 bronze badges

Collectives™ on Stack Overflow

Read CSV file in C# and store in DataTable

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related