Merges csv files from directory into a single csv file PowerShell

Question

How can I run one single PowerShell script that does the following in series?

1. Adds a the filename of all csv files in a directory as a column in the end of each file using this script:

   Get-ChildItem *.csv | ForEach-Object {
   $CSV = Import-CSV -Path $_.FullName -Delimiter ","
   $FileName = $_.Name
   
   $CSV | Select-Object *,@{N='Filename';E={$FileName}} | Export-CSV $_.FullName -NTI -Delimiter ","}
   

2. Merges all csv files in the directory into a single csv file

3. Keeping only a header (first row) only from first csv and excluding all other first rows from files.

   Similiar to what kemiller2002 has done here, except one script with csv inputs and a csv output.

Answer 1

Bill's answer allows you to combine CSVs, but doesn't tack file names onto the end of each row. I think the best way to do that would be to use the PipelineVariable common parameter to add that within the ForEach loop.

Get-ChildItem \inputCSVFiles\*.csv -PipelineVariable File |
  ForEach-Object { Import-Csv $_ | Select *,@{l='FileName';e={$File.Name}}} |
  Export-Csv \outputCSVFiles\newOutputFile.csv -NoTypeInformation

That should accomplish what you're looking for.

Answer 2

This is the general pattern:

Get-ChildItem \inputCSVFiles\*.csv |
  ForEach-Object { Import-Csv $_ } |
  Export-Csv \outputCSVFiles\newOutputFile.csv -NoTypeInformation

Make sure the output CSV file has a different filename pattern, or use a different directory name (like in this example).

Answer 3

If your csv files dont have always same header you can do it :

$Dir="c:\temp\"

#get header first csv file founded
$header=Get-ChildItem $Dir -file -Filter "*.csv" | select -First 1 | Get-Content -head 1

#header + all rows without header into new file
$header, (Get-ChildItem $Dir -file -Filter "*.csv" | %{Get-Content $_.fullname | select -skip 1}) | Out-File "c:\temp\result.csv"