Home

Unexpected file type behavior when saving in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-678044%22%20slang%3D%22en-US%22%3EUnexpected%20file%20type%20behavior%20when%20saving%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-678044%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20am%20using%20Excel%20Pro%20Plus%202016%2C%20in%20Windows%2010.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20have%20been%20using%20Excel%20with%20files%20in%20Excel%20and%20csv%20formats%20since%20the%201990s.%20I%20am%20very%20familiar%20with%20file%20type%20saving%20options%20and%20Excel's%20behavior%20in%20general.%20But%20just%20today%20I%20noticed%20something%20new%20and%20unexpected%20happening.%3C%2FP%3E%3CP%3EI%20open%20a%20csv%20file%20(called%20'filename.csv')%20with%20a%20.csv%20extension.%20I%20work%20in%20that%20file%20a%20bit.%20I%20click%20the%20close%20button%20and%20am%20prompted%20with%20the%20grammatically%20sloppy%20phrase%2C%20%22Want%20to%20save%20your%20changes%20to%20'filename.csv'%3F%22%20I%20click%20the%20SAVE%20button.%20Now%2C%20looking%20at%20the%20file's%20icon%2C%20it%20is%20still%20in%20the%20csv%20format%20and%20still%20has%20the%20.csv%20extension%2C%20as%20expected.%20However%2C%20when%20I%20open%20the%20file%20again%2C%20there%20have%20been%20significant%20format%20changes.%20All%20of%20the%20columns%20have%20been%20condensed%20into%20the%20first%20column.%3C%2FP%3E%3CP%3ESo%20apparently%2C%20it%20has%20been%20saved%20in%20.xlsx%20format%2C%20even%20though%20the%20file%20type%20never%20changed%20from%20csv.%3C%2FP%3E%3CP%3E(Note%20that%20under%20Excel%20Options%20%26gt%3B%20Save%20%26gt%3B%20Save%20Workbooks%20the%20%22Save%20files%20in%20this%20format%3A%22%20option%20is%20set%20to%20.xlsx%2C%20because%2C%20frustratingly%2C%20there%20is%20no%20option%20to%20simply%20save%20files%20in%20their%20original%20format%20by%20default%2C%20as%20used%20to%20happen.)%3C%2FP%3E%3CP%3EThe%20core%20of%20the%20issue%20is%20that%20the%20file%20has%20undergone%20conversion%20to%20xlsx%20without%20changing%20the%20filetype%20itself.%20Is%20this%20really%20supposed%20to%20happen%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-678044%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-678328%22%20slang%3D%22en-US%22%3ERe%3A%20Unexpected%20file%20type%20behavior%20when%20saving%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-678328%22%20slang%3D%22en-US%22%3EHello%20there.%20It%20seems%20that%20at%20some%20point%2C%20when%20the%20file%20was%20opened%20in%20Excel%2C%20the%20Text%20Import%20Wizard%20was%20ran%20and%20the%20'Comma'%20was%20removed%20as%20a%20delimiter.%20This%20put%20the%20data%20into%20a%20single%20stream%20of%20data%20(i.e.%20a%20single%20column).%20My%20assumption%20is%20it%20was%20then%20saved%20as%20txt%20type%2C%20although%20it%20could've%20been%20xlsx%20all%20the%20same.%3CBR%20%2F%3E%3CBR%20%2F%3ETo%20get%20your%20data%20back%20into%20csv%20format%2C%20you'd%20need%20to%20go%20to%20Data%20%26gt%3B%20Text%20to%20Columns%20%26gt%3B%20Delimited%2C%20and%20select%20the%20Comma%20type.%20Depending%20on%20your%20data%2C%20any%20UTF-8%20conversion%20may%20alter%20your%20data%2C%20such%20as%20if%20you%20didn't%20have%20quotes%20surrounding%20commas%2C%20etc.%20The%20bottom%20line%20is%2C%20if%20your%20data%20is%20still%20there%2C%20you%20can%20still%20save%20it%20as%20csv%20format.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-678678%22%20slang%3D%22en-US%22%3ERe%3A%20Unexpected%20file%20type%20behavior%20when%20saving%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-678678%22%20slang%3D%22en-US%22%3EIf%20possible%20I'd%20recommend%20leaving%20the%20original%20csv%20untouched%20and%20importing%20it%20into%20Excel%20using%20Power%20Query.%3CBR%20%2F%3E%3CBR%20%2F%3ECertain%20date%20formats%20and%20number%20%2F%20text%20items%20have%20often%20been%20changed%20in%20the%20past%20when%20editing%20in%20Excel%20and%20then%20saving%20as%20CSV%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-679015%22%20slang%3D%22en-US%22%3ERe%3A%20Unexpected%20file%20type%20behavior%20when%20saving%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-679015%22%20slang%3D%22en-US%22%3EYes%2C%20good%20point.%20Data%20connections%20ftw.%3C%2FLINGO-BODY%3E
NU39283
Occasional Visitor

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?

 

3 Replies
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.
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
Yes, good point. Data connections ftw.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies