How to combine CSVs with different columns?

Question

I have multiple CSV files and I need to combine all of them into 1 CSV file.

So, for example:

1.csv has data like below:

Roll no	Name
1	Abc
2	xyz

2.csv has data like below:

Name	Date	Score	Remarks
Mno	20-12-2023	450
Pqr	21-12-2023	480

and I want the combined CSV file for this data like below:

Master.csv

Roll no	Name	Date	Score	Remarks
1	Abc
2	xyz
	Mno	20-12-2023	450
	Pqr	21-12-2023	480

if a specific column is not present in a CSV file then leave it as empty.

How can I do this in C#?

Answer 1

You could read the two files as csv2.GetRecords<dynamic>() and get the dynamic properties to create a new dynamic list with a union of the two sets of headings.

void Main()
{
    using var reader1 = new StringReader("Roll no,Name\n1,ABC\n2,xyz");
    using var csv1 = new CsvReader(reader1, CultureInfo.InvariantCulture);
    var records1 = csv1.GetRecords<dynamic>().ToList();

    using var reader2 = new StringReader("Name,Date,Score,Remarks\nMno,20-12-2023,450,\nPqr,21-12-2023,480,");
    using var csv2 = new CsvReader(reader2, CultureInfo.InvariantCulture);
    var records2 = csv2.GetRecords<dynamic>().ToList();
    
    IDictionary<string, object> properties1 = (IDictionary<string, object>)records1.First();
    IDictionary<string, object> properties2 = (IDictionary<string, object>)records2.First();
    
    var headings = properties1.Keys.Union(properties2.Keys);
    
    var results = ConvertRecords(records1, headings);
    results.AddRange(ConvertRecords(records2, headings));
    
    using var csvOut = new CsvWriter(Console.Out, CultureInfo.InvariantCulture);
    csvOut.WriteRecords(results);
}

public List<dynamic> ConvertRecords(IEnumerable<dynamic> records, IEnumerable<string> headings)
{
    var results = new List<dynamic>();

    foreach (var record in records)
    {
        var result = new ExpandoObject() as IDictionary<string, Object>;
        var item = (IDictionary<string, object>)record;

        foreach (var heading in headings)
        {
            if (item.ContainsKey(heading))
            {
                result.Add(heading, item[heading]);
            }
            else
            {
                result.Add(heading, string.Empty);
            }
        }

        results.Add(result);
    }
    
    return results;
}