Forum Discussion

JustDavid's avatar
JustDavid
Helper IV
6 months ago
Solved

Fast Load "export" to Excel

Hello PQ Gurus,   I have a MS Access file from IT that has 12 million rows (for simplicity).   Due to the limit of rows in Excel, I'm trying to export these 12 million rows into 12 sheets (1 mill...
Need Help
Show and Tell
  • v-priyankata's avatar
    v-priyankata
    5 months ago

    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.

pcoley's avatar
pcoley
Super User
6 months ago

JustDavid 

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.

JustDavid's avatar
JustDavid
Helper IV
6 months ago

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.