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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: