PassThru fails with Send-SQLDataToExcel for a SQL query that returns 0 rows

Question

PassThru fails with Send-SQLDataToExcel for a SQL query that returns 0 rows

theotherhouse opened this issue 5 years ago · 8 comments

(Thanks to everyone involved in creating and maintaining this library. I am so glad it exists. It has been super helpful. NB: I am a novice Powershell user.)

I am trying to populate more than one sheet in a workbook with the contents of a series of views in SQL. The views are controlled by someone else and may contain 0 rows. If no rows are returned then the sheet still needs to be populated with the column names.

The Send-SQLDataToExcel function checks the length of the returned DataTable and emits a warning that there is no data to insert if the count is 0 and skips the call to Export-Excel. This causes two problems for me. Firstly if I chain the multiple calls with PassThru then the chain is broken when any one of the queries returns 0 rows. Additionally, if the Send-SQLDataToExcel statements are run independently the header row is not sent to Excel for any query that returns 0 rows.

If I change line 166 of the Send-SQLDataToExcel function to the following then I see the behaviour that I expect/desire/require.

if ($DataTable) {

Is there a reason that lines 166 to 171 could not be replaced with either the above change, or the following? (Indentation removed here for clarity.)

#Call export-excel removing parameters which relate to the SQL query, and keeping the rest.
'Connection' , 'Database'  , 'Session' , 'MsSQLserver' , 'SQL'  , 'DataTable'  , 'QueryTimeout'  | ForEach-Object {$null = $PSBoundParameters.Remove($_) }
Export-Excel  @PSBoundParameters -InputObject $DataTable

#Warn the user if the query returned 0 data rows
if     ($DataTable.Rows.Count -eq 0) {Write-Warning -Message ' SQL query returned 0 data rows.' }

Am I doing it wrong?

Answer 1 · 2019-10-29T22:55:49.000Z

Can you inspect the output of the SQL data before piping it? If there is no data, set it to [missing] or some other indicator and then flow it down the pipeline?

Answer 2 · 2019-10-30T01:16:08.000Z

There are ways of working around the issue. But it seems odd that the first example below would work without failing but the second example fails. If Export-Excel itself does not fail why should the helper function Send-SQLDataToExcel fail.

Example 1 Export-Excel:

$path = "$env:TEMP\emptyDataFrame1.xlsx"
Remove-Item -Path $path -Force -EA Ignore
$p = Get-Process | Where-Object {$_.WorkingSet -lt 1} | Export-Excel -Path $path -WorksheetName ProcessesNone-AutoSize -FreezeTopRow -AutoFilter -BoldTopRow -ClearSheet -PassThru
$p = Get-Process | Export-Excel -ExcelPackage $p -WorksheetName ProcessesAll -AutoSize -FreezeTopRow -AutoFilter -BoldTopRow -ClearSheet -PassThru
Export-Excel -ExcelPackage $p -show -AutoSize

Example 2 Send-SQLDataToExcel:

$path = "$env:TEMP\emptyDataFrame2.xlsx"
Remove-Item -Path $path -Force -EA Ignore
$p = Send-SQLDataToExcel -MsSQLserver -Connection localhost -SQL  "select name,type,type_desc from [master].[sys].[all_objects] where 1 = 0" -Path $path -WorkSheetname MasterNone -AutoSize -FreezeTopRow -AutoFilter -BoldTopRow -ClearSheet -PassThru
$p = Send-SQLDataToExcel -MsSQLserver -Connection localhost -SQL  "select name,type,type_desc from [master].[sys].[all_objects]" -ExcelPackage $p -WorkSheetname MasterAll -AutoSize -FreezeTopRow -AutoFilter -BoldTopRow -ClearSheet -PassThru
Export-Excel -ExcelPackage $p -show -AutoSize

If you apply my changes in the OP to the function then both examples will work without failing.

Answer 3 · 2019-10-30T11:25:07.000Z

@theotherhouse , this is by design, sorry I designed it ... basically
(something with no results ) | export-excel
will create a blank sheet. This means we can make modifications to the workbook Export-Excel without sending any data...
Currently when Send-SQLDataToExcel gets a table with zero rows or no table at all it doesn't call Export-Excel IIRC, the original idea was to avoid deleting a populated sheet if a query fails. In the (something with no results ) | export-excel case if overwriting with blank is a problem we expect people to do $result = (something); if ($result is not empty) {$result | export-Excel)

When Export-excel is passed a table it calls the underlying library which has direct insert method for tables, and I've just done a very quick test, that does allow a table with zero rows, and does put in column headings, which means there is no reason why Send-SQLDataToExcel can't send an empty table.

I've made a snap decision and added a -Force switch to Send-SQLDataToExcel, as a by-product of that I've also improved the error trapping, so now:

-Force not specified or query runs and returns at least one row - same behaviour as before
-Force specified:

If query returns zero rows, - the empty data table sent to Export-Excel, headers inserted.
If the query fails, or for any other reason the table has no columns null is sent to export-excel this should result in an empty sheet,

Some things may have problems with either a blank sheet or a sheet with just a header row using the -Force switch says you take responsibility for those :-)

