Preserve Leading Zeros and Decimal Text When Saving Excel Data as CSV
Question details
Preserve values such as 01.2020 as text, including their leading zeros, when saving Excel data to CSV and opening it again.
- Product
- Microsoft Excel CSV files
- Device & OS
- not provided
- Scenario
- The user saves worksheet data as a CSV file for use in another system, such as SAP, and needs the exported text to remain exact.
- Observed behavior
- The CSV may contain the correct text, but Excel can automatically reinterpret values such as 01.2020 as numeric data when the file is opened directly.
Use a text editor such as Notepad to check the CSV after saving it. If the file displays 01.2020 exactly, the export is correct; the change occurs during Excel's automatic CSV opening process.
Verify the CSV content in a text editor before changing the import method
Use this method to confirm whether the leading zero was preserved in the exported CSV itself.
CSV is a plain-text format and does not retain Excel cell formatting. A value stored as 01.2020 can be correct in the file even if Excel later converts its display when you double-click the CSV.
In Excel, select File > Save As, choose CSV (Comma delimited) or CSV UTF-8 (Comma delimited), and select Save.
Close the CSV in Excel, then right-click the .csv file in File Explorer and choose Open with > Notepad, or open it from within Notepad.
Find the relevant field and verify that it is written exactly as required, for example 01.2020. If it is present, the CSV export preserved the leading zero.
If you are uploading to SAP or another service, compare the text shown in Notepad with that system's required import value before uploading the CSV.
Import the CSV into Excel and set the affected column to Text
Use this method when you need to reopen or edit a CSV in Excel without allowing automatic type conversion.
Keep Leading Zeros When Importing CSV Files in WPS Spreadsheets
WPS Spreadsheets can import a CSV through its text import options so you can assign the affected column a Text format instead of allowing automatic numeric conversion. This helps preserve identifiers and decimal-like text such as 01.2020 for review or further editing.
- 1. Open a blank spreadsheet: Launch WPS Spreadsheets and create a new blank workbook.
- 2. Start the CSV import: Select Data > Import Data or use File > Open to select the CSV file and open the text import dialog when prompted.
- 3. Choose the delimiter: Select Comma as the delimiter and check the preview to ensure the fields are separated into the correct columns.
- 4. Format the affected column as Text: Select the column containing values such as 01.2020 and set its column data format to Text before completing the import.
- 5. Check the imported result: Confirm that the cells retain their leading zeros, then use File > Save As if you need to save the edited spreadsheet or export another CSV.
Frequently Asked Questions
Why does Excel remove the leading zero when I open a CSV file?
Excel automatically detects data types when opening CSV files directly. It may interpret 01.2020 as a number instead of text, even when the CSV file itself contains the leading zero.
How can I tell whether the leading zero was saved in my CSV file?
Open the CSV in Notepad or another plain-text editor. If the value appears as 01.2020 there, the exported file contains the correct text.
Does formatting a cell as Text in Excel guarantee that a CSV will preserve the format?
CSV does not store Excel formatting. Formatting the source cell as Text can help preserve the entered characters during export, but you must import the CSV column as Text when reopening it in Excel.
Should I double-click a CSV file to open it in Excel?
Not if the file contains values that must remain text. Open a blank workbook and use Data > Get Data > From File > From Text/CSV, then set the relevant column's data type to Text.
How should I prepare a CSV file for SAP identifiers with leading zeros?
Verify the CSV in a text editor and ensure every identifier exactly matches SAP's required format. When reviewing the file in Excel, import the identifier column as Text rather than opening the CSV directly.
