How to Parse Dates Correctly During CSV Import

A CSV cell holding 05/01/2026 names 5 January or 1 May, and the file carries nothing that decides which.

05/01/2026   →   2026-01-05    read day first
05/01/2026   →   2026-05-01    read month first

Both are days that exist, and both pass an unbounded date check. One of them puts the record four months from where it belongs, and nothing downstream complains.

This is a veterinary practice's patient list, exported on its way to new software, sorted by last visit with the newest first.

1Patient ID,Patient Name,Species,Last Seen,Booster Due,Weight (kg)2BR-04100,Pippin,Cat,2026-08-14,2027-05-09,3.43BR-04107,Nutmeg,Dog,2026-08-11,2027-05-01,54.14BR-04114,Bramble,Cat,2026-08-06,2027-05-04,42.5⋮27 rows not shown32BR-04310,Juniper,Cat,2026-02-30,2026-12-06,41.4⋮11 rows not shown44BR-04394,Frankie,Dog,2026-01-17,2026-10-08,26.845BR-04401,Maud,Cat,12/01/2026,10/10/2026,18.846BR-04408,Barnaby,Dog,08.01.2026,02/10/2026,19.247BR-04415,Suki,Cat,05/01/2026,07/10/2026,23.248BR-04422,Alfie II,Dog,02/01/2026,,27.949BR-04429,Noor,Dog,11/12/2025,09/09/2026,31.350BR-04436,Pippin,Dog,09/12/2025,04/09/2026,8.551BR-04443,Nutmeg,Dog,28/11/2025,06/09/2026,42.3⋮3 rows not shown55BR-04471,Dexter,Cat,20251029,08/07/2026,36.7⋮3 rows not shown59BR-04499,Milo,Cat,11 Oct 2025,"Jul 5, 2026",40.3⋮3 rows not shown63BR-04527,Poppy II,Dog,20/09/2025,03/04/26,6.2⋮65 rows not shown129BR-04989,Bruno,Cat,25/10/2024,03/07/2025,20.6

Everything above line 45 was recorded by the system the practice bought at its last migration, which writes ISO. Everything below was carried across from the system before it, which writes the day first with slashes. Line 46 adds a third shape with dots. Line 59 writes the month as a word in both columns, line 63 drops the century, and line 32 names a day February has never had.

Where the day and the month go missing

A spreadsheet does not hold 05/01/2026. It holds a number counting days from its epoch, with a display format beside it, and the app renders one from the other. Microsoft names both inputs to that rendering. The display format "varies according to the date formats that have been applied to the cell, and the current settings under Regional Settings in Control Panel" on Windows.

Saving as CSV keeps the rendering and drops the number and the format together. The machine that produced the file knew the order. The file records the result and never the rule.

That is why a list of accepted formats settles nothing on its own. 05/01/2026 matches DD/MM/YYYY and MM/DD/YYYY equally well. Declaring both accepts the value twice and leaves the question open.

What the runtime gives you for free

new Date(value) is the shortest path from a cell to a day. It answers a different question than the one the file is asking. Measured in Node 24 on V8, with the timezone set per line.

new Date("05/01/2026")      Fri May 01 2026    read month first
new Date("2026-01-05")      Sun Jan 04 2026    TZ=America/New_York
new Date("2026-01-05")      Mon Jan 05 2026    TZ=UTC
Date.parse("2026-02-30")    1772409600000      2 March 2026
new Date("20251029")        Invalid Date

ECMAScript defines one interchange format for date-times, adapted from the ISO 8601 calendar date extended format. For a string that does not conform, the spec says "the function may fall back to any implementation-specific heuristics or implementation-specific date formats". Other formats are implementation-defined and may not work across all browsers, MDN says, and it names the unreliability of Date.parse as one of the motivations for the Temporal API.

Three of those lines matter for an import. V8 reads a slash date month first, whatever locale the machine holds. A date-only ISO string lands on midnight UTC, so getDate() in any zone behind UTC returns the day before. And a day February never has rolls forward into March, which turns a broken cell into a plausible one. TC39 keeps a table of parse cases where implementations disagree, and an out-of-bounds day like that one is on it.

