How to Combine CSV Files and Add a Column in PowerShell

Learn how to efficiently combine multiple CSV files in PowerShell and prepend a column with the original file name for better data management.
---

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Powershell - Combine CSV files and append a column

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Combining CSV Files and Adding a Column in PowerShell

Working with multiple CSV files can be a common task in data management. Often, you may want to combine these files into a single document, while also appending a column that contains the name of the original file each entry came from. For PowerShell newcomers, this can feel daunting. However, we will walk through a streamlined solution step by step.

The Problem

You're attempting to merge several CSV files into one and want to add an extra column that includes the filename for each entry. Initially, you tried using the Import-Csv and Export-Csv cmdlets but found that they don’t have built-in options for adding columns directly. Many users have faced this issue when starting out with PowerShell.

The challenge consists of:

Combining multiple CSV files.

Adding a new column that includes the filepath or filename in each row.

Ensuring the process skips the header row in each file after the first.

The Solution

Step 1: Choose the Right Cmdlets

PowerShell offers powerful cmdlets that can help you manage CSV files:

Get-ChildItem to list files.

Import-Csv to read CSV files.

Export-Csv to save your combined data.

Step 2: Loop Through CSV Files

Instead of attempting to single out lines using complex conditions, we will simplify the process with ForEach-Object and leverage the -PipelineVariable parameter.

Here’s how it works:

Use Get-ChildItem to retrieve the CSV files.

Exclude any existing combined files to prevent duplicate entries.

Import each file's content and append the filename in a new column.

Step 3: Implement the Code

Here’s a concise example of how to accomplish this:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Code:

*Get-ChildItem -Filter .csv -PipelineVariable File: This command lists all CSV files in the current directory, assigning each file to the $File variable for use in the pipeline.

ForEach-Object { Import-Csv $File.FullName }: For each file, it imports its content.

Select *, -{l='OriginalFile';e={$File.Name}}: This selects all existing columns and adds a new column titled OriginalFile, which contains the name of the original file.

Step 4: Testing and Validation

After running the script:

Check that the OriginalFile column accurately reflects the source of each entry.

Conclusion

Combining multiple CSV files and appending a column for filenames in PowerShell is not only feasible but can also be accomplished quite effectively with the right approach. By following these steps, you can streamline your data management processes and enhance your PowerShell skillset at the same time.

Happy scripting!