Export AdventureWorksDW2017 to Excel for a Power BI Demo with Export-Excel

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.

AdventureWorksDW2017_InExcel

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?

Please Share This:

Share on facebook
Share on twitter
Share on linkedin

You may also like:

2 Responses

  1. One option that I have used successfully is to link each tab in the workbook to a table in the database, using Excel’s built-in embedded connectivity. It is less code and does not depend on having any module, etc., installed.

    1. Leo,
      Thanks for the comment!

      How long does that take you to link all of those tables? After you’re done, is the data stored inside the Excel Workbook(s)?

      One of the things I like about this approach is that it doesn’t depend on having Excel installed. All I need it the ImportExcel module and I’m able to export all that data to an Excel file. No add-ins, no drivers, just one module.

Leave a Reply to Leo Cohn Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Follow:

Subcribe to Blog Via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

What I'm Saying on Twitter

Subscribe via feedburner