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.
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.
- 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.
Read More: Open CSV File in Excel Without Formatting (2 Easy Ways)
- How to Compare 2 CSV Files in Excel (6 Easy Ways)
- How to Sort CSV File in Excel (2 Quick Methods)
- Convert CSV to Excel Automatically with Easy Steps
- [Solved:] Excel Is Opening CSV Files in One Column (3 Solutions)
- Excel VBA: Read a Text File Line by Line (6 Related Examples)
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.
- 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.
Read More: Formatting CSV File in Excel (With 2 Examples)
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.
- How to Open CSV with Delimiter in Excel (6 Simple Ways)
- How to Merge CSV Files in Excel (2 Easy Ways)
- Difference Between CSV and Excel Files (11 Suitable Examples)
- How to View CSV File in Excel (3 Effective Methods)
- How to Convert CSV to XLSX without Opening (5 Easy Methods)
- Excel VBA: Merge Multiple CSV Files into One Workbook