Aug 25 2020 02:30 PM
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!
Aug 26 2020 02:37 AM
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
Oct 11 2021 02:52 AM - last edited on Oct 11 2021 12:11 PM by Eric Starker
Oct 11 2021 02:52 AM - last edited on Oct 11 2021 12:11 PM by Eric Starker
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$]')