Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
You can’t perform that action at this time.