Combine CSV Files without Duplicating Headings using C#

On: 2017-01-27

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!

Previous Post: Delete SSAS Partitions with TMSL and PowerShell

Next Post: Read the Top N Rows from Large Text Files with C#

7 Comments

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());

}

On the line that reads, “File.ReadLines(filePaths[i]).Skip(1).Select(line => line.Split(splitter)).ForEach(spline =>” I’m getting the error below. Does anyone know what is wrong? Unfortunately my Linq is rusty.

Error CS1061 ‘IEnumerable<string[]>’ does not contain a definition for ‘ForEach’ and no accessible extension method ‘ForEach’ accepting a first argument of type ‘IEnumerable<string[]>’ could be found (are you missing a using directive or an assembly reference?)

Thanks in advance

James

3 years ago

Permalink

Reply
1. File.ReadLines(filePaths[i]).Skip(1).Select(line => line.Split(splitter)).ToList().ForEach(spline =>
  
  ameliaatlinwasis
  
  1 year ago
  
  Permalink
  
  Reply

Author

That sounds very useful, Declan. Thanks for sharing the code!

Chris Koester

5 years ago

Permalink

Reply
very cool, i was just writing this exact thing myself, and your idea of merging mismatched header is genius

omegauo

4 years ago

Permalink

Reply
is there a reason this would not work with an XLSX, as apposed to CSV? i changed the file extension type within the code, and attempted to combine excel spreadsheets instead, but the file keeps getting “corrupted” when attempting to open, however, when attempted with a CSV it worked fine.

Ulices

3 years ago

Permalink

Reply
UwU i wonder right now, did we get other way to do this? or have library for it? But in anway this post have helped me much :3 Thanks guys

Đỗ Quốc Cường

2 years ago

Permalink

Reply

Modern data platform and engineering in Azure

Combine CSV Files without Duplicating Headings using C#

7 Comments

Leave a Reply (Markdown formatting available) Cancel reply