Forum Discussion
Make worksheet name dynamic using OLEDB provider in PowerShell script
Rachna1860 Wow, that's a round about way of doing something simple. Not sure what the thinking was to do this through SQL server Agent.
For the most part, there is a PowerShell module out there to do just about anything. One just needs to find it.
In this case there is ImportExcel,
Install-Module ImportExcelWith this you could change the body of your script as follows. Assuming there is only 1 worksheet in the workbook.
#Assuming all filename variables have been initialized
#Import the excel file into a PS Object array
$XL = Import-Excel -Path $strFileName
#Export to thge CSV file
$XL | Export-Csv $CSVFile -NoTypeInformationYou could then run the script as a scheduled task as opposed to an SQL Agent job.
To show how the file in imported I imported an example file. This is how the object looks in PowerShell
PS /home/cwilliams@BBC.Local/Documents> $xl = Import-Excel -Path ./Book1.xlsx
PS /home/cwilliams@BBC.Local/Documents> $xl
ID Name Address Phone
-- ---- ------- -----
1 John Doe 123 Any Street 999-999-9999
2 John Doe 124 Any Street 999-999-10000
3 John Doe 125 Any Street 999-999-10001
4 John Doe 126 Any Street 999-999-10002
5 John Doe 127 Any Street 999-999-10003
6 John Doe 128 Any Street 999-999-10004
7 John Doe 129 Any Street 999-999-10005
8 John Doe 130 Any Street 999-999-10006
9 John Doe 131 Any Street 999-999-10007
10 John Doe 132 Any Street 999-999-10008
11 John Doe 133 Any Street 999-999-10009
12 John Doe 134 Any Street 999-999-10010
13 John Doe 135 Any Street 999-999-10011
14 John Doe 136 Any Street 999-999-10012
15 John Doe 137 Any Street 999-999-10013
16 John Doe 138 Any Street 999-999-10014
17 John Doe 139 Any Street 999-999-10015
18 John Doe 140 Any Street 999-999-10016
19 John Doe 141 Any Street 999-999-10017
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 -NoTypeInformationI 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 .xlsWhat can be the issue, does this Import Excel module not support reading a ".xls" file as well ?
