Import More Than 500,000 Rows of Text into Excel
Question details
The user needs to transfer more than 500,000 rows of text into an Excel workbook.

- Product
- Microsoft Excel 2016
- Device & OS
- not provided
- Scenario
- Importing a large text file while accounting for worksheet and file-format row limits.
- Observed behavior
- An XLS worksheet is limited to 65,536 rows, while an XLSX worksheet can hold up to 1,048,576 rows.
Check whether the workbook is an XLS file or shows Compatibility Mode in its title bar, and identify the delimiter used in the text file. Count the data records and allow a row for headers when planning the import.
Import the text file into an XLSX workbook
Use this method to import more than 500,000 records without manually copying and pasting them.
In Excel, select File > Save As and choose Excel Workbook (*.xlsx). This avoids the 65,536-row limit of the Excel 97–2003 XLS format.
Select Data > From Text/CSV, or Data > From Text if that is the option available in your Excel version. Choose the source text file.
In the import preview or wizard, select the correct delimiter and encoding. Set columns such as long IDs to Text when needed to preserve their values.
Select Load or Finish to import the records into a worksheet, then check the last imported row and save the workbook.

Split a text file that exceeds one worksheet’s limit
Use separate files and worksheets when the source contains more records than one XLSX worksheet can hold.
Import text data with WPS Spreadsheet
Open a text file in WPS Spreadsheet, choose its import settings, and save the resulting worksheet as an XLSX workbook. Split the source first if it exceeds the worksheet limit.
- 1. Open the text file: In WPS Spreadsheet, select File > Open and choose the text or CSV file. Change the file-type filter if the file is not visible.
- 2. Set import options: In the text import dialog, choose the appropriate delimiter and encoding, and set identifier columns to Text if needed.
- 3. Review the worksheet: Complete the import, then check the first and last rows. If the file exceeds the worksheet’s row limit, split it into smaller files and open each part separately.
- 4. Save as XLSX: Select File > Save As and choose the XLSX format to create an Excel-compatible workbook.

Frequently Asked Questions
Can Excel hold 500,000 rows on one worksheet?
Yes. An XLSX worksheet supports up to 1,048,576 rows, including any header row. An XLS worksheet is limited to 65,536 rows.
Why does my workbook stop at 65,536 rows?
It may be an Excel 97–2003 XLS workbook or open in Compatibility Mode. Select File > Save As and choose Excel Workbook (*.xlsx) to use the modern worksheet limit.
Can I import two million rows onto one Excel sheet?
No. One XLSX worksheet holds at most 1,048,576 rows. Split the source into smaller files and import them onto separate worksheets.
How do I keep leading zeros in imported IDs?
In the text import wizard or preview, set the ID column’s data type to Text before loading the data.




