Overview
When you open a CSV file directly in Excel, it automatically converts numeric-looking values (like employee IDs, zip codes, or phone numbers with leading zeros) into numbers, stripping the leading zeros. The correct approach is to use Excel's Power Query import feature, which lets you specify the column data type as Text — preserving all leading zeros exactly as they appear in the source file.
| Tool Required | Microsoft Excel (any recent version) |
| Estimated Time | 2–3 minutes |
Step-by-Step Instructions
Step 1: Open Excel (Do NOT open the CSV directly)
Launch the Microsoft Excel application. Important: Do not double-click the CSV file or open it directly — this causes Excel to auto-format the data, stripping any leading zeros.
Step 2: Import via Data > Get Data > From File > From Text/CSV
In Excel, click the Data tab on the ribbon. Then navigate to Get Data → From File → From Text/CSV. Browse to your CSV file and click Import.
Step 3: Click "Transform Data"
A preview of your data will appear. Instead of clicking Load, click the "Transform Data" button. This opens the Power Query Editor where you can set column data types.
Step 4: Change Column Type to Text
In Power Query, right-click on the column header that contains leading zeros. Go to Change Type → Text. This tells Excel to treat the column as plain text, preserving all characters including leading zeros.
Step 5: Click "Replace the Current"
A dialog will ask whether to replace the existing type conversion or add a new step. Click "Replace the current" to apply the Text type directly.
Step 6: Close & Load, then Format Cells as Text
Click "Close & Load" to bring the data into Excel. Once loaded, right-click the column → Format Cells → Select Text → click OK. Any values entered manually in this column will now also retain leading zeros.
Saving the File
When saving, choose File → Save As and select CSV UTF-8 (Comma delimited). This format preserves your data as plain text and maintains compatibility with other systems that read CSV files.
| ⚠️️ Important Reminder: Never open your CSV by double-clicking the file. Always use the Data → Get Data → From Text/CSV import path to maintain full control over column data types. |
Comments
0 comments
Please sign in to leave a comment.