Sort CSV in C# into multiple lines

Question

Hello folks, so I got that JSON to CSV thing settled. Now, what I'm looking to do is take the list of classes and break those into several lines. Each line has all courses listed and what I'm wanting to do is to repeat the values of email address, course_name, score, and completion date.
For sake of simplicity, only including two courses from the long long long list.

email,php_course_score,php_course_completion_date,dotnet_course_score,dotnet_course_completion_date
trainee1@Company portal .com, 89.56, 09-23-2022, 88.44, 08-23-2022

What I would like for this to do is look like this after processing:
trainee1@Company portal ,.com,php,89.56,09-23-2022
trainee1@Company portal .com,dotnet,88.44,08-23-2022

The course name is in the field name list as course_completed_date. Using substr I can strip out _completed_date to get the name of the course. If anyone needs the full file, I can provide this, just trying to keep the details reasonable. Thanks all!

Answer 1

@bbcompent1 , Welcome to Microsoft Q&A, based on your description, you want to sort csv file into lines you wanted.

I think there is no common method to do it, so it may be may be somewhat hardcoded.

Here is a code example you could refer to.

internal class Program  
    {  
        static void Main(string[] args)  
        {  
            var dt = ConvertCSVtoDataTable(@"1.csv");  
            List<string> cols = new List<string>();  
            foreach (DataColumn item in dt.Columns)  
            {  
                cols.Add(item.ColumnName);  
  
            }  
            DataView view1 = new DataView(dt);  
            DataTable table1 = view1.ToTable("Table1", true, cols.Take(1).ToArray());  
            DataTable table2 = view1.ToTable("Table2", true, cols.Skip(1).Take(2).ToArray());  
            DataTable table3 = view1.ToTable("Table3", true, cols.Skip(3).Take(2).ToArray());  
            List<Info> list = new List<Info>();  
            foreach (DataRow item in table2.Rows)  
            {  
                list.Add(new Info { Email = table1.Rows[0][0].ToString(), Score = item[0].ToString(), CompeltedDate = item[1].ToString().Trim(), Course = cols[1].Replace("_course_score", " ") });  
            }  
            foreach (DataRow item in table3.Rows)  
            {  
                list.Add(new Info { Email = table1.Rows[0][0].ToString(), Score = item[0].ToString(), CompeltedDate = item[1].ToString().Trim(), Course = cols[3].Replace("_course_score", " ") });  
            }  
            foreach (var item in list)  
        {  
            Console.WriteLine(item.Email+"," + item.Course +","+ item.Score+","+item.CompeltedDate);  
        }  
  
        }  
             
  
  
          
        public static DataTable ConvertCSVtoDataTable(string strFilePath)  
        {  
            DataTable dt = new DataTable();  
            using (StreamReader sr = new StreamReader(strFilePath))  
            {  
                string[] headers = sr.ReadLine().Split(',');  
                foreach (string header in headers)  
                {  
                    dt.Columns.Add(header);  
                }  
                while (!sr.EndOfStream)  
                {  
                    string[] rows = sr.ReadLine().Split(',');  
                    DataRow dr = dt.NewRow();  
                    for (int i = 0; i < headers.Length; i++)  
                    {  
                        dr[i] = rows[i];  
                    }  
                    dt.Rows.Add(dr);  
                }  
  
            }  
  
  
            return dt;  
        }  
  
    }  
  
    public class Info  
    {  
        public  string Email { get; set; }  
  
        public string Course { get; set; }  
  
        public string Score { get; set; }      
  
        public string CompeltedDate { get; set; }  
  
         
    }  

Result:

Best Regards,
Jack

---

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.