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.


How to Open Large CSV Files in Excel: 2 Easy Ways

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

power query ribbon options to open large csv files in excel

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

power query window to open large csv files in excel

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

import data options to open large csv files in excel through power query

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

queries and connections to open large csv files in excel through power query

You can now open the queries and connections here.

open large csv files in excel using power query

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

Read More: Open CSV File in Excel Without Formatting


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.

open large csv files in excel through splitting in third party website

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

keeping header and skipping options in third party website

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

download or advanced options in third party website for opening large csv files in excel

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

open large csv files in excel through splitting files in third party websites

Thus you will have the split file in your mail.

Read More: How to Read CSV File in Excel


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.


Related Articles


<< Go Back to Open CSV in Excel | Import CSV to Excel | Importing Data in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Abrar-ur-Rahman Niloy
Abrar-ur-Rahman Niloy

Abrar-ur-Rahman Niloy, holding a B.Sc. in Naval Architecture and Marine Engineering, has contributed to Exceldemy for nearly 1.5 years. As a leader in Excel, VBA, and Content Development teams, he authored 114+ articles and assisted the Exceldemy forum. Presently, as a project writer, he prioritizes stepping out of his comfort zone, aiming for constant technical improvement. Niloy's interests encompass Excel & VBA, Pivot Table, Power Query, Python, Data Analysis, and Machine Learning libraries, showcasing his commitment to diverse... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo