-
Notifications
You must be signed in to change notification settings - Fork 393
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
PassThru fails with Send-SQLDataToExcel for a SQL query that returns 0 rows #703
Comments
Can you inspect the output of the SQL data before piping it? If there is no data, set it to |
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:
Example 2 Send-SQLDataToExcel:
If you apply my changes in the OP to the function then both examples will work without failing. |
@theotherhouse , this is by design, sorry I designed it ... basically 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
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 |
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 The current line 173 needs to change to:
Thanks!! Gary. |
@theotherhouse not merged yet. Will let you know. Glad it worked out. |
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). |
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 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
I think a more consistent rendering of the logic for
Thanks again for considering my issue and the quick response. Cheers, Gary. |
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.
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.
|
(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 withPassThru
then the chain is broken when any one of the queries returns 0 rows. Additionally, if theSend-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.)
Am I doing it wrong?
The text was updated successfully, but these errors were encountered: