Powershell: Import CSV files to Excel

#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

Comments

  1. Need more info on Delimiter. for Example, I have data in this format:
    “data1″,”data2″ ,”data3”

    It does not work with “`t” as show here
    $s1data | ConvertTo-Csv -Delimiter “`t” -NoTypeInformation | Clip

    My Actual Error in PS…….
    ___________________________________________________________________
    PS C:\temp\output> .\csv-to-excel.ps1
    At C:\temp\output\csv-to-excel.ps1:29 char:40
    + $s1data | ConvertTo-Csv -Delimiter “`t” -NoTypeInformation | Clip
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~
    The string is missing the terminator: “.
    At C:\temp\output\csv-to-excel.ps1:29 char:38
    + $s1data | ConvertTo-Csv -Delimiter “`t” -NoTypeInformation | Clip
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Unexpected token ‘`t” -NoTypeInformation | Clip
    $s1.cells.item(1,1).Select()
    $s1.Paste()
    $mySheet ++
    }
    $workbook.SaveAs($FileName)
    $excel.Quit()
    ‘ in expression or statement.
    At C:\temp\output\csv-to-excel.ps1:29 char:38
    + $s1data | ConvertTo-Csv -Delimiter “`t” -NoTypeInformation | Clip
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Missing closing ‘)’ after expression in ‘If’ statement.
    At C:\temp\output\csv-to-excel.ps1:17 char:19
    + $Sheets | ForEach{
    + ~
    Missing closing ‘}’ in statement block.
    At C:\temp\output\csv-to-excel.ps1:15 char:22
    + If($sheetCount -gt 1){
    + ~
    Missing closing ‘}’ in statement block.
    + CategoryInfo : ParserError: (:) [], ParseException
    + FullyQualifiedErrorId : TerminatorExpectedAtEndOfString
    ___________________________________________________________________

Leave a Reply

%d bloggers like this: