Merge multiple csv file with same header using powershell

Question

I am having multiple csv files in a folder with data like below

file1

"Index","Response","Status","Time"
"32190","2","Succeeded","2023-01-18 08:31:34.9"
"32189","3","Succeeded","2023-01-18 08:26:34.9"
"32188","3","Succeeded","2023-01-18 08:21:34.9"

file2

"Index","Response","Status","Time"
"32190","2","Succeeded","2023-01-19 08:31:34.9"
"32189","3","Succeeded","2023-01-19 08:26:34.9"
"32188","3","Succeeded","2023-01-19 08:21:34.9"

Need to merge these files into a single csv with a one header

"Index","Response","Status","Time"
"32190","2","Succeeded","2023-01-18 08:31:34.9"
"32189","3","Succeeded","2023-01-18 08:26:34.9"
"32188","3","Succeeded","2023-01-18 08:21:34.9"
"32190","2","Succeeded","2023-01-19 08:31:34.9"
"32189","3","Succeeded","2023-01-19 08:26:34.9"
"32188","3","Succeeded","2023-01-19 08:21:34.9"

I have this below code but I am not able to get single header in it

$folder = 'D:\reports\daily_csv' 
$files = Get-ChildItem $folder\*.csv 
Get-Content $files | Set-Content "D:\Monthly\Merged_$prev_month.csv"

Please let me know what I need to add here to avoid multiple headers

Why not just: `Get-ChildItem $folder\*.csv |Import-Csv $_ |Export-Csv D:\Monthly\Merged_$prev_month.csv`? — iRon, Feb 08 '23 at 14:04
I only just now realized that your (good) question is a duplicate. — mklement0, Feb 08 '23 at 15:41
Good point, @iRon, though you have an extra `$_` in there; additionally, a workaround for a bug is required in Windows PowerShell: `Import-Csv -LiteralPath (Get-ChildItem $folder\*.csv).FullName | ...` or - less robustly - `(Get-ChildItem $folder\*.csv).FullName | Import-Csv` — mklement0, Feb 08 '23 at 15:49

score 3 · Accepted Answer · answered Feb 08 '23 at 13:46

Here is one way to do it using StreamReader and an anonymous function. Note that .OpenText() initializes the StreamReader with UTF8 encoding, if that's a problem you can use StreamReader(String, Encoding) instead.

$folder = 'D:\reports\daily_csv'
Get-ChildItem $folder\*.csv | & {
    begin { $isFirstObject = $true }
    process {
        try {
            $reader  = $_.OpenText()
            $headers = $reader.ReadLine()

            if($isFirstObject) {
                $headers
                $isFirstObject = $false
            }

            while(-not $reader.EndOfStream) {
                $reader.ReadLine()
            }
        }
        finally {
            if($reader) {
                $reader.Dispose()
            }
        }
    }
} | Set-Content path\to\mergedCsv.csv

Nice - didn't know that `System.IO.FileInfo` instances have an `.OpenText()` method. — mklement0, Feb 08 '23 at 14:38

mklement0 · Answer 2 · 2023-02-08T15:53:03.123

Santiago Squarzon's helpful plain-text-processing answer is definitely your best option, both in terms of performance, and in that it also preserves the formatting specifics (whether all fields or even only some fields are double-quoted or not).

A slower, but more convenient alternative that doesn't preserve the formatting specifics (which should not matter, however) is to use Import-Csv's support for multiple input files, via its -LiteralPath parameter:

Import-Csv -LiteralPath (Get-ChildItem D:\reports\daily_csv -Filter *.csv).FullName |
  Export-Csv -NoTypeInformation -Encoding utf8 "D:\Monthly\Merged_$prev_month.csv"

Note that neither -NoTypeInformation nor -Encoding utf8 are required anymore for Export-Csv in PowerShell (Core) 7+, unless you need a different encoding (BOM-less UTF-8 is now the consistent default; if you do need a BOM, use -Encoding utf8bom).

Also note that a bug has been fixed in PowerShell (Core) 7+ that enables providing the Get-ChildItem results to Import-Csv via the pipeline:

# PS 7+ ONLY - a bug in WinPS prevents Get-ChildItem input to Import-Csv
Get-ChildItem D:\reports\daily_csv -Filter *.csv |
  Import-Csv |
  Export-Csv "D:\Monthly\Merged_$prev_month.csv"

Merge multiple csv file with same header using powershell

2 Answers2