{"id":678,"date":"2026-09-23T03:27:36","date_gmt":"2026-09-22T18:27:36","guid":{"rendered":"https:\/\/jim-jin.com\/?p=678"},"modified":"2026-10-08T09:10:02","modified_gmt":"2026-10-08T00:10:02","slug":"csv%e3%81%ae%e5%85%88%e9%a0%ad%e3%81%ae0%e3%82%92%e6%b6%88%e3%81%95%e3%81%9a%e3%81%ab%e7%b7%a8%e9%9b%86%e3%81%99%e3%82%8b%e6%96%b9%e6%b3%95%ef%bd%9c%e9%83%b5%e4%be%bf%e7%95%aa%e5%8f%b7%e3%83%bb","status":"publish","type":"post","link":"https:\/\/jim-jin.com\/en\/posts\/678","title":{"rendered":"Keep Leading Zeros in CSV Files | Postal Codes and Product Codes"},"content":{"rendered":"\n

Have you opened a CSV in Excel and seen product code 00123 become 123, or postal and phone numbers lose their leading zeros?<\/p>\n\n\n\n

This happens when digit sequences are treated as numbers. For product and postal codes, leading zeros are important information.<\/p>\n\n\n\n

To keep leading zeros in CSV data, treat the relevant values as text from the moment they are read.<\/strong><\/p>\n\n\n\n

This article covers editing with a CSV editor or Excel, checking saved files and dealing with zeros already lost.<\/p>\n\n\n\n

Why do leading zeros disappear from CSV files?<\/h2>\n\n\n\n
\"Keep<\/figure>\n\n\n\n

CSV stores table values as text. Applications opening it may infer numbers or dates from those values.<\/p>\n\n\n\n

For example, 00123 read as a number is numerically the same as 123, so leading zeros are removed.<\/p>\n\n\n\n

Data type<\/th>Example value to preserve<\/th>When treated as a number<\/th><\/tr><\/thead>
Product code<\/td>00123<\/td>123<\/td><\/tr>
Customer number<\/td>000045<\/td>45<\/td><\/tr>
Postal code without a hyphen<\/td>0600001<\/td>600001<\/td><\/tr>
Phone number without hyphens<\/td>0312345678<\/td>312345678<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

The table contains illustrative examples.<\/p>\n\n\n\n

Excel may remove leading zeros through automatic numeric conversion. Microsoft recommends treating such data as text to preserve it.Microsoft Support: Keeping leading zeros and large numbers<\/a><\/p>\n\n\n\n

Distinguish quantities from identifiers<\/h3>\n\n\n\n

Quantities and amounts are numbers used in calculations. Postal and product codes identify destinations or products.<\/p>\n\n\n\n

Some systems treat 00123 and 123 as different products. Even if an identifier contains only digits, generally treat it as text when it is not used for calculations.<\/p>\n\n\n\n

CSV does not store Excel cell formatting<\/h3>\n\n\n\n

Excel workbooks can store cell formats and data types, but CSV cannot store Excel instructions telling readers to open a column as text.<\/p>\n\n\n\n

Even if a CSV correctly contains 00123, another person opening it directly in Excel may see 123.<\/p>\n\n\n\n

You need both to preserve zeros during editing and to open the saved file without losing them.<\/strong><\/p>\n\n\n\n

CSV Editor (Free)<\/span><\/a><\/div>\n\n\n\n

Before editing, check whether the original CSV retains zeros<\/h2>\n\n\n\n
\"Check<\/figure>\n\n\n\n

First keep a copy of the unedited CSV. Use a working copy for checks and edits.<\/p>\n\n\n\n

Even if Excel displays values without zeros, the original may retain them if it has not been overwritten.<\/p>\n\n\n\n

Open the original in a CSV editor or text editor and check the values.<\/p>\n\n\n\n

Original CSV contents<\/th>Action<\/th><\/tr><\/thead>
00123 is still present<\/td>Read it again as text<\/td><\/tr>
It already contains 123<\/td>Check an unedited copy or the export source<\/td><\/tr>
The correct number of digits is unknown<\/td>Compare with the product master or management rules<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Even when zeros are missing, check when values changed before adding zeros manually.<\/p>\n\n\n\n

CSV Editor (Free)<\/span><\/a><\/div>\n\n\n\n

Method 1: Edit values as text in a CSV editor<\/h2>\n\n\n\n
\"Preserve<\/figure>\n\n\n\n

For changing part of a CSV, an editor that treats values as text is an option.<\/p>\n\n\n\n

JIM-JIN’s CSV Editor is designed to retain cell values as text. Edit leading zeros, long numbers and date-like values without automatically converting them to numbers or dates.<\/p>\n\n\n\n

Basic editing workflow<\/h3>\n\n\n\n
    \n
  1. Keep the unedited original and prepare a working CSV.<\/li>\n\n\n\n
  2. Load the file in the CSV Editor.<\/li>\n\n\n\n
  3. Check Japanese text, column boundaries and leading zeros.<\/li>\n\n\n\n
  4. Edit the necessary cells, rows or columns.<\/li>\n\n\n\n
  5. Check save settings for the destination and download the edited CSV.<\/li>\n\n\n\n
  6. Read the saved CSV again and verify that relevant values have not changed.<\/li>\n<\/ol>\n\n\n\n

    For example, to change only a product name for code 00123, leave the code intact and edit the product name cell.<\/p>\n\n\n\n

    Check before pasting from Excel too<\/h3>\n\n\n\n

    An editor retaining text cannot restore zeros already lost at the copy source.<\/p>\n\n\n\n

    If you copy after Excel has changed 00123 to 123, the pasted value may also be 123.<\/p>\n\n\n\n

    Load the original CSV directly or verify that the source retains zeros before pasting.<\/strong><\/p>\n\n\n\n

    CSV Editor (Free)<\/span><\/a><\/div>\n\n\n\n

    Method 2: Import relevant columns into Excel as text<\/h2>\n\n\n\n

    When editing in Excel, do not double-click the CSV directly. Specify relevant columns as text during import.<\/p>\n\n\n\n

    Here is a typical From Text\/CSV workflow in Excel for Windows. Screens and menu names vary by version.<\/p>\n\n\n\n

    Specify column types before loading data<\/h3>\n\n\n\n
      \n
    1. Open a blank workbook in Excel.<\/li>\n\n\n\n
    2. On the Data tab, choose From Text\/CSV.<\/li>\n\n\n\n
    3. Select the unedited CSV.<\/li>\n\n\n\n
    4. Check encoding and delimiters, then choose Transform Data.<\/li>\n\n\n\n
    5. In Power Query, select postal or product code columns.<\/li>\n\n\n\n
    6. Change the data type to Text.<\/li>\n\n\n\n
    7. If prompted, choose Replace Current or the equivalent option to replace the existing type conversion.<\/li>\n\n\n\n
    8. Check that zeros remain, then choose Close & Load.<\/li>\n<\/ol>\n\n\n\n

      Adding a text conversion after an automatic numeric conversion may not restore zeros already lost.Configure the original data to be read as text.<\/strong> That is the key.<\/p>\n\n\n\n

      Microsoft also describes changing Power Query columns to text and replacing the current type conversion.Microsoft Support: Treating imported data as text<\/a><\/p>\n\n\n\n

      Format new cells as text before entering values<\/h3>\n\n\n\n

      When adding new product codes to imported data, format the destination cells as text in advance.<\/p>\n\n\n\n

        \n
      1. Select the destination cells or columns.<\/li>\n\n\n\n
      2. Open Format Cells.<\/li>\n\n\n\n
      3. Choose Text as the format.<\/li>\n\n\n\n
      4. Then enter or paste product codes and other identifiers.<\/li>\n<\/ol>\n\n\n\n

        Changing to text after entering 00123 and getting 123 does not automatically restore the zero. Set the format before entering data.Microsoft Support: Formatting numbers as text<\/a><\/p>\n\n\n\n

        Check automatic conversion settings in supported Excel versions<\/h3>\n\n\n\n

        Supported Microsoft 365 and Excel 2024 versions provide controls for automatic data conversion.<\/p>\n\n\n\n

        Disabling Remove leading zeros and convert to a number can retain digit sequences with leading zeros as text. Behavior varies by version and import method, so verify it with your actual CSV afterward.Microsoft Support: Data import and analysis options<\/a><\/p>\n\n\n\n

        CSV Editor (Free)<\/span><\/a><\/div>\n\n\n\n

        Displaying zeros differs from saving text containing zeros<\/h2>\n\n\n\n

        In Excel, applying the custom number format 00000 to numeric 123 can display 00123.<\/p>\n\n\n\n

        The underlying cell still contains numeric 123; the display format pads it with zeros.<\/p>\n\n\n\n

        Method<\/th>Cell value<\/th>Screen display<\/th><\/tr><\/thead>
        Enter 00123 as text<\/td>Text 00123<\/td>00123<\/td><\/tr>
        Apply format 00000 to numeric 123<\/td>Number 123<\/td>00123<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

        Display formats are useful for presenting Excel tables, but CSV output and the destination’s interpretation may differ.<\/p>\n\n\n\n

        Depending on export behavior, displayed zeros may be included in CSV output. However, CSV does not retain the original cell format, so reopening may convert the value to a number again.<\/p>\n\n\n\n

        For product codes sent to another system, check the actual saved CSV values rather than appearance alone.<\/strong><\/p>\n\n\n\n

        References: Microsoft Support: Displaying leading zeros with custom number formats<\/a><\/p>\n\n\n\n

        CSV Editor (Free)<\/span><\/a><\/div>\n\n\n\n

        Check zeros in the saved CSV<\/h2>\n\n\n\n

        Double-clicking a CSV after editing may cause Excel to read it as numbers again, making zeros appear missing.<\/p>\n\n\n\n

        This does not distinguish zeros lost during saving from zeros removed when reopening.<\/p>\n\n\n\n

        Use one of these checking methods.<\/p>\n\n\n\n