Forum Discussion
Fast Load "export" to Excel
Hi JustDavid
Thank you for reaching out to the Microsoft Fabric Forum Community.
ralf_anton Stachu MarkLaf pcoley Thanks for the inputs. Suggestions from users are valuable.
The slowness is mainly because Excel has to write millions of rows into worksheet cells, which naturally takes time. Power Query preview feels fast since it only shows a small sample of the data, but when loading, Excel must write the entire dataset. To improve it slightly, you could ensure the filtering happens in MS Access (using saved queries) so only the needed rows are retrieved, set Excel calculation to Manual, and disable background refresh during the load. Even with these steps, exporting 12M rows to worksheets will still take some time, as the limitation is mostly on Excel’s side rather than Power Query.
If there are any deviations from your expectation please let us know we are happy to address.
Thanks.
Please take into account the previous responses of MarkLaf , Stachu , Stachu and ralf_anton where it was suggested not to load the 12 million records into Excel worksheets. It is not efficient, and Excel is not designed for handling that volume of data. It is better to use Power BI or another tool that supports large-scale data processing.
In any case, if it must be done in Excel, a much better option is to load the data only into the data model (without physically loading the data into worksheets) and extract aggregated values as needed using PivotTables.
I thank everyone for their inputs.
I am completely agree that it is definitely not the best option to load all the records that is in MS Access to MS Excel into 12 different sheets in the same workbook at 1M each.
However, the point that I'm trying to learn here is not about best practise, but to understand if there's a way to make the load to excel faster.
As in this ask, there's no filtering etc (except filter to each period in order to load to a sheet), but just an export of records to excel. Due to the slowness of "exporting", am thinking if there are ways to make it faster.
Hi JustDavid
Thank you for reaching out to the Microsoft Fabric Forum Community.
ralf_anton Stachu MarkLaf pcoley Thanks for the inputs. Suggestions from users are valuable.
The slowness is mainly because Excel has to write millions of rows into worksheet cells, which naturally takes time. Power Query preview feels fast since it only shows a small sample of the data, but when loading, Excel must write the entire dataset. To improve it slightly, you could ensure the filtering happens in MS Access (using saved queries) so only the needed rows are retrieved, set Excel calculation to Manual, and disable background refresh during the load. Even with these steps, exporting 12M rows to worksheets will still take some time, as the limitation is mostly on Excel’s side rather than Power Query.
If there are any deviations from your expectation please let us know we are happy to address.
Thanks.
Hi JustDavid
Thank you for reaching out to the Microsoft Fabric Forum Community.
I hope the information provided was helpful. If you still have questions, please don't hesitate to reach out to the community.
Hi JustDavid
Hope everything’s going smoothly on your end. I wanted to check if the issue got sorted. if you have any other issues please reach community.
JustDavid wrote:However, the point that I'm trying to learn here is not about best practise, but to understand if there's a way to make the load to excel faster.
Die Methode wurde beinahe in jedem Post geschildert. Hier nochmal kurz zusammengefasst:
* Alle Access-Daten mit EINER PQ-Abfrage (in PQ oder Power BI Desktop) importieren.
* Die Daten nicht in eine Exceltabelle laden!!!
(auch nicht in 3, 4,12 oder wie vielen auch immer...)
* Das Ergebnis der Abfrage ausschließlich ins Datenmodell laden lassen!!!
* Mit Power Pivot auf das Datenmodell zugreifen und die Daten hier, mit Power Pivot verarbeiten.
