How to Open Large CSV Files in Excel (2 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

CSV stands for comma-separated values. These are widely used plain text files because of the simplicity of their creation and usability on different platforms. These files can also contain a large amount of data, making them large in file size. Although these files are suitable on other platforms, many users still prefer to use old-school Microsoft Excel for its simplicity and flexibility of the usage. But in some cases, the CSV files are so large that Excel doesn’t even open them. In this article, we will focus on how to open large CSV files in Excel.

Table of Contents hide

2 Easy Ways to Open Large CSV Files in Excel

1. Using Excel Power Query Editor

2. Splitting CSV File Online

Conclusion

2 Easy Ways to Open Large CSV Files in Excel

The main method to open large CSV files in Excel is to use the power pivot. Also, there are some online platforms that help both in opening the file and splitting them to open them in Excel. As the main problem with not opening these types of files is the limitation of Excel columns and row numbers (compared to the CVS files), splitting them sometimes resolves the issue too.

Below are the two methods you can follow to open large CSV files in Excel.

1. Using Excel Power Query Editor

Excel had a power pivot add-in that we could use to construct data models, set up connections, and perform calculations. Then came the power query that Microsoft maintained until Excel 2013. Although it is not maintained anymore, you can still use it to open large CSV files in Excel.

You can follow these steps to use Excel’s power query to open large CSV files.

Steps:

First, go to the Data tab on your ribbon.
Then select Get Data and hover your mouse cursor over From File and then select From Text/CSV.

Now browse your local file and select the CSV file.
After that, a window will open up. You will find the Load option at the bottom there. Now click on the downward-facing arrow beside it.

Among the drop-down menu, select Load To.
As a result, the Import Data box will open up.
Under the Select how you want to view this data in your workbook, select Only Create Connection.
At the bottom, check Add this data to the Data Model.

Finally, click on OK and give it some time to load.
You can now find the queries and connections under the window with the same name on the right of the worksheet.

You can now open the queries and connections here.

You can now work with your CSV file through this window here.

Similar Readings

2. Splitting CSV File Online

One of the reasons Excel can’t open large CSV files is that the software doesn’t permit having a row number exceeding 1,048,576. Although this may seem a large number, it is still relatively small compared to modern-day datasets that analysts often use. So another practical solution would be to break the CSV file into multiple small ones that will contain a lesser number of rows than Excel can contain.

There are websites that offer such functionalities free of charge nowadays. One viable option for this case would be to use the splitcsv.

Steps:

First, go to the site and select Choose File as shown in the figure, and select your file.

Now give it some time to upload. Once the upload is complete, click on Split Options.

On the next page, you can select your preferred Header Rows and Skip Rows options on top of the page.

At the bottom of the page, you will find the Output Details option which will help you go to the next page.

Now you can choose to download the file directly as shown in the figure, or you can go to the more advanced option depending on your preference.

In case you opt for more advanced options, you will find the following page next. Here, you can add, remove, or change the order of the column.

Next, you can find the Confirm Order option at the bottom of the page.

After that, you will find the details of the file again. Now select Split at the bottom of the page with your email id.

Thus you will have the split file in your mail.

Conclusion

So these were the methods we can follow to open large CSV files in Microsoft Excel. I hope you can open your files with ease now. If you are still having problems opening large files, it would be a wiser option to opt for other platforms that can handle such large data. Hopefully, you have found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.

For more guides like this, visit Exceldemy.com.