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.
$sourcefolder = "C:\Users\DELL\Desktop\work\csv" $sourcefiles = Get-ChildItem -Path $sourcefolder -Filter *.csv $sourcefiles
Directory: C:\Users\DELL\Desktop\work\csv Mode LastWriteTime Length Name ---- ------------- ------ ---- -a---- 1/4/2023 7:46 PM 52 students_group1.csv -a---- 1/4/2023 7:47 PM 46 students_group2.csv
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.
ipcsv students_group1.csv | ft -AutoSize
Name Marks ---- ----- Ayesha 80 Burhan 95 Usman 79 Haram 60
ipcsv students_group2.csv | ft -AutoSize
Name Marks ---- ----- Amna 60 Fizza 75 Sana 89 Ali 88
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:
$sourcefolder = "C:\Users\DELL\Desktop\work\csv"
$sourcefiles = Get-ChildItem -Path $sourcefolder -Filter *.csv
$result = @()
foreach ($file in $sourcefiles) {
$data = Import-Csv $file.FullName
$result += $data
}
$result | Export-Csv "C:\Users\DELL\Desktop\work\csv\merged.csv" -NoTypeInformation
$result
Name Marks ---- ----- Ayesha 80 Burhan 95 Usman 79 Haram 60 Amna 60 Fizza 75 Sana 89 Ali 88
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:
$result | Export-Csv "C:\Users\DELL\Desktop\work\csv\merged.csv" -NoTypeInformation
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.
# Import the data from the first CSV file $csv_file1 = Import-Csv 'C:\Users\DELL\Desktop\work\csv\students_group1.csv' # Import the data from the second CSV file $csv_file2 = Import-Csv 'C:\Users\DELL\Desktop\work\csv\students_group2.csv' # Concatenate the data from the two CSV files $merged_csv = $csv_file1 + $csv_file2 # Export the merged data to a new CSV file $merged_csv | Export-Csv 'C:\Users\DELL\Desktop\work\csv\merged_csv_File.csv' -NoTypeInformation $merged_csv
Name Marks ---------- Ayesha 80 Burhan 95 Usman 79 Haram 60 Amna 60 Fizza 75 Sana 89 Ali 88
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.
#path to save merged CSV file
$outputCsv = "C:\Users\DELL\Desktop\work\csv\result.csv"
#path to read CSV files
$inputCsvs = get-childItem "C:\Users\DELL\Desktop\work\csv\*.csv"
#read and write CSV header
[System.IO.File]::WriteAllLines($outputCsv,[System.IO.File]::ReadAllLines($inputCsvs[0])[0])
#read and append file contents except for the header
foreach ($csv in $inputCsvs) {
$lines = [System.IO.File]::ReadAllLines($csv)
#append all text to $outputCsv file
[System.IO.File]::AppendAllText($outputCsv, ($lines[1..$lines.Length] | Out-String))
}
#import $outputCsv to see merged content
ipcsv $outputCsv | ft -AutoSize
Name Marks ---------- Ayesha 80 Burhan 95 Usman 79 Haram 60 Amna 60 Fizza 75 Sana 89 Ali 88
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.
