Home > CSV, Excel, PowerShell > Combine csv’s into one Workbook

Combine csv’s into one Workbook

February 2nd, 2012 admin

Lately I was looking into the issue of combining all csv files in a folder into a single workbook.
One could do this in vba but it needed to be a scheduled overnight process.
As I recently discovered PowerShell I decided to give it a fair chance.

This is the script I came up with and to be fair it works like a charm.
Some minor tweaks need to be done but I couldn’t resist the post because it has been a while.

$files = get-childitem 'D:\Data\'

$datafiles = $files | where {$_.extension -eq ".csv"}

$Excel = New-Object -Com Excel.Application

$Excel.ScreenUpdating = $false
$Excel.DisplayAlerts = $false

$WorkBook = $Excel.Workbooks.Open('D:\Data\'+$datafiles[0].name)

$mv = [System.Reflection.Missing]::Value

for ($index = 1; $index -lt $datafiles.Count; $index++)
{
 $WorkSheet = $WorkBook.Sheets.Add($mv,$mv,$mv,'D:\Data\'+$datafiles[$index].name)
 $WorkSheet.Cells.EntireColumn.AutoFit()
}

$Excel.Visible = $True
$Excel.DisplayAlerts = $True
$Excel.ScreenUpdating = $True
Categories: CSV, Excel, PowerShell Tags: , ,
Comments are closed.