Exporting data from the Excel data model into an Excel table or as CVS file to be used in SQL Server

Copper Contributor

Hello: I have not been able to find a way to export an existing large table currently in the Excel data model (power pivot) either to an Excel table, or as a .csv file, or some other way to get it into SQL server. Any advice? Thank you!

3 Replies

@cbsoria 

I'm not sure how to import into SQL server, but in any case you shall to load first the data model table into Excel sheet. Here it depends on how it was loaded to data model.

If by Power Query, right click on query name in right pane and Load to table.

 

If it was loaded by Power Pivot connector, you may use data connections. First create any dummy table in Excel sheet like this one

image.png

After that Data->Existing connections, select this dummy table and load it as table into the sheet.

image.png

Right click on it and select

image.png

Here keep command type Table and type the name of the table in data model if you'd like to download it as it is. Otherwise select DAX from drop-down menu and use DAX query expression to return transformed table starting from EVALUATE.

image.png

As result data model table will be loaded

image.png

You can also use OPENROWSET to import excel file in sql server.

SELECT * INTO Your_Table FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\temp\MySpreadsheet.xlsx',
'SELECT * FROM [Data$]')