When you try to open large CSV files, you see the message : "File not loaded completely".
That means the file size is too big for Excel to open it.
In that case, we suggest 3 solutions :
Split your CSV into different files
Splitting your CSV into several smaller files allow you to open these files directly with Excel.
This solution has a downside: it won't allow you to create Pivot tables of the complete data. If that's a problem, use Solution #2. If you want to extract a specific part of data (for example, only one of your artists or one of your releases), use Solution #3
Overcome Excel limitations
This trick might seem a bit complex at first glance but it is a great workaround for which you don't need a solution other than Excel. This video show you how it's done:
If it's too fast, follow the steps below:
- Download the CSV file on your computer
- To start, open Excel with a blank spreadsheet and look at the "Data" tab
- The command we use is the second on that tab, called "New Query". In some recent pre-release versions of Excel that might show up as "Get Data". Select "New Query", then "From File", then "From Text/CSV".
- Browse your computer and select the CSV you want to open and... click on Open. Once the file is opened, a preview of the file is displayed.
- VERY IMPORTANT STEP: Here, 2 solutions depending on whether you want to Edit (or at least control check) your data or not.
➔ If you don't want to edit your data, click on the small arrow on the right of the Load button and click on Load To... then go directly to step 7.
➔ If you want to Edit your data, click on Transform Data and follow the next step.
- Perform all the actions you want on the data set. Once you have finished them, click on the arrow below the Load & Close button and select Close and Load To...
- In the "Load To" dialog, select "Add this data to the Data Model". Then, select the option "Only Create Connection" that has just become selectable:
Why is this necessary? By doing this, we're not loading the data to an Excel sheet/table but to a Data Model that can be exploited via Excel. This is crucial, since the sheet has the 1-million-row limit, but the Data Model doesn't.
- After that, click "Load". This start loading the whole large file, whatever the number of lines, no matter if it exceeds Excel's capacity:
It might take a while, be patient as Excel loads the data. Once it is done, the data set (called a "query") is connected to the Excel sheet.
- In the Insert tab, click on PivotTable:
- In the pop-in window, select "Use an external data source" then click on the Choose Connection... button and select the query you loaded under Connections in this Workbook
- Your PivotTable is created and can be used as any PivotTable!
Use a specific software
Some softwares are specifically made to manage large CSV files, edit large data set or even extract a part of it. One is actually included within Excel: it is called Power Query.
- Follow steps 2 to 4 above
- At step 4, click on Edit to open Power Query. Edit your data as you want and then click on Load To...
- Follow from Step 6.
See also : Automatic Financial Reports