Combine CSV Files without Duplicating Headings using C#

The simple script below shows how to combine csv files without duplicating headings using C#. This technique assumes that all of the files have the same structure, and that all of them contain a header row.

The timings in this post came from combining 8 csv files with 13 columns and a combined total of 9.2 million rows.

I first tried combining the files with the PowerShell technique described here. It was painfully slow and took an hour and a half! This is likely because it is deserializing and then serializing every bit of data in the files, which adds a lot of unnecessary overhead.

Next I tried the C# script below using LINQPad. When reading from and writing to a network share, it took 3 minutes and 56 seconds. Much better! Next I tried it on a local SSD drive and it took just 44 seconds. To recap:

PowerShell with deserialization/serialization: 90 minutes
C# with source and destination on network drive: 3 minutes and 56 seconds
C# with source and destination on a local SSD: 44 seconds

/*
C# script combines multiple csv files without duplicating headers.
Combining 8 files with a combined total of about 9.2 million rows 
took about 3.5 minutes on a network share and 44 seconds on an SSD.
*/

string sourceFolder = @"C:\CSV_Files";
string destinationFile = @"C:\CSV_Files\CSV_Files_Combined.csv";

// Specify wildcard search to match CSV files that will be combined
string[] filePaths = Directory.GetFiles(sourceFolder, "CSV_File_Number?.csv");
StreamWriter fileDest = new StreamWriter(destinationFile, true);

int i;
for (i = 0; i < filePaths.Length; i++)
{
    string file = filePaths[i];

    string[] lines = File.ReadAllLines(file);

    if (i > 0)
    {
        lines = lines.Skip(1).ToArray(); // Skip header row for all but first file
    }

    foreach (string line in lines)
    {
        fileDest.WriteLine(line);
    }
}

fileDest.Close();

C# script combines multiple csv files without duplicating headers.

Combining 8 files with a combined total of about 9.2 million rows

took about 3.5 minutes on a network share and 44 seconds on an SSD.

string sourceFolder = @"C:\CSV_Files";

string destinationFile = @"C:\CSV_Files\CSV_Files_Combined.csv";

// Specify wildcard search to match CSV files that will be combined

string[] filePaths = Directory.GetFiles(sourceFolder, "CSV_File_Number?.csv");

StreamWriter fileDest = new StreamWriter(destinationFile, true);

int i;

for (i = 0; i < filePaths.Length; i++)

