Unexpected file type behavior when saving in Excel

[NU39283]

I am using Excel Pro Plus 2016, in Windows 10.

I have been using Excel with files in Excel and csv formats since the 1990s. I am very familiar with file type saving options and Excel's behavior in general. But just today I noticed something new and unexpected happening.

I open a csv file (called 'filename.csv') with a .csv extension. I work in that file a bit. I click the close button and am prompted with the grammatically sloppy phrase, "Want to save your changes to 'filename.csv'?" I click the SAVE button. Now, looking at the file's icon, it is still in the csv format and still has the .csv extension, as expected. However, when I open the file again, there have been significant format changes. All of the columns have been condensed into the first column.

So apparently, it has been saved in .xlsx format, even though the file type never changed from csv.

(Note that under Excel Options > Save > Save Workbooks the "Save files in this format:" option is set to .xlsx, because, frustratingly, there is no option to simply save files in their original format by default, as used to happen.)

The core of the issue is that the file has undergone conversion to xlsx without changing the filetype itself. Is this really supposed to happen?

[#2]

Hello there. It seems that at some point, when the file was opened in Excel, the Text Import Wizard was ran and the 'Comma' was removed as a delimiter. This put the data into a single stream of data (i.e. a single column). My assumption is it was then saved as txt type, although it could've been xlsx all the same.

To get your data back into csv format, you'd need to go to Data > Text to Columns > Delimited, and select the Comma type. Depending on your data, any UTF-8 conversion may alter your data, such as if you didn't have quotes surrounding commas, etc. The bottom line is, if your data is still there, you can still save it as csv format.

[#3]

If possible I'd recommend leaving the original csv untouched and importing it into Excel using Power Query.

Certain date formats and number / text items have often been changed in the past when editing in Excel and then saving as CSV

[#4]

Yes, good point. Data connections ftw.