Day: January 4, 2013

Powershell: Import CSV files to Excel

by ucinfo, posted in MsOffice, Powershell

#Powershell

So Ari asked if I could knock up a script that would import csv files in to Excel .. and here it is.  It uses the Excel object model, so excel and powershell need to be on the box you this from

Basically copy the csv files you want to your “rootfolder” of choice and run the script.  It uses the filename for the tab name.

$RootFolder = “C:temp”
$filename = “$RootFolderExcelSpreadsheet.xls”
$files = dir -Path $RootFolder *.csv

$excel = new-object -comobject Excel.Application
$excel.visible = $true
$workbook = $excel.workbooks.add()
$sheets = $workbook.sheets
$sheetCount = $Sheets.Count
$mySheet = 1
$mySheetName = “Sheet” + $mySheet
$s1 = $sheets | where {$_.name -eq $mySheetName }
$s1.Activate()

If($sheetCount -gt 1){
#Delete other Sheets
$Sheets | ForEach{
$tmpSheetName = $_.Name
$tmpSheet = $_
If($tmpSheetName -ne “Sheet1”){$tmpSheet.Delete()}
}
}

ForEach($file in $files){
If($mySheet -gt 1){$s1 = $workbook.sheets.add()}
$s1.Name = $file.BaseName
$s1.Activate()
$s1Data = Import-Csv $file.FullName
$s1data | ConvertTo-Csv -Delimiter “`t” -NoTypeInformation | Clip
$s1.cells.item(1,1).Select()
$s1.Paste()
$mySheet ++
}

$workbook.SaveAs($FileName)
$excel.Quit()

 

Let me know what you think