An import needs a parser whose failures stay visible.

How a raw cell becomes a stored day

A column picks one shape and reads every cell with it. The shape comes out of a count.

                       the column's values
                                │
                   every shape scores the sample
                                │
              ┌─────────────────┼─────────────────┐
        one shape read      two shapes tied     no shape read
          the most              on top             anything
              │                     │                  │
        it reads the          the machine       the column keeps
          column             picks one and         its text
                                 warns

Then each cell goes through the winner.

                       a cell in that column
                                │
                 ┌──────────────┴──────────────┐
            already ISO              the column's shape
                 │                 ┌───────────┴──────────┐
            store as is        reads it             reads nothing
                                   │                      │
                              store ISO             keep the text,
                                                     flag the cell

Updog Importer carries twenty-two shapes, and every one of them scores the column on its own.

YYYY-MM-DD      2026-08-14      and 2026-8-4
YYYY/MM/DD      2026/03/05
YYYY.MM.DD      2026.03.05
YYYYMMDD        20260305
DD/MM/YYYY      12/01/2026      against MM/DD/YYYY
DD.MM.YYYY      08.01.2026      against MM.DD.YYYY
DD-MM-YYYY      08-01-2026      against MM-DD-YYYY
DD/MM/YY        12/01/26        against MM/DD/YY
DD.MM.YY        08.01.26        against MM.DD.YY
DD-MM-YY        08-01-26        against MM-DD-YY
D MMM YYYY      11 Oct 2025
MMM D, YYYY     Jul 5, 2026
DD-MMM-YYYY     11-Oct-2025
MMM-DD-YYYY     Oct-11-2025
YYYY年M月D日    2026年10月11日
serial          46143

A time on the end of a value is cut before any shape sees it, so 2026-01-05T14:30:00Z and 2026-01-05 14:30 both reach the ISO shape and land on 5 January. An era after a Russian date and a weekday in front of an English one go the same way. The day survives and the time is gone.

A two-digit year widens on the POSIX window, 00 through 68 into the 2000s and 69 through 99 into the 1900s.

The serial number is the one shape that has to explain every value in the sample before it counts at all. A column of five-digit integers holds IDs as readily as days.

Month names come from the language the importer runs in, full and short, and English rides underneath whatever that language is. A German importer reads 11 Okt 2025 and 11 Oct 2025 alike. A Finnish one reads 11 lokakuuta 2025, where the month carries an ending it never has standing alone. Set ui.calendar.months to replace any of them.

A value with a part above 12 crosses a shape out by itself. 28/11/2025 hands 28 to the month under MM/DD/YYYY, which names no month, so that shape reads nothing there and its score stops climbing. Every shape runs over the same values, and the one that read the most of them takes the column.

const DAYS = [31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31];

const exists = (y: number, m: number, d: number): boolean => {
  if (m < 1 || m > 12 || d < 1) return false;
  const leap = (y % 4 === 0 && y % 100 !== 0) || y % 400 === 0;
  return d <= (m === 2 && leap ? 29 : DAYS[m - 1]);
};

const iso = (y: number, m: number, d: number): string | null => {
  if (!exists(y, m, d)) return null;
  const mm = String(m).padStart(2, "0");
  const dd = String(d).padStart(2, "0");
  return y + "-" + mm + "-" + dd;
};

export type Candidate = { id: string; read: (value: string) => string | null };

type Slot = "y" | "m" | "d";

// One shape, one regular expression, and the slot each captured group fills.
const shape = (
  id: string,
  re: RegExp,
  order: readonly [Slot, Slot, Slot],
): Candidate => ({
  id,
  read: (value) => {
    const match = re.exec(value.trim());
    if (!match) return null;
    const slot: Record<string, number> = {};
    for (let i = 0; i < 3; i++) slot[order[i]] = Number(match[i + 1]);
    return iso(slot.y, slot.m, slot.d);
  },
});

const SLASH = /^(\d{1,2})\/(\d{1,2})\/(\d{4})$/;

