Save multiple CSV files as XLS

Tag: excel , powershell , csv , xls Author: redflag1985 Date: 2014-01-28

I'm a total noob to PS and I'm having trouble trying to save a folder of CSV files to XLS or XLSX. I can do it for a single file, but can't work out what I'm doing wrong to do it multiple times.

CLS
$path = "H:\My Documents\"
$files = Get-ChildItem $path -include *.csv -recurse
echo $files
foreach($file in $files) {
$objExcel=New-Object -com "Excel.Application" 
$objWorkbook=$objExcel.workbooks.open($file)
$objWorksheet=$objWorkbook.Worksheets.Item(1)

#insert COUNTIF formula into cell A8
$strFormula = "=COUNTIF(I6:I1000," + [char](34) + ">0" + [char](34) + ")"
$objExcel.Cells.Item(1, 8).Formula = $strFormula

#Save as XLS
$xlout=$file.Replace('.csv','xlsx') 
$objWorkbook.SaveAs($xlOut,51) 

$objWorkbook.Close()
$objExcel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)
}

Can someone help me please?

Best Answer

Well, I don't see anything fundamentally wrong, but it may be having issues with Excel opening and closing in rapid succession. Try moving your loop to within the time frame of opening and closing Excel.

CLS
$path = "H:\My Documents\"
$files = Get-ChildItem $path -include *.csv -recurse
echo $files
$objExcel=New-Object -com "Excel.Application" 
foreach($file in $files) {
    $objWorkbook=$objExcel.workbooks.open($file)
    $objWorksheet=$objWorkbook.Worksheets.Item(1)

    #insert COUNTIF formula into cell A8
    $strFormula = "=COUNTIF(I6:I1000," + [char](34) + ">0" + [char](34) + ")"
    $objExcel.Cells.Item(1, 8).Formula = $strFormula

    #Save as XLS
    $xlout=$file.FullName.Replace('.csv','.xlsx') 
    $objWorkbook.SaveAs($xlOut,51) 

    $objWorkbook.Close()
}
$objExcel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)

Edit:
I updated $xlout to pull the FullName element, and also fixed it's replacement since it was losing the . and basically changing "somefile.csv" into "somefilexlsx".

comments:

No luck - I get: Exception calling "Replace" with "2" argument(s): "Unable to find the specified file." At line:15 char:5 + $xlout=$file.Replace('.csv','xlsx') + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : FileNotFoundException
Exception calling "SaveAs" with "2" argument(s): "The file could not be accessed. Try one of the following: • Make sure the specified folder exists. • Make sure the folder that contains the file is not read-only. • Make sure the file name does not contain any of the following characters: < > ? [ ] : | or * • Make sure the file/path name doesn't contain more than 218 characters."
At line:16 char:5 + $objWorkbook.SaveAs($xlOut,51) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : ComMethodTargetInvocation
The strange thing is, for a single file I can do it thus: $xlout=$csvpath.Replace('.csv','xlsx') $objWorkbook.SaveAs($xlOut,51) $objExcel.Quit() and that works perfectly.
And that works perfectly! So apart from my missing the ".", it needed the fullname element in there and performing the loop after opening excel? Thank you!