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:


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:


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.

26 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 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


    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 ).

    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 |

  9. Pingback: Comparing The Performance Of Reading Data From Files With File.Contents And Web.Contents In Power Query And Power BI « Chris Webb's BI Blog

  10. Using CSV files, how do you overcome dates coming through as text? I recently had one source file which I was not able to convert in Power Query, nor Excel, to convert the date to serial number/date formatting.

  11. Pingback: Chris Webb's BI Blog: New Option To Solve Problems With Power Query Not Loading All Data From An Excel Worksheet Chris Webb's BI Blog

  12. Hey Chris! Just wondering how does the speed of CSV files compare to accessing a table in the current workbook? (i.e. Excel.CurrentWorkbook())

  13. Hello Chris, ran into performance trouble when importing from SP folder XLSX files exactly as you have analyzed here. Not much trouble in the beginning but when projects get larger you have to wait for 30min to load data this way. So motivated by your hints I’ve spent some time in shifting some benchmark files from XLSX to CSV file import. Performance seems to be much better indeed, but now nasty format and regional setting errors show up where PBI XLSX data connector never has complaint about.

    Problem 1: double quotes cause line breaks.
    Solution: Transform Sample File >> Applied Steps >> Gear
    Comma-Separated Values >> Line breaks >> Ignore quoted line breaks

    Problem 2. my regional settings are German but delimiters set to US comma and semincolon for some good reasons. Decimal numbers are not imported correctly >> error (automatic type conversion) or text string (everything you can save manually)
    1st trial: File >> Options & Settings >> Options >> Current File >> Regional Settings >> Locale for Import >> Englisch (United States)
    Then all dates are misinterpreted
    2nd trial: Type conversion to string >> Add custom col >> =Number.FromText([Xyz], “en-US” >> correct col result.

    But hey, this CSV cleansing stuff by manual operations is cumbersome and by no way robust. So I am afraid I’ll further move on to wait for my XLSX imports or do you have some panacea to a M code novice?

Leave a Reply to SAM Cancel reply

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

%d bloggers like this: