Request: Query a spreadsheet with OLEDB

[royashbrook]

Any chance/interest adding a feature to pull data using oledb? I have some situations where I query data from a several sources, including some spreadsheets, and I use sql files for those queries, so it just makes sense to do the same thing for the spreadsheets. Keeps my project cleaner.

I use something like below to do this today:

function Read-OleDbData([string]$ConnectionString,[string]$SqlStatement){
    $DataTable = new-object System.Data.DataTable
    $DataAdapter = new-object System.Data.OleDb.OleDbDataAdapter $SqlStatement,$ConnectionString
    $null = $DataAdapter.Fill($DataTable)
    $null = $DataAdapter.Dispose()
    $DataTable.Rows | Select-Object $DataTable.Columns.ColumnName
}
$ReadDataArgs = @{
    SqlStatement = Get-Content query.sql -Raw
    ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=file.xlsx;Extended Properties='Excel 12.0 Xml;HDR=NO;IMEX=1;'"
}
$Results = Read-OleDbData @ReadDataArgs

Then I handle $Results as needed. This assumes that query.sql has an oledb query like below.

select
    ROUND(F1) as [A]
from
    [sheet1$A1:A1]

For something like this, maybe this wouldn't make sense, but I have some more complicated use cases that make the SQL easier to manage.

[dfinke]

Interesting idea for sure. Feels like it opens a can of worms for supporting it. Thanks for the suggestion, I will play with and think about it.

[charliekunkel]

I imagine you could just use Invoke-SQLCmd to output to a delimited file, load the output into into a variable, then use ImportExcel module on that...?

Invoke-Sqlcmd -Database $DBName -ServerInstance $DBServer -Username $UID -Password $PWD -Query $SQL | export-csv -delimiter $Delim -path c:\temp\output.txt -notype

[royashbrook]

I imagine you could just use Invoke-SQLCmd to output to a delimited file, load the output into into a variable, then use ImportExcel module on that...?

Invoke-Sqlcmd -Database $DBName -ServerInstance $DBServer -Username $UID -Password $PWD -Query $SQL | export-csv -delimiter $Delim -path c:\temp\output.txt -notype

@charliekunkel Thanks for the comment. I am using oledb against a spreadsheet, not going from sql. We use something like below, normally, for sql to excel:

$dt = Invoke-Sqlcmd @sqlargs
$dt |
  Select-Object $dt.Columns.ColumnName |
  Export-Excel $file -TableStyle Medium6 -AutoSize -NoNumberConversion *

where $file is the name of an output file and @sqlargs comes from a config file and looks something like this in json:

    "sql": {
        "QueryTimeout": 1800,
        "ConnectionString": "YourConnectionString",
        "InputFile": "get-data.sql",
        "OutputAs": "DataTables"
    },

Our standard job template is here which is where I use it. It's super basic.

For Excel to 'something else' I use what I've submitted.

[royashbrook]

Interesting idea for sure. Feels like it opens a can of worms for supporting it. Thanks for the suggestion, I will play with and think about it.

@dfinke - 👍 I thought about trying to find where to plug it in and submitting a pull request, but figured I would submit a feature request first just in case it looked like too much of a can of worms. I like to hope anyone that is using oledb to query from sql instead of just using the methods available has a good reason like me. The errors that come back from oledb are sometimes less than helpful.

I would have submitted it as just another command similar to these and put some caveat emptor language in the command help. Probably added a number of examples to help others out.

Previously, I used a custom module with epplus.dll and just had this in there, but since I switched to ImportExcel, I just use the commands above in the handful of places where I need to. Maybe no one else in the world has a need for it, but I thought if anyone did, this module would be the best place for that functionality.

[dfinke]

@royashbrook cool. Maybe a PR to add a small prototype? Plus some pester tests, and example? It's been a long time since I use OleDb. I envision fielding lots of questions on constructing the sql, that's my primary concern.

[royashbrook]

@royashbrook cool. Maybe a PR to add a small prototype? Plus some pester tests, and example? It's been a long time since I use OleDb. I envision fielding lots of questions on constructing the sql, that's my primary concern.

@dfinke 👍 Will do. Yeah, I think it would be like 'Here are the examples that are tested. Good luck!' =P

[dfinke]

@royashbrook haha, yeah, there are some examples that are exactly that. Cool, looking forward to playing with it!

[royashbrook]

@royashbrook haha, yeah, there are some examples that are exactly that. Cool, looking forward to playing with it!

@dfinke pr submitted. 👍