Export every table in the AdventureWorksDW2017 db to Excel
#Requires -Modules SqlServer | |
#Requires -Modules ImportExcel | |
<# The AdventureWorksDW2017 only has 29 tables and they're all under 1 million rows.#> | |
cd SQLSERVER:\SQL\LocalHost\SQL2017\Databases\AdventureWorksDW2017\Tables | |
<# Scenario #1 A) all Dimensions in a single file, | |
and B) each Fact table in their own file. #> | |
<# A) Every Dimension table in a worksheet named after the table, the same Excel file #> | |
dir | WHERE { $_.name -like 'dim*' } | | |
foreach { | |
"$($_.Name)" | |
Read-SqlTableData -ServerInstance LocalHost\SQL2017 -DatabaseName AdventureWorksDW2017 -SchemaName dbo -TableName $_.Name -OutputAs DataRows | | |
Export-Excel -Path "c:\temp\AW\AdventureWorksDW2017_Dims.xlsx" -WorksheetName $_.Name -ExcludeProperty RowError,RowState,Table,ItemArray,HasErrors | |
} | |
<# B) Each Fact-table in it's own Excel file, named after the table. #> | |
dir | WHERE { $_.name -like 'fact*' } | | |
foreach { | |
"$($_.Name)" | |
Read-SqlTableData -ServerInstance LocalHost\SQL2017 -DatabaseName AdventureWorksDW2017 -SchemaName dbo -TableName $_.Name -OutputAs DataRows | | |
Export-Excel -Path "c:\temp\AW\$($_.Name).xlsx" -WorksheetName $_.Name -ExcludeProperty IsReadOnly,IsFixedSize,IsSynchronized,SyncRoot,Count | |
} | |
<# Scenario #2 Each table in it's own Excel file, named after the table. #> | |
dir | | |
foreach { | |
"$($_.Name)" | |
Read-SqlTableData -ServerInstance LocalHost\SQL2017 -DatabaseName AdventureWorksDW2017 -SchemaName dbo -TableName $_.Name -OutputAs DataRows | | |
Export-Excel -Path "c:\temp\AW\$($_.Name).xlsx" -WorksheetName $_.Name -ExcludeProperty IsReadOnly,IsFixedSize,IsSynchronized,SyncRoot,Count | |
} | |
cd SQLSERVER:\SQL\LocalHost\SQL2017\Databases\AdventureWorksDW2017\Tables | |
<# Scenario #3 Every table in a worksheet named after the table, all in the same Excel file #> | |
dir | | |
foreach { | |
"$($_.Name)" | |
Read-SqlTableData -ServerInstance LocalHost\SQL2017 -DatabaseName AdventureWorksDW2017 -SchemaName dbo -TableName $_.Name -OutputAs DataRows | | |
Export-Excel -Path "c:\temp\AW\AdventureWorksDW2017.xlsx" -WorksheetName $_.Name -ExcludeProperty RowError,RowState,Table,ItemArray,HasErrors | |
} | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment