Forum Discussion
Exporting data from the Excel data model into an Excel table or as CVS file to be used in SQL Server
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
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$]')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
After that Data->Existing connections, select this dummy table and load it as table into the sheet.
Right click on it and select
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.
As result data model table will be loaded
https://www.edureka.co/community/28113/how-export-power-queries-from-one-workbook-another-with-vba
