Excel contains over one million rows – 1,048,576 to be exact. If your file is larger than that and you will try to open this file in Excel, you are going to get the following message.
After you click OK, you can move to the last row (Ctrl + Down Arrow) and see that the worksheet is filled to the last row.
Open large CSV
There is a solution in Excel. You can’t open big files in a standard way, but you can create a connection to a CSV file. This works by loading data into Data Model, keeping a link to the original CSV file. This will allow you to load millions of rows.
Here’s how to do it.
- Navigate to Data >> Get & Transform Data >> From File >> From Text/CSV and import the CSV file.
- After a while, you are going to get a window with the file preview.
- Click the little triangle next to the load button.
- Select Load To…
- Now, we need to create a connection and add data to the Data Model. This won’t load data to an Excel sheet because we have a limit of about a million rows there.
- Click OK. This will take a while to load.
- On the right side, you have the name of our file and the number of rows. If you save the file, you will notice that its size increased significantly.
- Double-click this area to open Power Query Editor.
- Now, if you scroll down, you will notice that the new rows are added on the go.
- To change a value, right-click a cell and select Replace Values.
- It’s going to replace all “builders” with “roofers” under the “profession” column.