Fix Large AutoNumber Jumps After Importing Text Files into Microsoft Access
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.

- 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.
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.
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.
Right-click the text file, choose Open with, and select Notepad or another plain-text editor.
Scroll to the bottom of the file and check for rows that appear blank but contain only semicolons or other delimiters.
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.
Press Ctrl+S to save the cleaned text file, then run the Access import procedure again.

Delete Unused Rows in Excel Before Saving the Text File
Use this if the semicolon-delimited file is created or maintained in Excel and clearing cells is not enough.
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.

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.