This is in my repo now, and I'm going to send @dfinke a pull request soon would you like to try the new version and see if does what you want before I do that ?

Cheers
James

Answer 4 · 2019-10-30T15:50:49.000Z

Thanks @jhoneill

That is excellent. That revised design works for me.. And I can see that @dfinke has already pulled your change into master. Thanks for the swift response.

NB: The current implementation fails though becasue the Force parameter has not been included in the list to strip before sending `Export-Excel'.

The current line 173 needs to change to:

'Connection' , 'Database'  , 'Session' , 'MsSQLserver' , 'SQL'  , 'DataTable'  , 'QueryTimeout', 'Force'  |

Thanks!!

Gary.

Answer 5 · 2019-10-30T16:13:18.000Z

@theotherhouse not merged yet. Will let you know. Glad it worked out.

Answer 6 · 2019-10-30T16:18:52.000Z

Fixed the that little bug.

Also fixed some tests which were breaking. I have not added new tests for the extra functionality yet. Will try to do that before the day is out. (Quite a few plates spinning at the moment).

Answer 7 · 2019-10-30T19:14:19.000Z

Thanks @jhoneill and @dfinke ,

The current design works for me. Thanks for the rapid turn around.

I do still think that my original concern about the -PassThru is still valid with the behaviour of Send-SQLDataToExcel being different to the base Export-Excel behavior.

The following example will result in two sheets being created in the same workbook one blank. This would not be the case if the calls to 'Export-Excel' were replaced with calls to Send-SQLDataToExcel with or without the new -Force parameter.

$path = "$env:TEMP\emptyDataFrame3.xlsx"

$sheet1 = ConvertFrom-Csv @"
Region,Item,TotalSold
"@

$sheet2 = ConvertFrom-Csv @"
Region,Item,TotalSold
South,drill,100
"@

Remove-Item $path -ErrorAction SilentlyContinue

$p = $sheet1 | Export-Excel $path -WorksheetName Sheet1 -PassThru
$p = $sheet2 | Export-Excel -ExcelPackage $p -WorksheetName Sheet2 -PassThru

Export-Excel -ExcelPackage $p -show -AutoSize

I think a more consistent rendering of the logic for Send-SQLDataToExcel would be:

#Remove parameters which relate to the SQL query, and keeping the rest for subsequent call to Export-Excel
'Connection' , 'Database'  , 'Session' , 'MsSQLserver' , 'SQL'  , 'DataTable'  , 'QueryTimeout', 'Force'  |
	ForEach-Object {$null = $PSBoundParameters.Remove($_) }
#if force was specified export even if there are no rows. If there are no columns the query failed and export "null"
if ($DataTable.Rows.Count) { 
	   Export-Excel  @PSBoundParameters -InputObject $DataTable 
}
elseif ($Force -and $DataTable.Columns.Count) {
	   Write-Warning -Message ' SQL Query returned 0 rows, headers exported using -Force.'
	   Export-Excel  @PSBoundParameters -InputObject $DataTable 
}
else { 
	Write-Warning -Message ' No Data to insert.'
	Export-Excel  @PSBoundParameters -InputObject $null 
}

Thanks again for considering my issue and the quick response.

Cheers,

Gary.

Answer 8 · 2019-10-30T23:43:00.000Z

I'll add those as well.

Yes there are inconsistencies. Export-Excel is pipeline focused, begin , process for each object, and end. Which are open a workbook and create a sheet, insert headers for the first object, and data for each object, "final tasks", formatting, tables / pivots / charts etc. and saving.

$sheet1 = ConvertFrom-Csv @"
Region,Item,TotalSold
"@

returns null. No headings to work with, so piped to Export-Excel it will do the begin and end parts, and you get a blank sheet.

Send-SQLData is much more of a traditional "Get-Data". "Put-Data-in-Excel" way of working. The data goes to excel as a one table object - Export Excel doesn't fill in cells, or write headers, it just says "Here's a table put it at cell X of sheet Y"

The old way meant not using passthru, and re-opening the excel file each time, because if there was no data nothing got sent.
You said you needed headers, and an empty table isn't null, it has the structure without any data rows, so asking for that to be inserted gives just a header - which wasn't possible with an empty CSV.
If I needed a blank sheet without headers I'd run without the -force switch and do

$p = send ... -passthru
if (-not $p) {$P = $null | export .... -passthru }