Convert CSV to datatable

Question

Hello

I am trying to convert manually a CSV file into a datatable for posterior analysis and filtering.

The company was doing it manually in Excel but I’d figure it can be done programmatically and increase productivity.

I did try do use some existing classes and nuggets without success (Errors I can’t fix or don’t understand).

Could someone please look at my code and help me figure how to convert manually a CSV with '|' as divider between cells to datatable and apply filtering please. Or Just point me in the right direction.

 

I am trying this: https://www.c-sharpcorner.com/forums/c-sharp-importing-csv-files-into-datatables 

 

I like to figure things by myself but this is taking me forever.

 

Best regards

Rui 

private void ConvertCSVtoDataTable(string filePath)
     {
        LogMessage("(<-o->)");

        // Counts lines in file
        long lineCount = 0;
        int columnsCount = 0;
        char divider = '|';

        // FIRST LINE - Read first Line for Columns Count
        string firstLine = File.ReadLines(filePath).First();

        // FIRST LINE - COUNT columns for DataTable using DIVIDER = "|" from First Line
        foreach (char c in firstLine) if (c == divider) columnsCount++;


        // CREATE DATATABLE
        DataTable dt = new DataTable();
        dt.Clear();
        dt.Columns.Add("a"); // Colonne 0
        dt.Columns.Add("b"); // Colonne 0
        dt.Columns.Add("c"); // Colonne 0
        dt.Columns.Add("d"); // Colonne 0
        dt.Columns.Add("e"); // Colonne 0
        dt.Columns.Add("f"); // Colonne 0
        dt.Columns.Add("g"); // Colonne 0
        dt.Columns.Add("h"); // Colonne 0
        dt.Columns.Add("i"); // Colonne 0
        dt.Columns.Add("j"); // Colonne 0
        dt.Columns.Add("k"); // Colonne 0
        dt.Columns.Add("l"); // Colonne 0
        dt.Columns.Add("m"); // Colonne 0
        dt.Columns.Add("n"); // Colonne 0
        dt.Columns.Add("o"); // Colonne 0
        dt.Columns.Add("p"); // Colonne 0
        dt.Columns.Add("q"); // Colonne 0
        dt.Columns.Add("r"); // Colonne 0
        dt.Columns.Add("s"); // Colonne 0
        dt.Columns.Add("t"); // Colonne 0
        dt.Columns.Add("u"); // Colonne 0
        dt.Columns.Add("v"); // Colonne 0
        dt.Columns.Add("w"); // Colonne 0
        dt.Columns.Add("x"); // Colonne 0
        dt.Columns.Add("y"); // Colonne 0
        dt.Columns.Add("z"); // Colonne 0


        // READ LINE BY LINE
        using (StreamReader r = new StreamReader(filePath))
        {
           string line;

           // Reads line by line from the file
           while ((line = r.ReadLine()) != null)
           {

              while (line.IndexOf(divider) != -1) // -1 is returned if not found
              {

                 // START DIVIDING IN BLOCKS FOR DATATABLE
                 // Get divider position
                 int endBlock = line.IndexOf(divider);

                 // Get first Block of text
                 String firstBlock = line.Substring(0, endBlock);
                 LogMessage("Line: " + lineCount + " - Block: " + firstBlock);
                 // Deletes first text block
                 line = line.Remove(0, firstBlock.Length + 1);

                 // MOVE TO NEXT CELL/BLOCK UNTIL END OF LINE

                 // REPEAT IN EVERY LINE

                 lineCount++;
              }
           }
        }

     }