How to Combine CSV Files 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.