Have you ever wanted to export an entire SQL Server database to Excel file?  Yeah, me neither.  Until yesterday, when I was trying to build a Power BI demo with sample data (that needed to come from files, not a db) I have never even considered doing such a thing.

Turns our, it’s extremely easy to do with the ImportExcel PowerShell module.

Obviously, you have to have the module installed, and a copy of AdventureWorksDW2017 db restored to a SQL Server.  After that,  all you have to do is loop through the tables, ‘query’ them with the Read-SqlTableData cmdlet, and pipe the results to the Export-Excel cmdlet.

I did some trial and error with this yesterday.  I settled on exporting all of the Dimension tables to separate Worksheets within the same Excel file, and exporting all of the Fact tables to their own individual files (since they tend to be much larger).

I also tried out all tables in one file, as well as all tables in individual file.  I created a gist with all of these options.

If you decide to try this out yourself, the most things to do are to:

• Install the SqlServer & ImportExcel modules
• Import both the SqlServer & ImportExcel modules into your PowerShell session
• Change the name of the SQL Server instance in both the SQL Provider, and in the Read-SqlTableData cmdlet

#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            
}

If you end up giving this a try, I would LOVE for you to comment and tell me how easy or hard it was for you to do?