SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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?

SQLvariant

Aaron Nelson ( blog | twitter ) is a Microsoft MVP for SQL Server (Data Platform) and leads the PowerShell Virtual Chapters of PASS, and volunteers for the local PASS Chapter AtlantaMDF, and helps organize SQL Saturday events in Atlanta. The PowerShell VC of PASS hosts monthly sessions on SQL Server and PowerShell, and you can find the recordings of those sessions on their YouTube channel.

Comments

Leave a comment on the original post [sqlvariant.com, opens in a new window]

Loading comments...