CSV import: date format errors checklist

TL;DR

CSV date errors are almost never the importer's fault. They are ambiguity: 03/04/2026 means March 4 in the US and April 3 in Europe, and spreadsheets silently reformat dates on open. Standardize on ISO format (2026-03-04) before import, and the whole category of errors disappears. When dates import wrong, check the file's actual text, not what the spreadsheet shows.

The query

CSV import: date format errors checklist

Use this when

• CSV imports fail on date columns
• Dates import with month and day swapped
• Users report "invalid date" errors on clean-looking files
• You are writing CSV import documentation

Not for

• Non-date column errors
• File encoding or delimiter problems
• Building or debugging the importer code
• Timezone conversion logic

Steps

1. Look at the raw file, not the spreadsheet

Open the CSV in a plain text editor. Spreadsheets reformat dates on display, so the file may say 03/04/2026 while the screen shows something else. The raw text is the truth.

Expected output: the actual date strings the importer sees.

2. Identify the ambiguous formats

Flag every MM/DD/YYYY versus DD/MM/YYYY ambiguity. Any date where the day is 12 or less is unreadable without knowing the locale. Also flag two-digit years and missing leading zeros.

Expected output: a list of the ambiguous values in the file.

3. Convert to ISO format

Rewrite the date column as YYYY-MM-DD (2026-03-04). It is unambiguous in every locale and every importer accepts it. Do the conversion in the spreadsheet with a custom format, then re-export.

Expected output: a file with zero ambiguous dates.

4. Strip the spreadsheet artifacts

Check for extra spaces, dates stored as text in some rows and real dates in others, and header names that do not match what the importer expects. Clean all three before re-importing.

Expected output: a file the importer can parse without guessing.

5. Re-import a small sample first

Import five rows and verify the dates land correctly before running the full file. Catching a locale mismatch on five rows beats catching it on fifty thousand.

Expected output: confirmed correct dates, then the full import.

Template: the pre-import checklist

Before you import, [Name], run through this:

[ ] Dates are YYYY-MM-DD (2026-03-04)
[ ] No two-digit years anywhere
[ ] Opened the raw file in a text editor to confirm
[ ] Column headers match the template exactly
[ ] Test-imported 5 rows and checked the dates

Variant phrasings

csv dates importing wrong

Month-day swap is the top suspect. Steps 1 through 3.

invalid date format in csv import

Check the raw text first. The spreadsheet is lying to you about what is in the file.

csv import month and day swapped

Locale ambiguity. Convert to ISO format and re-import.

Why it happens

CSV has no types: every value is text, and the importer has to guess what each string means. Dates are the worst case because the same string is valid in two readings. Spreadsheets make it worse by reformatting on open and on save, so the file the user "checked" is not the file they uploaded. ISO format removes the guesswork entirely.

Edge cases

• Timestamps with timezones: 2026-03-04 10:00 means nothing without a zone. Include the offset or convert to UTC first.
• Very old dates: some spreadsheet date systems shift pre-1900 dates by a day. Rare, but real in historical data.
• Empty cells vs placeholder text: some importers treat them differently. Match your importer's expectation.
• Locale-specific month names: non-English month names will not parse in an English-locale importer. Use numbers.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_wG2oqs3lrNHfHOJvcEFY-Q