export const shapes: Candidate[] = [
  shape("YYYY-MM-DD", /^(\d{4})-(\d{1,2})-(\d{1,2})$/, ["y", "m", "d"]),
  shape("DD/MM/YYYY", SLASH, ["d", "m", "y"]),
  shape("MM/DD/YYYY", SLASH, ["m", "d", "y"]),
];

// The shape that read the most values takes the column. A tie goes to the
// first in the list, which the caller orders by the machine's own locale.
export const detect = (
  values: string[],
  list: Candidate[],
): Candidate | null => {
  let winner: Candidate | null = null;
  let best = 0;
  for (const candidate of list) {
    let score = 0;
    for (const value of values) {
      if (candidate.read(value) !== null) score++;
    }
    if (score > best) {
      best = score;
      winner = candidate;
    }
  }
  return winner;
};

Those lines carry three of the twenty-two shapes and the loop that scores them. The rest are more regular expressions, a table of month names, and the same loop.

What a column can settle on its own

Reading a column before converting any of it is what makes the count possible. Updog Importer takes the first 1000 rows of a file, scores every shape against the values it finds in each column, and keeps the winner for that column of that file.

The patient list has two date columns, and they come out differently.

Last Seen                        Booster Due
DD/MM/YYYY     82                MM/DD/YYYY     80
YYYY-MM-DD     42                DD/MM/YYYY     80
MM/DD/YYYY     34                YYYY-MM-DD     43
DD.MM.YYYY      1                MMM D, YYYY     1
MM.DD.YYYY      1                DD/MM/YY        1
D MMM YYYY      1                MM/DD/YY        1
YYYYMMDD        1

Last Seen holds 82 slash values, and 48 of them put a number above 12 first, which no month can take. MM/DD/YYYY reads nothing in those 48, and the count ends 82 to 34. The 42 readable ISO values score for a third shape and leave the pair where it stands.

Booster Due ties. The practice books its booster clinics in the first twelve days of a month, so no value across its 128 rows holds a part above 12, and both readings explain 80 values each. A column like that cannot answer the question about itself, however many rows it holds, and the column beside it never speaks for it.

A tie falls to the machine. The shapes are listed with the reader's own order first, taken from Intl.DateTimeFormat asked to print a day and a month.

en-US    month 12 / day 31      month first
en-GB    day 31 / month 12      day first
de-DE    day 31 . month 12 .    day first
ja-JP    month 12 / day 31      month first

The same column then imports one way on a laptop in Chicago and another way on a laptop in Manchester. That is the floor of any detector. The machine that ran this import reads en-US, so MM/DD/YYYY took Booster Due. The tie also reaches your onError, which is the one place it is reported.

[updog] column "Booster Due" could be read as MM/DD/YYYY or DD/MM/YYYY;
applied MM/DD/YYYY. Samples: 2027-05-09, 2027-05-01, 2027-05-04,
2027-05-09, 2027-04-11

The winner then converts the values it reads, and the rest keep their text.

Last Seen      line 45    12/01/2026     →   2026-01-12
Last Seen      line 51    28/11/2025     →   2025-11-28
Last Seen      line 46    08.01.2026     →   08.01.2026
Last Seen      line 59    11 Oct 2025    →   11 Oct 2025
Booster Due    line 46    02/10/2026     →   2026-02-10

cells that would hold a different day under the other reading
Last Seen                                               32
Booster Due                                             75

The same punctuation reads two ways in one file. 12/01/2026 in Last Seen becomes 12 January, and 32 cells of that column turn on a count of 82 against 34. 02/10/2026 in Booster Due becomes 10 February, and 75 cells of that one turn on the machine that opened the file. A staff roster's start dates go through the same count, column by column.

Which values keep their text

Six cells of 256 come out of the file unconverted. One of them names a day that exists in no year. The other five match a shape that lost its column.

A file that writes 20251029 in every row imports as days. One 20251029 among 82 slash values keeps its text.

Excel reads 00 through 29 as 2000 to 2029, and 30 through 99 as 1930 to 1999, and Microsoft states that the cutoff a person can change in Control Panel applies to typed dates only. Both windows carry 03/04/26 to 2026. They part company at 30, which Excel reads as 1930 and Updog Importer as 2030.

What the person sees

