Merge Multiple CSV Files in PowerShell

Using Import-Csv and Export-Csv

Before merging multiple .csv files, let’s write a script to see how many .csv files are in the specified directory.

For that, create a variable called $sourcefiles containing a list of all the CSV files in the directory. Next, use the Get-ChildItem cmdlet to retrieve all the CSV files in the directory. You can do this by using the following commands.

We can see two .csv files (students_group1.csv and students_group2) in the given directory, which is C:\Users\DELL\Desktop\work\csv in our case; make sure to update the directory with yours having your CSV files. But first, let’s see the content available in each .csv file.

Above, we can see the students_group1.csv and students_group2.csv files contained two columns (Name, Marks) and four rows having information about student names and marks. Here, we used the ipcsv command to read the content of CSV files, which is an alias for Import-Csv in PowerShell.

Now, we can use a foreach loop to iterate through each .csv file in the $sourcefiles list and merge them into a single .csv file. Here is an example code of how to do this:

In this code snippet, the Import-Csv cmdlet was used to read the contents of the .csv file into a $data variable. The $result variable is then updated to include the data from each .csv file. The $result contained merged data available in both CSV files.

Finally, we can use the Export-Csv cmdlet to write the merged data to a new CSV file. Here is an example of how to do this:

After executing the above code, we can observe a new CSV file called merged.csv in the given directory (C:\Users\DELL\Desktop\work\csv ) with the merged data from all of the CSV files in the directory.

Note: This will only work if these two CSV files have the same structure (i.e. they have the same number of fields/columns, and the data in each column is of the same type).

Using + operator

Use + operator to concatenate two CSV files in PowerShell.

We have already learned about the Import-Csv and Export-Csv cmdlets that read the source files’ contents and write the merged data to a new CSV file, respectively.

Here, a new file is created as merged_csv_File.csv in the specified path, containing the combined data from csv_file1.csv and csv_file2.csv (see the output above).

Using the File Class

Use the ReadAllLines() and WriteAllLines() methods of the File class to merge two CSV files having the same header in PowerShell.

After execution of the above script, we can see the merged file is created in the specified directory C:\Users\DELL\Desktop\work\csv as result.csv.

In the above example, we have created two variables, $inputCsvs contains the path of the directory where the CSV files (students_group1.csv and students_group2.csv) are located that we want to merge, and the $outputCsv variable created to store the merged CSV files data.

Here, the ReadAllLines() method of the System.IO.File class was used to read the header of the first CSV file in the array $inputCsvs and write it to merged CSV ($outputCsv) using WriteAllLines() method.

• In Powershell, WriteAllLines creates a new file, write specified string array to the file, and then closes it.
• ReadAllLines opens a file, reads all lines of the file, and then closes it.

After that foreach loop was used to iterate through each CSV file in the directory. Inside the loop, we used the ReadAllLines() method again to read all lines of the CSV files while the AppendAllText() method was used to append all data to the merged CSV file ($outputCsv).

That’s all about how to merge multiple CSV Files in PowerShell.