How to split single row values into multiple rows based on columns c#

Question

I have a large csv file where there are multiple values that belong to different people store in the same row. Below you can find a sample of this data.

country	country2	country3	name1	name2	name3	phone1	phone2	phone3
USA	UK	Australia	Michael	Mitchell	David	222	333	444
Colombia	Paraguay	Bolivia		John	Chris		555	7777
Brazil	Germany	Japan	Silvia	Ana		888	999

I want to split this data so that I can keep the first 3 columns untouched and only format the rest, meaning that I will keep the format for country, country2 and country3 but name and phone will appear just once. The idea is that each person from the same row will have at the end the same 3 countries but its data will be viewed on a separate row in order to look like this:

country	country2	country3	name	phone
USA	UK	Australia	Michael	222
USA	UK	Australia	Mitchell	333
USA	UK	Australia	David	444
Colombia	Paraguay	Bolivia
Colombia	Paraguay	Bolivia	John	555
Colombia	Paraguay	Bolivia	Chris	7777
Brazil	Germany	Japan	Silvia	888
Brazil	Germany	Japan	Ana	999
Brazil	Germany	Japan

I have seen some examples based on SQL but I am trying to accomplish this on C# since I need to have the data set on this particular way so that I can do some other things with it before sending it to the database. I currently storing the data into a datatable but I am not sure how can I make this change without affecting the incosistency of the data. Any ideas?

EDIT: This is the code that I have so far only to send this data to the datatable:

    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;
    }

Answer 1

I expect that your test csv file looks like that:

USA;UK;Australia;Michael;Mitchell;David;222;333;444
Colombia;Paraguay;Bolivia;;John;Chris;;555;7777
Brazil;Germany;Japan;Silvia;Ana;;888;999;;

You will get what you want in modifiedData variable:

using System.Collections.Generic;
using System.IO;

namespace CsvMod
{
    public class OriginalData
    {
        public string Country1 { get; set; }
        public string Country2 { get; set; }
        public string Country3 { get; set; }
        public string Name1 { get; set; }
        public string Name2 { get; set; }
        public string Name3 { get; set; }
        public string Phone1 { get; set; }
        public string Phone2 { get; set; }
        public string Phone3 { get; set; }
    }

    public class ModifiedData
    {
        public string Country1 { get; set; }
        public string Country2 { get; set; }
        public string Country3 { get; set; }
        public string Name { get; set; }
        public string Phone { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            var csvLines = File.ReadAllLines("test.csv");

            var originalData = new List<OriginalData>();

            foreach (var line in csvLines)
            {
                var items = line.Split(';');

                originalData.Add(new OriginalData
                {
                    Country1 = items[0],
                    Country2 = items[1],
                    Country3 = items[2],
                    Name1 = items[3],
                    Name2 = items[4],
                    Name3 = items[5],
                    Phone1 = items[6],
                    Phone2 = items[7],
                    Phone3 = items[8],
                });
            }

            var modifiedData = new List<ModifiedData>();

            foreach (var item in originalData)
            {
                modifiedData.AddRange(new List<ModifiedData>
                {
                    new ModifiedData
                    {
                        Country1 = item.Country1,
                        Country2 = item.Country2,
                        Country3 = item.Country3,
                        Name = item.Name1,
                        Phone = item.Phone1,
                    },
                    new ModifiedData
                    {
                        Country1 = item.Country1,
                        Country2 = item.Country2,
                        Country3 = item.Country3,
                        Name = item.Name2,
                        Phone = item.Phone2,
                    },
                    new ModifiedData
                    {
                        Country1 = item.Country1,
                        Country2 = item.Country2,
                        Country3 = item.Country3,
                        Name = item.Name3,
                        Phone = item.Phone3,
                    },
                });
            }
        }
    }
}

Or if you really trust your data, then one LINQ statement and result contains the same:

using System.Collections.Generic;
using System.IO;
using System.Linq;

namespace CsvMod
{
    public class ModifiedData
    {
        public string Country1 { get; set; }
        public string Country2 { get; set; }
        public string Country3 { get; set; }
        public string Name { get; set; }
        public string Phone { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            var csvLines = File.ReadAllLines("test.csv");

            var result = csvLines.Aggregate(new List<ModifiedData>(), (acc, x) =>
            {
                var items = x.Split(';');

                acc.AddRange(new List<ModifiedData>
                {                    
                    new ModifiedData
                    {
                        Country1 = items[0],
                        Country2 = items[1],
                        Country3 = items[2],
                        Name = items[3],
                        Phone = items[6],
                    },
                    new ModifiedData
                    {
                        Country1 = items[0],
                        Country2 = items[1],
                        Country3 = items[2],
                        Name = items[4],
                        Phone = items[7],
                    },
                    new ModifiedData
                    {
                        Country1 = items[0],
                        Country2 = items[1],
                        Country3 = items[2],
                        Name = items[5],
                        Phone = items[8],
                    },
                });

                return acc;
            });
        }
    }
}