Data Import & Export

Import More Than 500,000 Rows of Text into Excel

Bushra ParveenBushra Parveen Nov 20, 2026 869 views

Question details

The user needs to transfer more than 500,000 rows of text into an Excel workbook.

How to Import More Than 500,000 Rows of Text into Excel
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.
Before you start

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.

Solution 1Recommended

Import the text file into an XLSX workbook

Use this method to import more than 500,000 records without manually copying and pasting them.

1
Save in the modern format

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.

2
Open the text importer

Select Data > From Text/CSV, or Data > From Text if that is the option available in your Excel version. Choose the source text file.

3
Check the import settings

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.

4
Load the data

Select Load or Finish to import the records into a worksheet, then check the last imported row and save the workbook.

Import the text file into an XLSX workbook
Worksheet capacity: An XLSX worksheet holds up to 1,048,576 rows total. If the first row contains headers, up to 1,048,575 rows remain for data.
Work with large text files

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. 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. 2. Set import options: In the text import dialog, choose the appropriate delimiter and encoding, and set identifier columns to Text if needed.
  3. 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. 4. Save as XLSX: Select File > Save As and choose the XLSX format to create an Excel-compatible workbook.
Import delimited text without manually pasting hundreds of thousands of rowsOpen and save Microsoft Excel XLSX workbooksUse separate worksheets for source files that exceed one sheet’s capacityWork with a familiar spreadsheet interface
microsoft office alternative - wps office

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.