Forum Discussion
Make worksheet name dynamic using OLEDB provider in PowerShell script
Clifra_Jones Thanks for the response. My file contains only one worksheet to read. I installed Import Excel module but looks like it does not support reading an .xlt file. I got attached error message when trying to convert '.xlt' file to CSV format.
Is there something that I am doing wrong ? The requirement is to read an ".xlt" file with different worksheet name every time and convert it into "CSV" format. We do not have excel installed on the server where the SQL server agent job is run hence cannot use "COM" object to read the ".xlt" file and also need to use SQL server agent job as this is the standard convention followed in the team, cannot deviate from it.
Clifra_Jones I tested the below simple script to convert xls file (Microsoft Excel 97-2003 worksheet) to CSV format.
$strFileName = "C:\Test\BM.xls"
$CSVFile = "C:\Test\BM.csv"
$XL = Import-Excel -Path $strFileName
#Export to the CSV file
$XL | Export-Csv $CSVFile -NoTypeInformation
I am getting below error message -
Import-Excel does not support reading this extension type .xls
At C:\Program Files\WindowsPowerShell\Modules\ImportExcel\7.1.1\Public\Import-Excel.ps1:99 char:21
+ ... throw "Import-Excel does not support reading this extensi ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OperationStopped: (Import-Excel do...nsion type .xls:String) [], RuntimeException
+ FullyQualifiedErrorId : Import-Excel does not support reading this extension type .xls
What can be the issue, does this Import Excel module not support reading a ".xls" file as well ?