Powershell, Merging CSV Files with FileNames

Question

Hi everybody i'm trying to merge multiple csv files with same number of columns in one single file with an extra column name that shows the file name, here is an example of what i want:

file01.csv
c01,c02,c03,c04,c05
A,1,4,2,3
B,5,4,6,4

file02.csv
c01,c02,c03,c04,c05
C,2,4,5,6
D,2,4,1,3

Expected result.

merged.csv
c01,c02,c03,c04,c05,Name
A,1,4,2,3,file01.csv
B,5,4,6,4,file01.csv
C,2,4,5,6,file02.csv
D,2,4,1,3,file02.csv

I tried with this code. It merged all the files but it doesn't show me the filename:

Get-ChildItem "*.csv" | % { Import-csv -header c01,c02,c03,c04,c05 $_.FullName | select-object c01,c02,c03,c04,c05,@{e="$_.basename";n="Name" | Export-Csv Merged.csv }

Matt Matt · Accepted Answer · 2015-12-14T01:31:46

Your files and output are not true CSVs which is where you are running into issues. The columns are there but there is a header which must be dealt with. Lets use Get-Content to get the file data and ignore the first line. Then we can convert the remaining data to a CSV object. With each row we can append a value that contains the file name where it originated from. Collect all the update data and prefix the output with "merged.csv"

$path = "d:\temp"
$mergedData = Get-ChildItem $path -Filter "*.csv" | ForEach-Object{
    $file = $_
    # Get the file data
    Get-Content $file.FullName | 
        # Skip the filename
        Select-Object -Skip 1 |
        # Now we can get CSV object in the same way that Import-CSV would generate 
        ConvertFrom-Csv | ForEach-Object{
            # Add the current filename as a column
            $_ | Add-Member -MemberType NoteProperty -Name "Name" -Value $file.Name -PassThru
        }
# Convert back into raw, quoted data
} | ConvertTo-Csv -NoTypeInformation

# Prefix the header before the compiled data
"merged.csv",$mergedData | Set-Content "$path\merged.csv"

Powershell, Merging CSV Files with FileNames

1 Answers