How to Combine CSV Files in PowerShell

1. The CSV Format
2. Filter the .csv Files in PowerShell
3. Fetch Each .csv File Object in PowerShell
4. Merge the Content of CSV Files in PowerShell
5. Write the Merged Content to a Text File in PowerShell

There can be a requirement to merge several CSV files and write the data into a text file in PowerShell. The Import-Csv and Export-Csv cmdlets can be used to perform this operation.

The CSV Format

There are various types of file formats available for different purposes. The CSV uses a simple structure to store data.

It can be used to exchange data among different programs. As its name CSV(comma-separated values) implies, data is comma-separated.

The information stored in a CSV file looks like the following.

UserName,Identifier,First Name,Last Name
jak12,1001,Harry,Smith
Rock90,1002,Jeremy,Hamilton
Rick23,1003,Rakesh,Donas
User01,1004,Lokie,Ferguson

The delimiter can be a space, semicolon or any other character.

Let’s create two csv files that we will merge using PowerShell.

username.csv content:

username2.csv content:

Filter the .csv Files in PowerShell

It is recommended to use the Get-ChildItem cmdlet to fetch all the .csv file types in the specified location. The -Filter parameter can be passed to filter the files with the .csv extension.

The folder path to the .csv files might be different in your case.

$csvFilePath = "D:\csvfiles"
$csvFileList = Get-ChildItem $csvFilePath -Filter *.csv

Let’s check the content available in the $csvFileList.

$csvFileList

Output:

Fetch Each .csv File Object in PowerShell

Let’s retrieve each file object with its full name. The Select-Object cmdlet can be used to retrieve each object with its expanded properties.

We are piping the previous step output to the Select-Object command.

$csfFilesToImport = $csvFileList | Select-Object -ExpandProperty FullName

When you display the $csfFilesToImport variable, it can be seen that both the CSV file objects are available with the full name.

$csfFilesToImport

Output:

Merge the Content of CSV Files in PowerShell

Since we got all the CSV files, let’s import the content of each file. This would easily merge the content with a common header row.

We can use the Import-CSV to construct table-like objects from the piped CSV file contents.

$importedCsvFiles = $csfFilesToImport | Import-Csv
$importedCsvFiles

Output:

The information has been merged from the two CSV files provided.

Write the Merged Content to a Text File in PowerShell

We can transform the above objects into CSV strings and save them to a text file. The Export-Csv cmdlet is available to use.

We should use the Append parameter to ensure the content is not replaced and appended.

$importedCsvFiles | Export-Csv D:\merged.txt -NoTypeInformation -Append

Output:

This should create a new text file called merged.txt. That file content would be merged from the above two CSV files.