Keeping cell number format when converting from Excel to .CSV

[crb128]

I have files that need to be uploaded to our operational software using the .CSV format. When I receive the initial file, it is in excel format and thus needs to be converted. Problem is that some of the cells being uploaded contain characters saved as a "Text" formatted cell and are converted to whatever excel thinks it should be when changing to .CSV.

For example. A cell could contain 3981-5-19 and with a cell format of "text". When the file is converted to .CSV, the cell now would read 5/19/3981 or 19-May. I need the original formatting to stay when I convert the file, otherwise the item isn't recognize in the .CSV upload to our oeprational software.

Any help, suggestions, responses, etc. is greatly apprecaited.

Thanks!

[martindwilson]

a cell formatted as text saved as csv keeps its formatting

[crb128]

a cell formatted as text saved as csv keeps its formatting

Appreciate the response, but that's not happening in this case. It's the first thing I tried and got the same result each time. Tried several different variations of copying, pasting, saving, etc. and each time, it changed from a TEXT format in excel and defaulted automatically back to GENERAL format after being saved. Same occured when I changed the cell format to TEXT after it had already been converted to .CSV.

If there is a setting or something I am missing, feel free to point out what that would be, but otherwise, what you claim in your response, is not happening.

[martindwilson]

got a sample work book?

[crb128]

I've attached the xls file I am pulling the data from.

[martindwilson]

here it is saved as csv

[MrShorty]

For example. A cell could contain 3981-5-19 and with a cell format of "text". When the file is converted to .CSV, the cell now would read 5/19/3981 or 19-May.

If I can nitpick -- exactly how are you determining that the given cell entry is wrong in the CSV file? I

If I take your file, save as a CSV file, then open the CSV file in a text editor (like wordpad), the file looks exactly like it did in Excel (without the gridlines). If I then open the CSV file back into Excel, that's when Excel tries to convert the text strings it can recognize as dates to dates.

Your post suggests that these CSV files are not intended to be read by Excel. I would suggest you determine for sure that the problem is occuring when Excel creates the CSV file. The CSV file might be correct, even if Excel can't read the file correctly.

[crb128]

If I can nitpick -- exactly how are you determining that the given cell entry is wrong in the CSV file? I

If I take your file, save as a CSV file, then open the CSV file in a text editor (like wordpad), the file looks exactly like it did in Excel (without the gridlines). If I then open the CSV file back into Excel, that's when Excel tries to convert the text strings it can recognize as dates to dates.
Your post suggests that these CSV files are not intended to be read by Excel. I would suggest you determine for sure that the problem is occuring when Excel creates the CSV file. The CSV file might be correct, even if Excel can't read the file correctly.

Nailed it. When reading the file in wordpad, it looks exactly as it's meant to. When trying to read it in EXCEL, that's when the problem occus. You are correct that the file is accurate, the program reading it is skewing it when viewed in EXCEL.

My problem is that the upload I need only recognizes the EXCEL .CSV when deducing the columns - when I use the .txt format, it brings up everything in a single column.

If there is a way to correct the formatting when EXCEL read the file so that the cell format doesn't convert, then that's what I need to. If that's not possible, then it's an issue with the software I am uploading to and I need to get it corrected through that route.

[MrShorty]

If there is a way to correct the formatting when EXCEL read the file so that the cell format doesn't convert, then that's what I need to. If that's not possible, then it's an issue with the software I am uploading to and I need to get it corrected through that route.

I think the latter option is best. .CSV files are really no different from any other ASCII text file (.txt) -- they can't really store formatting information. Any "changes" to the .CSV file (like interpreting xxxx-x-x as a date rather than text) are done at the whim of the programming opening/reading the .CSV file. In the end, the best solution to your problem (if there really is a problem) will be determined by understanding how the target program reads the data in the file.