Powershell – Merging multiple CSV files into one using PowerShell

batch filecsvpowershell

Hello I'm looking for powershell script which would merge all csv files in a directory into one text file (.txt) . All csv files have same header which is always stored in a first row of every file. So I need to take header from the first file, but in rest of the files the first row should be skipped.
I was able to find batch file which is doing exactly what I need, but I have more than 4000 csv files in a single directory and it takes more than 45 minutes to do the job.

@echo off
ECHO Set working directory
cd /d %~dp0
Deleting existing combined file
del summary.txt
setlocal ENABLEDELAYEDEXPANSION
set cnt=1
for %%i in (*.csv) do (
 if !cnt!==1 (
 for /f "delims=" %%j in ('type "%%i"') do echo %%j >> summary.txt
) else (
 for /f "skip=1 delims=" %%j in ('type "%%i"') do echo %%j >> summary.txt
 )
 set /a cnt+=1
 )

Any suggestion how to create powershell script which would be more efficient than this batch code?

Thank you.

John

Best Answer

If you're after a one-liner you can pipe each csv to an Import-Csv and then immediately pipe that to Export-Csv. This will retain the initial header row and exclude the remaining files header rows. It will also process each csv one at a time rather than loading all into memory and then dumping them into your merged csv.

Get-ChildItem -Filter *.csv | Select-Object -ExpandProperty FullName | Import-Csv | Export-Csv .\merged\merged.csv -NoTypeInformation -Append
Related Question