Skip to content

Instantly share code, notes, and snippets.

@ctigeek
Last active March 13, 2018 18:13
Show Gist options
  • Save ctigeek/264621937cafa1989e0cbcabc122567c to your computer and use it in GitHub Desktop.
Save ctigeek/264621937cafa1989e0cbcabc122567c to your computer and use it in GitHub Desktop.
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