Powershell - Combine CSV files and append a column

Question

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
}

TheMadTechnician · Accepted Answer · 2022-03-01 23:12:08Z

5

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

answered Mar 1, 2022 at 23:12

TheMadTechnician

36.5k3 gold badges48 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

James Over a year ago

Great, thank you! I want to prepend the file base name to each row, so I was able to switch it around a bit to work.

Santiago Squarzon · Accepted Answer · 2022-03-01 23:12:55Z

4

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

answered Mar 1, 2022 at 23:12

Santiago Squarzon

65.5k5 gold badges26 silver badges59 bronze badges

Collectives™ on Stack Overflow

Powershell - Combine CSV files and append a column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related