{

string file = filePaths[i];

string[] lines = File.ReadAllLines(file);

if (i > 0)

{

lines = lines.Skip(1).ToArray(); // Skip header row for all but first file

}

foreach (string line in lines)

{

fileDest.WriteLine(line);

}

fileDest.Close();

Let me know what you think in the comments. And if you know of a faster way to accomplish this, I’d love to hear it!

Not a faster way but if the columns of the different csv files are partially mismatched then the below will solve the problem
Also included are the helper classes containing the methods to move the data from column to row-based format then to csv format.


public static class CSVHelpers
{
    public static void CombineCsvFiles(string sourceFolder, string destinationFile, string searchPattern = "*.csv", bool isMismatched = false)
    {
        // Specify wildcard search to match CSV files that will be combined
        string[] filePaths = Directory.GetFiles(sourceFolder, searchPattern);
        if (isMismatched)
            CombineMisMatchedCsvFiles(filePaths, destinationFile);
        else
            CombineCsvFiles(filePaths, destinationFile);
    }

    public static void CombineCsvFiles(string[] filePaths, string destinationFile)
    {
        StreamWriter fileDest = new StreamWriter(destinationFile, true);

        int i;
        for (i = 0; i < filePaths.Length; i++)
        {
            string file = filePaths[i];

            string[] lines = File.ReadAllLines(file);

            if (i > 0)
            {
                lines = lines.Skip(1).ToArray(); // Skip header row for all but first file
            }

            foreach (string line in lines)
            {
                fileDest.WriteLine(line);
            }
        }

        fileDest.Close();
    }

    public static void CombineMisMatchedCsvFiles(string[] filePaths, string destinationFile, char splitter = ',')
    {

        HashSet<string> combinedheaders = new HashSet<string>();
        int i;
        // aggregate headers
        for (i = 0; i < filePaths.Length; i++)
        {
            string file = filePaths[i];
            combinedheaders.UnionWith(File.ReadLines(file).First().Split(splitter));
        }
        var hdict = combinedheaders.ToDictionary(y => y, y => new List<object>());

        string[] combinedHeadersArray = combinedheaders.ToArray();
        for (i = 0; i < filePaths.Length; i++)
        {
            var fileheaders = File.ReadLines(filePaths[i]).First().Split(splitter);
            var notfileheaders = combinedheaders.Except(fileheaders);

            File.ReadLines(filePaths[i]).Skip(1).Select(line => line.Split(splitter)).ForEach(spline =>
            {
                for (int j = 0; j < fileheaders.Length; j++)
                {
                    hdict[fileheaders[j]].Add(spline[j]);
                }
                foreach (string header in notfileheaders)
                {
                    hdict[header].Add(null);
                }

            });
        }

        DataTable dt = hdict.ToDataTable();

        dt.ToCSV(destinationFile);
    }
}

public static class DataTableHelper
{
    public static DataTable ToDataTable(this Dictionary<string, List<object>> dict)
    {
        DataTable dataTable = new DataTable();

        dataTable.Columns.AddRange(dict.Keys.Select(c => new DataColumn(c)).ToArray());

        for (int i = 0; i < dict.Values.Max(item => item.Count()); i++)
        {
            DataRow dataRow = dataTable.NewRow();

            foreach (var key in dict.Keys)
            {
                if (dict[key].Count > i)
                    dataRow[key] = dict[key][i];
            }
            dataTable.Rows.Add(dataRow);
        }

        return dataTable;
    }

    public static void ToCSV(this DataTable dt, string destinationfile)
    {
        StringBuilder sb = new StringBuilder();

        IEnumerable<string> columnNames = dt.Columns.Cast<DataColumn>().
                                          Select(column => column.ColumnName);
        sb.AppendLine(string.Join(",", columnNames));

        foreach (DataRow row in dt.Rows)
        {
            IEnumerable<string> fields = row.ItemArray.Select(field => field.ToString());
            sb.AppendLine(string.Join(",", fields));
        }

        File.WriteAllText(destinationfile, sb.ToString());
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

public static class CSVHelpers

{

public static void CombineCsvFiles(string sourceFolder, string destinationFile, string searchPattern = "*.csv", bool isMismatched = false)

{

// Specify wildcard search to match CSV files that will be combined

string[] filePaths = Directory.GetFiles(sourceFolder, searchPattern);

if (isMismatched)

CombineMisMatchedCsvFiles(filePaths, destinationFile);

else

CombineCsvFiles(filePaths, destinationFile);

}

public static void CombineCsvFiles(string[] filePaths, string destinationFile)

{

StreamWriter fileDest = new StreamWriter(destinationFile, true);

int i;

for (i = 0; i < filePaths.Length; i++)

{

string file = filePaths[i];

string[] lines = File.ReadAllLines(file);

if (i > 0)

{

lines = lines.Skip(1).ToArray(); // Skip header row for all but first file

}

foreach (string line in lines)

{

fileDest.WriteLine(line);

}

fileDest.Close();

}

public static void CombineMisMatchedCsvFiles(string[] filePaths, string destinationFile, char splitter = ',')

{

HashSet<string> combinedheaders = new HashSet<string>();

int i;

// aggregate headers

for (i = 0; i < filePaths.Length; i++)

{

string file = filePaths[i];

combinedheaders.UnionWith(File.ReadLines(file).First().Split(splitter));

}

var hdict = combinedheaders.ToDictionary(y => y, y => new List<object>());

string[] combinedHeadersArray = combinedheaders.ToArray();

for (i = 0; i < filePaths.Length; i++)

{

var fileheaders = File.ReadLines(filePaths[i]).First().Split(splitter);

var notfileheaders = combinedheaders.Except(fileheaders);

File.ReadLines(filePaths[i]).Skip(1).Select(line => line.Split(splitter)).ForEach(spline =>

{

for (int j = 0; j < fileheaders.Length; j++)

{

hdict[fileheaders[j]].Add(spline[j]);

}

foreach (string header in notfileheaders)

{

hdict[header].Add(null);

}

});

}

DataTable dt = hdict.ToDataTable();

dt.ToCSV(destinationFile);

}

public static class DataTableHelper

{

public static DataTable ToDataTable(this Dictionary<string, List<object>> dict)

{

DataTable dataTable = new DataTable();

dataTable.Columns.AddRange(dict.Keys.Select(c => new DataColumn(c)).ToArray());

for (int i = 0; i < dict.Values.Max(item => item.Count()); i++)

{

DataRow dataRow = dataTable.NewRow();

foreach (var key in dict.Keys)

{

if (dict[key].Count > i)

dataRow[key] = dict[key][i];

}

dataTable.Rows.Add(dataRow);

}

return dataTable;

}

public static void ToCSV(this DataTable dt, string destinationfile)

{

StringBuilder sb = new StringBuilder();

IEnumerable<string> columnNames = dt.Columns.Cast<DataColumn>().

Select(column => column.ColumnName);

sb.AppendLine(string.Join(",", columnNames));

foreach (DataRow row in dt.Rows)

{

IEnumerable<string> fields = row.ItemArray.Select(field => field.ToString());

sb.AppendLine(string.Join(",", fields));

}

File.WriteAllText(destinationfile, sb.ToString());

}

Data Analytics Consultant in Indianapolis, Indiana

Combine CSV Files without Duplicating Headings using C#

5 Comments

Leave a Reply (Markdown formatting available) Cancel reply