Skip to content

Instantly share code, notes, and snippets.

@ctigeek ctigeek/Import-XlsData.ps1
Last active Mar 13, 2018

Embed
What would you like to do?
Query XLS spreadsheet from powershell
function Import-XlsData($filePath) {
## You have to have these drivers...
##https://www.microsoft.com/en-us/download/confirmation.aspx?id=13255
##The first row will be the column names
##This only looks at the first sheet. If you want a different sheet you'll have to adjust the table name retrieved from $schema.
$strProvider = "Provider=Microsoft.ACE.OLEDB.12.0"
$strDataSource = "Data Source=$filePath"
$strExtend = "Extended Properties='Excel 12.0;IMEX=1'"
$objConn = New-Object System.Data.OleDb.OleDbConnection("$strProvider;$strDataSource;$strExtend")
$objConn.open()
$schema = $objConn.GetOleDbSchemaTable([System.Data.OleDb.OleDbSchemaGuid]::Tables, $null)
$table = $schema.Rows[0].TABLE_NAME
$dataTable = New-Object System.Data.DataTable
$command = $objConn.CreateCommand()
$command.CommandText = "select * from [$table]"
$dataAdapter = New-Object -TypeName System.Data.OleDb.OleDbDataAdapter -ArgumentList $command
$dataAdapter.Fill($dataTable) | Out-Null
$DataReader.Dispose()
$objConn.Dispose()
$dataTable.Rows
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.