Others

Fix Large AutoNumber Jumps After Importing Text Files into Microsoft Access

Elise WilliamsElise Williams Nov 13, 2026 868 views

Question details

Users need to stop Microsoft Access from assigning unusually large AutoNumber values after importing semicolon-delimited text files when empty delimiter-only rows are present.

Fix Large AutoNumber Jumps After Importing Text Files into Microsoft Access
Product
Microsoft Access
Device & OS
not provided
Scenario
Importing a semicolon-delimited text file into Microsoft Access.
Observed behavior
After import, the AutoNumber field jumps to a very large value such as 240,939,769 instead of continuing sequentially.
Before you start

Open the source text file in a plain-text editor such as Notepad and scroll carefully to the end to inspect for empty-looking rows. If the file was created in Excel, review the final rows because blank-looking rows may still contain semicolons or other delimiters.

Solution 1Recommended

Remove Empty Rows at the End in a Plain-Text Editor

Use this when you can edit the source file directly and want the most reliable fix before importing into Access.

1
Open the source file in Notepad

Right-click the text file, choose Open with, and select Notepad or another plain-text editor.

2
Scroll to the end and inspect empty rows

Scroll to the bottom of the file and check for rows that appear blank but contain only semicolons or other delimiters.

3
Delete the empty delimiter-only rows

Place the cursor at the start of each empty row, press Shift+End to select the line, and press Delete. Remove all trailing rows that contain only semicolons.

4
Save and reimport the file

Press Ctrl+S to save the cleaned text file, then run the Access import procedure again.

Remove Empty Rows at the End in a Plain-Text Editor
Import fixed: After removing trailing delimiter-only rows, Access should continue AutoNumber values sequentially after import.
Free Microsoft Office alternative

Use WPS Office as a Free Microsoft Office Alternative for Document and Spreadsheet Tasks

WPS Office is a free, lightweight alternative to Microsoft Office with a familiar ribbon interface and high compatibility for .docx, .xlsx, and .pptx files. It does not replace Microsoft Access, but WPS Spreadsheet can help clean and prepare semicolon-delimited source files before import, making migration less disruptive.

Clean and prepare semicolon-delimited source files in WPS Spreadsheet before importing them into Access.Open and save .xlsx, .docx, and .pptx files with Microsoft Office format compatibility.Familiar ribbon-style interface reduces the learning curve when moving from Microsoft Office.Free, lightweight suite that runs smoothly on Windows, macOS, Android, and iOS.
microsoft office alternative - wps office

Frequently Asked Questions

Why does Access AutoNumber jump to a very large number after importing a text file?

Trailing empty rows that contain only semicolons or other delimiters may be interpreted as records, causing Access to consume many AutoNumber values during import.

How do I see empty rows in a delimited text file?

Open the file in Notepad or another plain-text editor, scroll to the bottom, and look for rows that appear blank but contain only semicolons or other delimiters.

Can I fix this in Excel by clearing the blank cells?

No. Clearing cell contents is not enough because the rows still exist. Select the unused rows, right-click the row headers, choose Delete, and save the file again.

Does WPS Office include Microsoft Access?

No. WPS Office includes Writer, Spreadsheet, Presentation, and PDF tools, but it does not include an Access database module. It can still help edit source text or spreadsheet files.

How do I reimport the cleaned text file into Access?

In Microsoft Access, go to External Data > Text File, select the cleaned file, and follow the import wizard or use your saved import specification.