"File not loaded completely"

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 file into different files

• Overcome Excel limitations

• Use a specific software

Split your CSV into different files

Splitting your CSV into several smaller files allow you to open these files directly with Excel.

The easiest way to do this is to use a dedicated solution. The best free one we found (and use) is CSV Splitter, click here to download it. Other solutions exist like Free Huge CSV Splitter.

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:

1. Download the CSV file on your computer
2. To start, open Excel with a blank spreadsheet and look at the "Data" tab
3. 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".
4. 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.
5. 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.
6. 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...
7. 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.
8. 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.
9. In the Insert tab, click on PivotTable:
10. 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
11. 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.

1. Follow steps 2 to 4 above
2. At step 4, click on Edit to open Power Query. Edit your data as you want and then click on Load To... 
3. Follow from Step 6.

See also : Automatic Financial Reports