A value that failed to convert stays exactly as the file wrote it, and the column's date rule reports it.

line 32    Last seen      "2026-02-30"     Invalid date
line 46    Last seen      "08.01.2026"     Invalid date
line 55    Last seen      "20251029"       Invalid date
line 59    Last seen      "11 Oct 2025"    Invalid date
line 59    Booster due    "Jul 5, 2026"    Invalid date
line 63    Booster due    "03/04/26"       Invalid date

The text survives. The cell shows the person the string they have to fix, and the grid falls back to that text for anything that is not a valid ISO date.

A converted value is stored as YYYY-MM-DD and displayed through the viewer's own locale. The same stored day reads as 12/01/2026 in London and 1/12/2026 in Chicago. That formatter is pinned to Latin digits and the Gregorian calendar, so ar-EG prints 19/8/2026 in Latin digits, fa-IR prints the Gregorian day, and th-TH prints the year 2026. Typing a date back into the grid depends on all three, and so does the search box, which reads what the screen shows.

Editing a cell opens a date picker, and the min and max of a { type: "date" } validator set the range it offers as well as the bounds the check enforces. Those bounds compare ISO strings, so they only ever see values that converted.

A converted value leaves through onComplete and through an export as ISO, whatever shape it arrived in. A value that never converted leaves as the text the file wrote, which is one more reason to fix the six flagged cells before the rows reach a database.

Where the shape comes from

Data reaches the grid three ways, and each one counts its own shapes.

The winner is kept on the pair of the source and the column. A later chunk of the same source reuses it, and a second file counts for itself. Two files dropped in one upload get their own shape in every column, and neither borrows anything from the other. Neither borrows from the punctuation of the number columns either. A language can put the day first and still write 1,234.56, which is what British English does.

What this does not solve

One shape covers one column. A column that mixes shapes converts the values its winner reads and keeps the rest as text, which is what happens to 08.01.2026 and 11 Oct 2025 here.

A column whose days never pass 12 ties, and the machine that opened the file breaks the tie. Nothing inside Booster Due could have settled it, and no number of extra rows would have helped.

Rows that disagree with each other inside one column, half day first and half month first, get read one way. The rows on the other side of that split become wrong days that pass validation.

A whole date is the unit. A cell holding 2026-01-05 14:30 arrives as 5 January 2026 with the 14:30 dropped, and an offset or a timezone on the end goes with it.

As of August 2026 the importer never shows the winning shape on screen and never asks anyone to confirm it. A person matching columns sees the file's own values, and the converted ones appear later, in the grid. A tied column reaches your onError and nobody else. The strongest guard against a wrong reading is still a schema that asks the systems you control for ISO.

What you write

Two declarations turn a column into a date column. The editor puts every value through the parser, and the rule that comes with it makes the failures visible.

import type { DataEditorColumn } from "@updog/data-editor";

export const columns: DataEditorColumn[] = [
  {
    id: "patientRef",
    title: "Patient ID",
    validators: [{ type: "required" }, { type: "unique" }],
  },
  { id: "name", title: "Patient name", validators: [{ type: "required" }] },
  { id: "species", title: "Species" },
  {
    id: "lastVisit",
    title: "Last seen",
    editor: { type: "date" },
    validators: [{ type: "date", max: "2026-08-24" }],
  },
  {
    id: "boosterDue",
    title: "Booster due",
    editor: { type: "date" },
  },
  {
    id: "weightKg",
    title: "Weight (kg)",
    editor: { type: "number" },
    validators: [{ type: "number", min: 0, max: 90, decimalPlaces: 1 }],
  },
];

editor: { type: "date" } is what triggers conversion. A column carrying only a { type: "date" } validator is checked and never converted, so its slash values fail the check as written. A date editor with no validator of its own gets one anyway, so Booster due reports its two cells without declaring a rule. Last seen declares one, and that rule reports its four. Which of the file's headers becomes that column is settled one step earlier, in column matching.

The six flagged cells in this file are the safe ones. They stop, they name themselves, and somebody fixes them in the grid before the import lands. The hundred and seven that would hold a different day under the other reading are why a date column earns this much attention, and why the work starts with counting a column before touching a single value in it.