#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.
$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
flaphead.com 
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
___________________________________________________________________