Importing a CSV without breaking dates and leading zeros

TL;DR:

• Double-clicking a CSV lets the spreadsheet guess each column's type. The guess turns 02134 into 2134 and can swap day and month.

• Use the app's import step, set ID-like columns to text, and tell it the date order or locale.

• Keep the original file untouched and check a few rows against it.

A CSV is just text: values separated by commas, one row per line. It carries no types, no formats and no memory of what a column meant. When a spreadsheet opens it, the spreadsheet decides what each value probably is, and that decision is where ZIP codes and dates get damaged.

Why the damage happens

A spreadsheet sees 02134 and thinks: that looks like a number. A number has no leading zeros, so it stores 2134. The zero is gone, and if you save the file the zero is gone from the file too.

Dates fail differently. The text 03/04/2026 means 3 April in one country and 4 March in another. The spreadsheet picks one using your computer's regional settings, called the locale. If the file came from someone with a different locale, every date where both numbers are 12 or less can be silently wrong. A date like 25/04/2026 cannot be a month 25, so it may stay as text while its neighbours convert, and you end up with a column that is half dates and half words.

Before you open the file

• Make a copy of the CSV and work on the copy. Never save over the original.

• Open it in a plain text editor first and read the first few lines. You will see exactly what is stored.

• Find out which date order the file uses, from whoever made it or from a date that cannot be ambiguous, such as a 25 in the day position.

• List the columns that are identifiers rather than quantities: ZIP codes, phone numbers, account numbers, product codes. These should be text.

Import instead of double-clicking

Both main spreadsheet apps have an import route that shows a preview and lets you choose a type per column. Look in the File or Data menu for an option named like Import, From Text, or From CSV, and check your app's help page for the current name in your version.

1. Start the import: Choose the import option and pick the copy of the file. Confirm the delimiter is a comma, or whatever the preview shows is separating the columns.

2. Set identifier columns to text: In the preview, select the ZIP code column and set its type to text. Do the same for phone numbers and codes. Text columns keep every character exactly as written.

3. Set the date column: Either set the type to date and choose the order the file uses (day first or month first), or leave it as text and convert it yourself afterwards. When unsure, text is the safer choice.

4. Check the locale setting: If the import dialog shows a language or region option, set it to match the file's source, not your own computer, when they differ.

5. Finish and compare: Load the data, then compare three rows against the text file by eye: the first, one from the middle, and the last.

Worked example: a CSV with ZIP codes and dates

Here is a three-line file from a regional office that writes dates day first.

id,zip,ordered
1,02134,03/04/2026
2,98101,25/04/2026
3,07030,12/05/2026

Opened by double-click on a computer set to month first, the zips become 2134, 98101 and 7030, and the dates go wrong in two ways. Row 1 becomes 4 March instead of 3 April. Row 2 stays as text, because there is no month 25. Row 3 becomes 5 December instead of 12 May.

Import it properly. Set zip to text, so 02134 and 07030 survive. Set ordered to text, load it, and then convert in a helper column with a formula that builds the date from its pieces. If the text is in C2, this builds it day first:

=DATE(VALUE(RIGHT(C2,4)), VALUE(MID(C2,4,2)), VALUE(LEFT(C2,2)))

Check it by substituting row 1: RIGHT gives 2026, MID gives 04, LEFT gives 03, so the result is year 2026, month 4, day 3, which is 3 April 2026. Row 3 gives 2026, 5, 12, which is 12 May. Both match what the file's author meant.

Tip: If your file can be changed at the source, ask for dates as 2026-04-03: year first, month, then day. Almost every app reads that form the same way, so no locale guess is needed.

If the zeros are already gone

You can restore a five-digit ZIP from a number with a text formula. If 2134 is in B2, this pads to five digits:

=TEXT(B2,"00000")

That returns 02134. This is only safe if you know every value should have exactly five digits. Do not use it on a column where lengths differ.

Mistakes to avoid

• Saving the spreadsheet back to CSV and overwriting the source. Save a new file name.

• Formatting the column as text after the import. By then the zero is already gone; the type must be set during import.

• Assuming the whole column parsed the same way. Sort the column and look at both ends.

• Trusting a date because it displays nicely. Test one with a day above 12.

Doing it faster

When the preview dialog confuses you, DuckyHelper can read it on your screen while the pill is open and explain each setting out loud. The checks against the original file are still yours to do.

Frequently asked questions

Why does my ZIP code lose its zero?

The spreadsheet reads it as a number, and numbers do not keep leading zeros. Import the column as text instead.

Is a CSV the same as an Excel file?

No. A CSV is plain text with no formatting or types, so every app has to guess unless you tell it.