Remove some coma separated header values from a csv file and create a datatable from that csv in C#

Question

I have a very big csv file like this (more than 12K rows) ;

    Comment, DateTime   Name,  Age,  Class, Place,  --> these are the header columns
    Good,    03/10/2022, John,  12,    3,     UK,
    Bad,     12/10/2022, Tom,   15,    2,     US

This is a generalized example which shows column names. But it will be more than this columns some times.

I am reading it as shown below

    List<string> lines = File.ReadAllLines(System.IO.Path.ChangeExtension(FileNameWithPath, ".csv")).ToList();

I need a datatable from the above mentioned csv file but i DO NOT want Comment and Place columns in the datatable.

Can anybody show me how we can achieve this ?

Column datatypes :

       DateTime --> typeof(datetime)

       Name     --> typeof(string)

       Age --> typeof(double?)

       Class  --> typeof(int)

Does it need to be a DataTable? If you are open to using a class object, take a look at how you can deserialize to a model in CsvHelper. Sorry but obligatory possible duplicate — Narish
– Narish, Commented Oct 26, 2022 at 15:23
@Narish buddy, we cannot predict the columns name and number of columns in advance. — Micheal
– Micheal, Commented Oct 26, 2022 at 15:33
that sounds like a rough situation. Well in that case, only thing I can think of is to create the full data table and then you can use dt.Columns.Remove("colNameString") or dt.Columns.RemoveAt(colIndex). Or for only one forward pass, build the datatable manually. Since you are getting the info in as List<string> it seems like there is some data marshalling you are going to be doing in any case — Narish
– Narish, Commented Oct 26, 2022 at 15:37

stephemer · Accepted Answer · 2022-10-26 15:47:52Z

0

You can remove the columns using DataColumnCollection.Remove() after converting from the list to a datatable.

dt.Remove("Comments")

answered Oct 26, 2022 at 15:47

stephemer

1

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

Comments

Zain Maqbool · Accepted Answer · 2022-10-26 15:56:03Z

0

public static DataTable CSVtoDataTable(string filePath)
  {
    DataTable dtData = new DataTable();
    using (StreamReader sReader = new StreamReader(filePath))
    {
        string[] columnHeader = sReader.ReadLine().Split(',');
        foreach (string header in columnHeader)
        {
            dtData.Columns.Add(header);
        }
        while (!sReader.EndOfStream)
        {
            string[] rows = sReader.ReadLine().Split(',');
            DataRow drRow = dtData.NewRow();
            for (int i = 0; i < columnHeader.Length; i++)
            {
                drRow[i] = rows[i];
            }
            dtData.Rows.Add(drRow);
        }

    }

    dtData.Columns.Remove("Comment");
    dtData.Columns.Remove("Place");
        return dtData;
   }

answered Oct 26, 2022 at 15:56

Zain Maqbool

1

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Remove some coma separated header values from a csv file and create a datatable from that csv in C#

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related