Prevent Excel from Automatically Converting Dates When Importing CSV Files
Question details
Control CSV column data types in Excel so values that resemble dates are not automatically converted during import.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs to open or import a CSV file while preserving ISO-formatted dates, codes, or date-like values in the intended format.
- Observed behavior
- When the CSV file is opened by double-clicking, Excel automatically guesses column types and may convert some values to dates while leaving similar values as text.
Do not open the CSV file by double-clicking it. Start from a blank Excel workbook so you can use the import preview and choose the correct data type for each affected column.
Import the CSV through Get Data and set the column type
Use this method in current versions of Excel when you need to review and control data types before the CSV is loaded into a worksheet.
The Text/CSV import workflow prevents Excel from relying only on automatic type detection. In the preview or Power Query Editor, set date-like identifiers and values that must remain unchanged to Text.
In a blank Excel workbook, select Data > Get Data > From File > From Text/CSV, then select the CSV file and choose Import.
In the preview window, confirm the file origin, delimiter, and detected column layout before loading the data.
Select Transform Data to open Power Query Editor, then select the column that Excel might convert automatically.
On the Transform tab, select Data Type and choose Text for identifiers or date-like strings that must not change. Choose Date only when the column should contain actual Excel dates.
Select Home > Close & Load to place the imported CSV data into the worksheet with the selected column types.
Use the legacy Text Import Wizard to assign Text format by column
Use this option when the legacy import wizard is available and you want to specify a format for each CSV column before it is imported.
Import CSV Data as Text in WPS Spreadsheets
WPS Spreadsheets provides a familiar spreadsheet workflow for opening and organizing CSV data. Use its text import options to review delimiters and keep date-like identifiers as text before working with the file.
- 1. Open WPS Spreadsheets: Launch WPS Office and create a new Spreadsheet workbook instead of opening the CSV by double-clicking it.
- 2. Start a CSV import: Use Menu > Open and select the CSV file, then use the text import options shown for the file.
- 3. Confirm the delimiter: In the import dialog, choose the delimiter used in the file, such as Comma, and verify that the preview separates fields correctly.
- 4. Keep date-like values as text: Select the affected column in the import preview and choose Text when the values are identifiers or fixed-format strings rather than working dates.
- 5. Complete the import: Select Open or OK to load the CSV into WPS Spreadsheets, then save the workbook as XLSX if you need an Excel-compatible workbook.
Frequently Asked Questions
Why does Excel change values in a CSV file when I double-click it?
Double-clicking opens the CSV with Excel's automatic type detection. Excel may interpret values such as 2024-01-05, 03-04, or 00123 as dates or numbers instead of retaining the original text.
How do I keep ISO-formatted values from becoming dates in Excel?
Use Data > Get Data > From File > From Text/CSV, select Transform Data, select the affected column in Power Query Editor, and choose Transform > Data Type > Text before selecting Home > Close & Load.
Should I select Text or Date for a CSV date column?
Choose Date only when you need Excel to calculate, sort, or filter the values as real dates. Choose Text when the values are codes, identifiers, or date-like strings that must display exactly as written in the CSV.
Can I use the Text Import Wizard instead of Power Query?
Yes. Enable it through File > Options > Data > Show legacy data import wizards, then use Data > Get Data > Legacy Wizards > From Text (Legacy). In the final wizard step, select the relevant column and choose Text.
Will formatting a worksheet column as Text after opening a CSV restore the original values?
Usually no. If Excel already converted the values during opening, changing the worksheet format afterward may not restore the original CSV text. Re-import the file through the Text/CSV import tool and assign the column as Text before loading it.
