Forum Discussion
Saving as a CSV changes some numbers to dates
We have a file with a field for ordering seats. The field has values such as 12-13, 18-19, 22-23. Any value that starts with 12 or lower gets changed to a date.
For example, 8-9 gets changed to Aug-9. I don't know where this date format comes from, because the default date format would be 8/9/25. I've tried formatting the field in the original file as general, text & special, but it always ends the same. I added a # before the seats & the fields didn't change, but the second I removed the # BOOM, Excel screwed up & formatted them as a date.
I've tried opening the CSV through the data ribbon as well as just double clicking the file, but to no avail, any way I open the CSV, it has changed the seats to a date
One more option is to enable in Options legacy From Text connector
With it import csv as Data->Get Data->Legacy Wizards->From Text (Legacy), take comma as separator, on the third step of the wizard select all columns and assign Text column data format.
finally uncheck adding data to data model and select destination cell
3 Replies
One more option is to enable in Options legacy From Text connector
With it import csv as Data->Get Data->Legacy Wizards->From Text (Legacy), take comma as separator, on the third step of the wizard select all columns and assign Text column data format.
finally uncheck adding data to data model and select destination cell
THANK YOU! The legacy import worked like a charm. You have my undying gratitude
Preceding each term of the source data by an apostrophe " ' " should force the data to text, but so should pre-formatting the cells as text (once the data is read, it is too late). Importing via Power Query can also improve the chance of success because I think Excel respects text formatting from PQ.
