Forum Discussion
Make worksheet name dynamic using OLEDB provider in PowerShell script
I have a PowerShell script that reads an excel template file from a shared drive, copies it to another location and converts it to CSV format. This script is being run through a SQL server agent job.
Below is the script, it is using OLEDB provider as we don't have excel installed on that server where the CSV file will be created.
The script is running fine and doing intended job, but in this script the worksheet name is predefined. But now the excel template file that we receive is having a different worksheet name every time. Need help in making the worksheet dynamic in below code.
PowerShell script :
New-PSDrive -Name "UNCPath" -PSProvider "FileSystem" -Root "\\ttpr-loc\share3"
$OrgnFile = "UNCPath:\Shared\1208201_Test.xlt"
$CSVFile = "C:\Files\1208201_Test.csv"
$OrgnFileExists = Test-Path $OrgnFile
If ($OrgnFileExists -eq $True)
{
Copy-item "UNCPath:\Shared\1208201_Test.xlt" –destination "C:\Files\1208201_Test.xlt" -Force
$strFileName = "C:\Files\1208201_Test.xlt"
$strSheetName = 'Test$'
$strProvider = "Provider=Microsoft.ACE.OLEDB.12.0"
$strDataSource = "Data Source = $strFileName"
$strExtend = "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';"
$strQuery = "Select * from [$strSheetName]"
$objConn = New-Object System.Data.OleDb.OleDbConnection("$strProvider;$strDataSource;$strExtend")
$sqlCommand = New-Object System.Data.OleDb.OleDbCommand($strQuery)
$sqlCommand.Connection = $objConn
$objConn.open()
$da = New-Object system.Data.OleDb.OleDbDataAdapter($sqlCommand)
$dt = New-Object system.Data.datatable
[void]$da.fill($dt)
$objConn.close()
$dt | Export-Csv $CSVFile -NoTypeInformation
}
Else
{
Exit
}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-10017Clifra_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 ?
