I need to be able to create an Excel document with multiple tabs for a dataset with multiple results\data tables. I have a stored procedure with 3 selects in them, fairly simple ones pulling data from the sys tables in SQL Server.

Below is my script where I am assuming there will be 5 tables, if there are more then it will be missed and if there are less and then the other tabs will be empty.

What's the best way to loop through the dataset and create the Excel document?

$SQLServer = '.\SQLEXPRESS';
$Database = 'AdventureWorks';
$sqlCommand = "SELECT 'Tables' AS DataSetName, * FROM sys.tables

SELECT 'Columns' AS DataSetName, * FROM sys.columns";

$connectionString = "Server=$SQLServer;Database=$Database;Integrated 
$connection = New-Object 
$command = New-Object System.Data.Sqlclient.Sqlcommand($sqlCommand, 
$adapter = New-Object System.Data.sqlclient.SqlDataAdapter $command 
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataSet) > null

foreach($table in $dataset.Tables)
    Write-Host $table.TableName
    if ($($table.TableName) -eq 'table'){$Table1 = $table}
    if ($($table.TableName) -eq 'table1'){$Table2 = $table}
    if ($($table.TableName) -eq 'table2'){$Table3 = $table}
    if ($($table.TableName) -eq 'table3'){$Table4 = $table}
    if ($($table.TableName) -eq 'table4'){$Table5 = $table}

$DataToGather = @{
    table = {$Table1}
    table1 = {$Table2}
    table2 = {$Table3}
    table3 = {$Table4}
    table4 = {$Table5}

Export-MultipleExcelSheets -Show -AutoSize 'C:\Temp\Test.xlsx' $DataToGather

  • 1
    \$\begingroup\$ Welcome to Code Review! Can you confirm that the code is complete and that it functions correctly? If so, I recommend that you edit to add a summary of the testing (ideally as reproducible unit-test code). If it's not working, it isn't ready for review (see help center) and the question may be deleted. \$\endgroup\$ – Toby Speight Feb 21 '19 at 15:08
  • \$\begingroup\$ Thanks a lot @TobySpeight, the code does work but would like a better solution to eliminate the use of fixed tables. Rather want to iterate through the dataset. Ive tried below example by Lot Pings but getting some issues which I will post. Will also update the script so anyone can run it on against their SQL instance. \$\endgroup\$ – The Faan Feb 22 '19 at 6:38

If the Excel sheet names should retain the original TableNames this could do:
(can't test myself)
The code contains two commented out alternatives.

# your code upto $connection.Close() ...

$DataToGather = @{}  
foreach ($table in $dataset.Tables) {
    Write-Host $table.TableName
    Add-Member -InputObject $DataToGather -Name $table.TableName -Value $table 
#   Add-Member -InputObject $DataToGather -Name $table.TableName -Value $dataset.Tables['$($table.TableName)']
#   $DataToGather.Add($table.TableName,$table)

Export-MultipleExcelSheets -Path 'C:\Temp\Test.xlsx' -InfoMap $DataToGather -Show -AutoSize 

  • \$\begingroup\$ getting the below when running the script with your snippet added. Table cmdlet Add-Member at command pipeline position 1 Supply values for the following parameters: MemberType: \$\endgroup\$ – The Faan Feb 22 '19 at 12:56
  • \$\begingroup\$ Try with -MemberType NoteProperty \$\endgroup\$ – LotPings Feb 22 '19 at 13:48
  • \$\begingroup\$ Getting this now. Invoke-Item : Cannot find path 'C:\Temp\Test.xlsx' because it does not exist. At C:\Program Files\WindowsPowerShell\Modules\ImportExcel\5.4.4\ImportExcel.psm1:525 char:17 + if ($Show) {Invoke-Item $Path} \$\endgroup\$ – The Faan Feb 25 '19 at 8:15
  • \$\begingroup\$ Can't get this to work correctly, still getting the above error. \$\endgroup\$ – The Faan Mar 4 '19 at 13:55
  • \$\begingroup\$ Sorry, but without a similar environment I can't test myself. \$\endgroup\$ – LotPings Mar 5 '19 at 10:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.