Fix NetSuite JE Import External ID Date Issue
Excel rewrites external IDs like 10-10 into dates before NetSuite ever sees the CSV. Here is how to stop it and repair the entries it broke.
Sarah Jenkins, CPAPrincipal Finance Automation Specialist
On this page
When your journal entry external IDs look like 10-10 and NetSuite turns them into 10/10/2026, the problem isn't in NetSuite. It's Excel rewriting your CSV before the file ever reaches the Import Assistant. The record lands in the wrong posting period, or the import fails with an invalid reference error.
Why Excel Keeps Converting Your Text
The externalid field on the Journal Entry record is plain text. It has no date validation. NetSuite will store 10-10 as a department code or cost-center reference without complaint. The conversion happens entirely on your desktop.
When you save a CSV and reopen it in Excel, the program auto-detects values that look like dates. A cell containing 10-10 matches the MM-DD pattern, so Excel converts it to 10/10/2026 and reformats it. Here's the trap: even if you set the column to Text before saving, the conversion happens again the moment you reopen the file. Your formatting is gone, and the damage is baked into the saved file.
This is why the apostrophe trick from the old spreadsheet days doesn't survive a CSV round-trip. Typing '10-10 forces the cell to text in the original workbook, but when Excel re-parses the raw CSV on open, the leading apostrophe is stripped and the value is re-interpreted as a date again.
The Workaround That Survives a CSV Save
NetSuite's own CSV import documentation gives the reliable solution: wrap the value in an equals sign and double quotes.
| Approach | What Excel Does |
|---|---|
Type 10-10 | Converts to 10/10/2026 |
| Format column as Text | Reverts on reopen |
Prefix with apostrophe '10-10 | Strips apostrophe on reopen |
Use ="10-10" | Keeps literal text, survives reopen |
In the cell, enter:
="10-10"Excel treats this as a formula that evaluates to the text string 10-10. When you save the CSV, the underlying value is written as 10-10 without the formula wrapper. The official guidance confirms this approach stops Excel from changing text to dates.
The discipline matters more than the cell entry. Do not open the CSV after saving it. Generate the file, save it, and go straight to Transactions > Management > Import CSV Records. The moment you open the file to "check" it, you reintroduce the date conversion.
When Your External ID Is Generated by a Formula
If your external ID column is produced by a formula, like =A2&"-"&B2, save the results as static values before exporting. A formula-driven cell writes the formula string to the CSV, not the evaluated result. That can produce odd artifacts, including values that Excel re-interprets on import.
Copy the column and paste it back as Paste Values (right-click > Paste Special > Values) so the file contains literal text. Then apply the same no-reopen rule.
The Duplicate External ID Error
Once you get past the date conversion, a second error can surface. NetSuite requires the external ID to be unique within a record type. The SOAP reference spells it out: "This record already exists" means you attempted to add a journal entry using an external ID already in use.
If your department code is 10-10, and you've imported entries with that external ID before, every new import with the same value fails. The external ID is a reference key, not a department label you can reuse per posting period. Give each journal entry a unique external ID, such as 10-10-2026-001, or append the entry number.
One more thing to check: if you use the single-file import format, the external ID must appear in every line of the CSV, not just the first row of each record. The import format documentation confirms this requirement. Missing it triggers the mapping error: "You must map one of the following NetSuite fields to a column in your CSV file, to be a unique identifier: External ID, Internal ID, or a transaction number field."
A Clean Import Workflow
Build a repeatable process so the date conversion never catches you off guard:
- Generate the CSV from your source system or a template.
- In the external ID column, enter values as
="10-10"or paste static text. - Save the file as CSV.
- Do not reopen it. Go straight to Transactions > Management > Import CSV Records.
- On the Field Mapping page, map the External ID column to the External ID field and choose Add as the import behavior.
- Confirm each external ID is unique before you run the import.
From an audit perspective, a consistent external ID convention gives you a clean reference key to trace each imported entry back to its source file. That traceability matters at year-end when your auditors ask where a specific journal entry came from.
The safest habit is to avoid dash-separated numeric codes entirely. Use a prefix like DEPT-10-10 or 10_10 instead, so Excel never mistakes the value for a date in the first place. If you're already mid-import and hitting these errors, fix the external ID column first, then re-run. The time spent correcting the source file beats the time spent chasing down misposted entries in the general ledger.
