Preventing Truncated Numbers in CSVs (Tracking & Account IDs)

Overview

When you export or import data via CSV (comma-separated values), long numeric strings (for example, tracking numbers, account IDs) may be truncated, rounded, or displayed in scientific notation. This happens because tools like Excel (and other spreadsheet applications) try to interpret numeric fields and can’t reliably show every digit for very long numbers. This article explains why it happens and shows how to avoid losing or corrupting these numbers.

Why This Happens

• Spreadsheet programs (Excel, Numbers, etc.) often auto-convert cells they think are “numbers,” applying formatting like scientific notation or rounding when they exceed certain digit limits.

• In Excel’s case, numbers longer than 15 digits usually get converted to scientific notation or lose precision.

• As a result, critical identifiers like tracking numbers or long account strings may be shortened or altered.

• These changes happen silently unless specific precautions are taken.

How to Prevent Truncation

When Opening a CSV File Directly

• If you see a prompt that Excel “will perform data conversions,” choose “Don’t Convert” (or similar wording) to preserve raw values.

• If there’s no prompt, you’ll need to adjust Excel’s default settings for automatic conversions (see next section).

Adjusting Excel Settings

Mac

1. In Excel, go to Excel → Preferences (from the top menu).

2. Under the Authoring section, choose Edit.

3. In the Automatic Data Conversions section, uncheck: 

   • Keep first 15 digits of long numbers and display in scientific notation
   • Convert digits surrounding the letter ‘E’ to scientific notation
4. (Optional) To always see a warning, check “When loading a CSV file or similar file, notify me of any automatic data conversions.”

Windows (Excel)

1. Go to File → Options.

2. On the left, select Data (or the relevant section controlling automatic conversions).

3. Find the settings related to automatic numeric formatting and uncheck:
   • Automatically format numbers with more than 15 digits in scientific notation
   • Convert numbers containing “E” into scientific notation
4. (If available) Enable an option to notify you of automatic data conversions when opening CSVs. 

Best Practice: Import CSV via the Data Import Tool

Instead of opening a CSV directly, use Excel’s import functions to explicitly define column formats. This gives you control over how each field is interpreted.

Mac Instructions

1. Open a blank Excel workbook.

2. Use Data → Get Data (Power Query) → From Text (Legacy) (or equivalent).

3. Select your .csv file and click Get Data.

4. In the import wizard, choose Delimited, then Next.

5. Set comma as the delimiter (or the appropriate delimiter).

6. When you get to the step where you assign column data types, select the column(s) for tracking or IDs and choose Text.

7. Finish the import and load it into the current worksheet.

Windows Instructions

1. Open a blank Excel file.

2. Use Data → Get Data → From Text/CSV.

3. Choose your .csv file and click Import.

4. In the preview window, set Delimiter to comma and change Data Type Detection to Based on entire dataset (so Excel doesn’t auto-guess formats prematurely).

5. For columns containing tracking numbers, override the type to Text.

6. Click Load to insert data into your sheet.

Summary / Best Practices

• Avoid opening CSV files by double-clicking—use the import tools so you control formats.

• Always set tracking, account, or long numeric fields as Text during import.

• Adjust Excel’s automatic conversion settings so it doesn’t silently reformat large numbers.

• Use a notification or prompt (if available) so you catch any unintended conversions.