Comparing The Performance Of CSV And Excel Data Sources In Power Query

My posts from two weeks ago (see here and here) on using Process Monitor to troubleshoot the performance of Power Query queries made me wonder about another question: how does the performance of reading data from CSV files compare to the performance of reading data from Excel files? I think most experienced Power Query users in either Power BI or Excel know that Excel data sources perform pretty badly but I had never done any proper tests. I’m not going to pretend that this post is a definitive answer to this question (and once again, I would be interested to hear your experiences) but hopefully it will be thought-provoking.

To start off, I took the 153.6MB CSV file used in my last few posts and built a simple query that applied a filter on one text column, then removed all but three columns. The query ran in 9 seconds and using the technique from my last post I was able to draw the following graph from a Process Monitor log file showing how Power Query reads data from the file:

image

Nothing very surprising there. Then I opened the same CSV file in Excel and saved the data as an xlsx file with one worksheet and no tables or named ranges; the resulting file was only 80.6MB. Finally I created a duplicate of the first query and pointed it to the Excel file instead. The resulting query ran in 59 seconds – around 6 times slower! Here’s a comparison with the performance of this query with the first query:

image

The black line in the graph above is the amount of data read (actually the offset values showing where in the file the data is read from, which is the same thing as a running total when Power Query is reading all the data) from the Excel file; the green line is the amount of data read from the CSV file (the same data shown in the first graph above). A few things to mention:

  • Running Process Monitor while this second query was refreshing had a noticeable impact on its performance – in fact it was almost 20 seconds slower
  • The initial values of 80 million bytes seem to be where data is read from the end of the Excel file. Maybe this is Power Query reading some file metadata? Anyway, it seems as though it takes 5 seconds before it starts to read the data needed by the query.
  • There’s a plateau between the 10 and 20 second mark where not much is happening; this didn’t happen consistently and may have been connected to the fact that Process Monitor was running

In any case you don’t need to study this too hard to understand that the performance of reading data from an xlsx format Excel file is terrible compared to the performance of reading data from a CSV. So, if you have a choice between these two formats, for example when downloading data, it seems fair to say that you should always choose CSV over xlsx.

12 responses

  1. Hi Chris

    I see exactly the same but haven’t tested it as properly as you 🙂

    In order to convert the Excel files I use https://cloudconvert.com API to CSV files, and save them to Azure Blob storage. It actually works quite good.

    Let me know if you want a copy of the flow

    BR
    Erik

    Let me know if

  2. Pingback: Performance Test: Loading CSV Versus Loading Excel In Power Query – Curated SQL

  3. Thanks for these three posts. Really helping a lot when PQ users are facing the options between Excel and CSV.

    I’d recently experienced LONG really LONG Refresh time for getting data from (seven) Excel files in a folder. Guess how long? It’s 10+ minutes…

    I realised that (after reading your posts) the potential problem is the number of worksheets in each Excel file, and probably the number of Excel files in the folder. There are four worksheets in each file but I need only two. And there are 7 files in the folder (one file for a quarter).

    What I did were to combine the 7 files into three (with more data in each file); and keep only the two worksheets I need in the three files. With this simple modification, the Refresh time took around 2 minutes. Although it’s still relatively long, it’s a huge improvement comparing to 10+ minutes.

    I believe the performance may get better if the files are converted into CSV… will try that out later.

    Thanks for pointing to the right direction. 🙂

  4. Dear Chris, I thought that excel files are better than csv files, until I read your article. Great Observation.

    I had the opportunity to attend the webinar in which you explained M language code of PQ with so much simplicity. May I request you to share the recording link.

  5. Try with xlsb – its worse than xlsx – But this is a know issue as per Microsoft – because the xlsb file is using an Access Driver to read the data – Strange no ?.

  6. Hi Chris
    The fact that BI works better with csv sources than Excel sources, it was reported long time ago (see https://www.excelando.co.il/en/analyzing-power-query-performance-source-large-files/#blog ).

    But it is the first time I can see a measure about it that can be easily replicated.

    And There is another point worth mentioning that I figured out thanks to your last posts:
    “Union (append) CSVs is not a cause for slowness” is not true.
    The location of the CSV file can be very important: when the csv sources sit on a remote location (server, NAS), each access to the tiniest file may take some time. For 1 file, it is not noticeable but for many files, it makes a big difference.
    In some queries, I used to use 1 csv file with currenry rates for each day. The csv files were very small (5k) but 5 years of files means 1826 files to access at every refresh !!!
    Now I made yearly csv files. Only 5 csv file to access. The same query takes now a few seconds while it used to take motre than a minute.

  7. Pingback: Power BI Release Notes, Visual filters, RLS and more... (August 6, 2018) | Guy in a Cube

  8. Pingback: Comparing The Performance Of CSV And Excel Data Sources In Power Query | Pardaan.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: