Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Merging multiple CSV files into one using PowerShell

Tags:

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

like image 246
john50 Avatar asked Jan 11 '15 22:01

john50


1 Answers

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 
like image 58
stinkyfriend Avatar answered Sep 19 '22 13:09

stinkyfriend