How to Import CSV Files with More Than 65536 Rows in Excel
In Excel versions prior to 2007, the maximum number of rows per worksheet is 65536, and even in newer versions, this limit is expanded to 1048576 rows. So what do you do when you need to work with a CSV file that exceeds these row limits?
Fortunately, there are several solutions available to tackle this issue, depending on your needs and preferences. In this article, we will explore these solutions and guide you through the process of importing large CSV files into Excel.
Using MS Access
One effective way to work with large CSV files in Excel is to use Microsoft Access. MS Access can handle CSV files with millions of records, and you can easily connect your Excel workbook to an Access database to query or analyze the data. Here’s how you can do it:
Change the file extension
To begin, you should change the file extension of your CSV file to .txt. This is essential because MS Access will require this format to guide you through the import wizard.
Import data into MS Access
Follow these steps:
- Open MS Access and create a new blank database.
- Go to the External Data tab and click on “Text File” in the Import & Link group.
- Browse for your .txt file and select “Import the source data into a new table in the current database”. Click OK.
- In the Import Text Wizard, choose “Delimited” as the file format and click Next.
- Choose the delimiter that separates your data (comma, tab, etc.) and check if the first row contains field names. Click Next.
- Assign data types and field names for each column. Click Next.
- Choose a primary key for your table or let Access add one for you. Click Next.
- Give a name to your table and click Finish.
Connect Excel to MS Access
- After importing your data into MS Access, you can connect Excel to Access and work with your data:
- Open Excel and go to the Data tab. Click on “From Access” in the Get & Transform Data group.
- Browse for your Access database and select the table you just imported. Click OK.
- In the Import Data dialog box, choose how you want to view the data in Excel (Table, PivotTable Report, etc.) and specify where you want to place it. Click OK.
Using Power Query
Power Query is a powerful feature in Excel that allows you to connect to various data sources, including CSV files with more than 65536 rows. To use Power Query, you need to have Excel 2010 or a later version and install the Power Query add-in from Microsoft’s website. Here’s how you can do it:
Install Power Query add-in
If you haven’t already, download and install the Power Query add-in from Microsoft’s official website.
Import CSV file using Power Query
Follow these steps:
- Open Excel and navigate to the Power Query tab. Click on “From File” and then select “From CSV”.
- Browse for your CSV file and click “Import”.
- In the Query Editor window, you can apply various transformations or filters to your data as needed. Click “Close & Load” when you are done.
Choose how to view your data
In the Import Data dialog box, you can choose how you want to view the data in Excel (Table, PivotTable Report, etc.) and specify where you want to place it. Click OK.
Using Delimit
For those who prefer a third-party solution, Delimit is an excellent choice. Delimit is specialized software designed to open CSV files with up to 2 billion rows and 2 million columns quickly. They offer a free 15-day trial version that you can download from their website. With Delimit, you can efficiently view, edit, filter, sort, or export your CSV data as needed.
These are some of the primary ways to import CSV files with more than 65536 rows in Excel. The choice of method depends on your specific requirements and familiarity with the tools. However, it’s important to note that while these methods enable you to work with larger datasets in Excel, the software is not optimized for handling very large datasets. You may encounter performance issues or limitations if you attempt to process a massive amount of data at once.
For more advanced data analysis, visualization, or management of large datasets, it’s advisable to consider using dedicated tools such as R, Python, Tableau, Power BI, or other data analytics and visualization platforms. These tools are designed to handle extensive data and provide more advanced functionalities, making them ideal choices for complex data tasks.
