Multiple issues with saving .xlsx file as a .csv

[superbeau]

I haven't done this in a while, but I'm now trying to convert an .xlsx file to a .csv file. I need to have a .csv file to import a bunch of data into a computer system at work. I've done this before and it has never been an issue. Now I'm having a bunch of issues. The biggest problem is that my source .xlsx file contains cells with 10-digit codes and some of them start with one or more zeros. In the source file, I have these cells set to "text" formatting so that Excel doesn't wipe the leading zeros. I click "Save As", select ".csv", enter a file name and location, and click "Save". The system pops up a new Excel window with the newly created CSV file, and the data looks correct. However, when I close that new file and then reopen the file by double-clicking it in my destination folder, it opens Excel and shows that the 10-digit codes have been converted from text to numbers, and thus Excel sees fit to drop any and all leading zeros! I thought the purpose of a .csv file was to store information without touching the formatting. Now these codes are not only worthless, but dangerous, because if I accidentally imported them into my work system, it would corrupt a bunch of other information because it won't recognize the codes properly.

Ok, second issue I'm seeing is that my source .xlsx file has multiple tabs. I want to be able to create one .csv file for each tab in the .xlsx file. I open a certain tab and go through the process above to save as a .csv file. Excel pops up a warning that converting to .csv will drop formatting and will only convert the active tab to a new .csv file. I click "Ok" and then the newly created .csv file opens automatically (like I mentioned above). However, instead of seeing a new .csv file with only one tab (from the active tab in the source file), I'm seeing all of the tabs. If I close and open the file - which blows up my 10dig codes like I said above - all of the tabs are still there. I have no idea what would happen if I tried to import that new .csv file into my computer system at work... but I'm guessing it would be bad.

I chatted with Microsoft Help and they basically said that this is the way it's supposed to be, which I'm pretty sure is NOT accurate. Has anybody else seen this stuff happening? Any fixes out there that I can try?

Thanks in advance...

[#2]

@superbeau 

---

@superbeau wrote:

I click "Save As", select ".csv", enter a file name and location, and click "Save". The system pops up a new Excel window with the newly created CSV file

---

This doesn't happen on my system.

And you don't open csv with Excel but import csv into Excel - either the legacy way or the Power Query way.

Check the csv content with a text editor. Does it show leading zeros?

[#3]

@Detlef Lewin yes, if I open the .csv file in Notepad, the leading zeros are there. So I guess I should just plan to Save As a .csv file and then not open that file again with Excel. But I wonder why then Excel opens the newly formed .csv file automatically in an Excel window right after you create it? And why does that file look correct, but if you close and reopen, it gets messed up?

[#4]

Also, I should have mentioned that I use Office 365, so I have the latest version of Excel running in Windows 10 Pro (64 bit) on a Surface Book laptop.

[#5]

@superbeau 

---

@superbeau wrote:

But I wonder why then Excel opens the newly formed .csv file automatically in an Excel window right after you create it?

---

I have no idea. It does not happen on my system.

[#6]

@superbeau 

To create separate csv files for each worksheet you may use powershell , sample is here Convert Excel file (XLSX) to CSV in Powershell , or similar VBA script, or manually: right click on sheet, Move or Copy, check Copy, from destination select new book and from it save as csv. Content will be in Excel window since that's default application for csv files. Modern Excel save all csv sheets into the file if Save As.

If close and open csv file in Excel again it performs default conversions, that was for all versions. In particular, numbers saved as texts will be converted back to numbers (but not only, there are another conversions). I don't know about workaround, the only not to open in Excel but import to Excel using legacy wizard or Power Query. In both cases mark proper columns as having Text type. 

[#7]

@Sergei Baklan Ok, thank you!

[#8]

@superbeau , you are welcome