filemixer

FileMixer / Guides / Leading zeros

Open a CSV in Excel without losing leading zeros

Double-click a CSV and Excel guesses what each value is. The guesses lose data, and saving the file again makes the loss permanent.

What goes wrong

In the CSVExcel showsWhy
021342134It reads a ZIP code as a number and drops the zero.
41111111111111114.11111E+15Excel keeps 15 significant digits. The 16th becomes 0, for good.
3-55-MarIt looks like a date, so it becomes one.
JoséJoséThe file is UTF-8 but Excel opened it with an older Windows encoding.

If you then press Save, the CSV is rewritten with Excel's version. The zeros and digits are gone from the file itself.

Fix 1: convert the CSV to .xlsx first

The quickest way. The CSV to Excel converter reads the file as text, so nothing is guessed. Values with leading zeros and numbers longer than 15 digits stay as text. Plain numbers become real numbers you can sum. Accents come through because the file's encoding is detected. Open the .xlsx and everything is as it was in the CSV.

Convert CSV to Excel

Fix 2: import instead of opening

In Excel for Microsoft 365, Windows or Mac:

  1. Start with a blank workbook. Go to Data › From Text/CSV and pick the file.
  2. Check File Origin says 65001: Unicode (UTF-8). If the preview shows “José”, change it.
  3. Click Transform Data. Select the ZIP, ID or phone columns, then Data Type › Text. Choose Replace current if asked.
  4. Click Close & Load.

Fix 3: turn off the conversions

Recent versions of Excel for Microsoft 365 have File › Options › Data › Automatic data conversion (on a Mac, Excel › Preferences › Edit). Uncheck “Remove leading zeros”, “Convert digits to scientific notation” and the date option. It applies to every CSV you open after that. Older versions don't have the setting.

When you make CSVs for others

If you send CSVs that people will open in Excel, add a UTF-8 marker (a byte order mark) at the start of the file. Excel then reads accents correctly on a double-click. FileMixer adds it by default to every CSV it saves; uncheck Add a UTF-8 marker for systems that reject it.

For the leading zeros, the safest thing to send is an .xlsx with those columns stored as text. That's what FileMixer's converter produces.