How to convert data table rows to column c#

Question

I have a csv file which has multiple columns with data

Name, Description, Date
"ABC", "Hello testing", "2022-09-14"
"ABC", "Hello testing123", "2022-09-15"
"ABC", "Hello testing245", "2022-09-16"
"ABC1", "Hello testing", "2022-09-14"
"ABC1", "Hello testing143", "2022-09-16"
"ABC2", "Hello testing2", "2022-09-14"
"ABC2", "Hello testing2", "2022-09-15"

I want the change the Name values into columns and add rows data accordingly

Current output:

 ABC                ,ABC1              ,ABC2
"Hello testing"    , "Hello testing"  , "Hello testing" 
"Hello testing123", "Hello testing123","Hello testing123"
"Hello testing245", "Hello testing245","Hello testing245"
"Hello testing"   ,  "Hello testing"  , "Hello testing"
"Hello testing143",  "Hello testing143",  "Hello testing143"
"Hello testing2"  ,  "Hello testing2"  ,"Hello testing2" 
"Hello testing2"  ,  "Hello testing2"  ,  "Hello testing2"

I need to add column Name values as a header and then add the description and date according to the sample output

Sample output:

Date ,ABC               ,ABC1           ,ABC2
14,   Hello testing,    Hello testing   ,Hello testing2
15,   Hello testing123,                 ,Hello testing2
16,   Hello testing245, Hello testing143

I have read the csv and then my output table is displaying same data in all rows of all columns .How can I grouped it according to Name?

code:

        using (var reader = new StreamReader("data.csv"))

        using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
        {
           
            // Extract the column headers from the first row
            csv.Read();
            csv.ReadHeader();
            int columnNameIndex = 0; //name
            int columnValueIndex = 2; //description

            DataTable inputTable = new DataTable();
            foreach (string header in csv.HeaderRecord)
            {
                inputTable.Columns.Add(header);
            }
            while (csv.Read())
            {
                DataRow row = inputTable.NewRow();
                foreach (DataColumn column in inputTable.Columns)
                {
                    row[column.ColumnName] = csv.GetField(column.DataType, column.ColumnName);
                }
                inputTable.Rows.Add(row);
            }

            var outputTable = new DataTable();

            //Get the names of the columns for the output table
            var columnNames = inputTable.AsEnumerable().Where(x => x[columnNameIndex] != DBNull.Value && x[columnValueIndex] != DBNull.Value)
                                                            .Select(x => x[columnNameIndex].ToString()).Distinct().ToList();
            DataRow outputRow = outputTable.NewRow();

            //create the columns in the output table
            foreach (var columnName in columnNames)
            {
                outputTable.Columns.Add(new DataColumn(columnName));
            }

            //get all the rows in the input table
            var totalRows = inputTable.Rows.Count;

            //loop through the input table
            for (int n = 0; n < totalRows; n++)
            {
                //loop through each columnname for each row
                for (int i = 0; i < columnNames.Count; i++)
                {
                    //if it's the first loop we need a new row
                    if (i == 0)
                    {
                        outputRow = outputTable.NewRow();
                    }

                    outputRow[columnNames[i]] = inputTable.Rows[n][columnValueIndex].ToString();
                    //^^get the corresponding value from the input table

                  //confusion at this stage
                    if (i == columnNames.Count - 1)
                        outputTable.Rows.Add(outputRow);

                }
            }

           
        }
    }

Answer 1

One solution:

        //a way to read csv file
        IEnumerable<(string id, string desc, string date)> ds = File.ReadAllLines("csv.txt")
                                                                    .Skip(1)
                                                                    .Select(x => x.Replace("\"", ""))
                                                                    .Select(x => (x.Split(',')[0], x.Split(',')[1], x.Split(',')[2]));

        //get title of each column from dynamic values (ABC, ABC1,....)
        var titles = ds.Select(x => x.id).Distinct().OrderBy(x => x);

        //sort values by date then by id (ABC,ABC1...) and group by date
        var dd = ds.OrderBy(x => x.date).ThenBy(x => x.id).GroupBy(x => x.date);

        DataTable workTable = new DataTable("CSV");

        workTable.Columns.Add(new DataColumn("date"));
        foreach(var x in titles)
            workTable.Columns.Add(new DataColumn(x));

        //load the datable
        foreach(var d in dd)
        {
            DataRow row = workTable.NewRow();
            row["date"] = d.Key.Trim();
            foreach (var v in d)
            {
                row[v.id] = v.desc.Trim();                   
            }
            workTable.Rows.Add(row);
        }

        //show the content of datable
        foreach (DataRow row in workTable.Rows)
        {
            string date = row["date"].ToString();
            Console.Write($"{date}");
            foreach (var t in titles)
            {
                Console.Write($" -> {t} -> [{row[t]}]");
            }
            Console.WriteLine();
        }

result:

2022-09-14 -> ABC -> [Hello testing] -> ABC1 -> [Hello testing] -> ABC2 -> [Hello testing2]
2022-09-15 -> ABC -> [Hello testing123] -> ABC1 -> [] -> ABC2 -> [Hello testing2]
2022-09-16 -> ABC -> [Hello testing245] -> ABC1 -> [Hello testing143] -> ABC2 -> []

Just adapt your ouput as you like