Powershell - Combine CSV files and append a column

user_2844703 0
Viewed 238

I'm trying (badly) to work through combining CSV files into one file and prepending a column that contains the file name. I'm new to PowerShell, so hopefully someone can help here.

I tried initially to do the well documented approach of using Import-Csv / Export-Csv, but I don't see any options to add columns.

Get-ChildItem -Filter *.csv | Select-Object -ExpandProperty FullName | Import-Csv | Export-Csv CombinedFile.txt -UseQuotes Never -NoTypeInformation -Append

Next I'm trying to loop through the files and append the name, which kind of works, but for some reason this stops after the first row is generated. Since it's not a CSV process, I have to use the switch to skip the first title row of each file.

$getFirstLine = $true

Get-ChildItem -Filter *.csv | Where-Object {$_.Name -NotMatch "Combined.csv"} | foreach {
    $filePath = $_

$collection = Get-Content $filePath  
    foreach($lines in $collection) {
        $lines = ($_.Basename + ";" + $lines)
    }

    $linesToWrite = switch($getFirstLine) {
           $true  {$lines}
           $false {$lines | Select -Skip 1}

    }
    
    $getFirstLine = $false
    Add-Content "Combined.csv" $linesToWrite
}
powershell
2 Answers

This is where the -PipelineVariable parameter comes in real handy. You can set a variable to represent the current iteration in the pipeline, so you can do things like this:

Get-ChildItem -Filter *.csv -PipelineVariable File | Where-Object {$_.Name -NotMatch "Combined.csv"} | ForEach-Object { Import-Csv $File.FullName } | Select *,@{l='OriginalFile';e={$File.Name}} | Export-Csv Combined.csv -Notypeinfo
user_32457490

Merging your CSVs into one and adding a column for the file's name can be done as follows, using a calculated property on Select-Object:

Get-ChildItem -Filter *.csv | ForEach-Object {
    $fileName = $_.Name
    Import-Csv $_.FullName | Select-Object @{
        Name = 'FileName'
        Expression = { $fileName }
    }, *
} | Export-Csv path/to/merged.csv -NoTypeInformation
user_153395440
Related