Numbers converting to Scientific Formats in .csv files

[lorencapps]

I know this question has been asked a bajillion times, so I apologize for the redundancy.

I am working with an Excel spreadsheet and saving it as a .csv file in order to upload to an application that parses out the .csv data as transactions. The system requires .csv files, so this is how I need to save my doc (with this extension). I have been successful at preventing Excel from coverting that long number into scientific format. I have saved as a TXT file, pasted the longer number and it displays correctly. That is all good. But I have to save as a .csv. So if I do that, close the Excel window, and then open again (as the .csv file), the numbers are back to being displayed in scientific format. I have tried creating an Excel doc from scratch and entering text in Text format, to see if this created a cleaner file. But again, the second I save as .csv, close the window and then open that file up again, that dang scientific format is back.

Does anyone have any idea of how to work around this? Once I have successfully gotten the numbers to display as the long-chain number, how can I get them to "stick" so that they don't revert back to scientific format when I reopen the file?

Thanks so much for your help!

 

[Joe4]

So if I do that, close the Excel window, and then open again (as the .csv file), the numbers are back to being displayed in scientific format.

Welcome to the Board!

How are you re-opening the CSV file? Are you opening it in Excel?
I recommend NEVER using Excel to open CSV files directly, as it re-converts the data's format based on its "best guesses" and does things like this and drops leading zeroes on Zip Codes and ID Numbers.

So you CSV file *may* actually be fine, and the problem may just be trying to view it with Excel. If you want to see the "true" content of a CSV file, use a Text Editor to view it (NotePad usually comes with Office).

 

[lorencapps]

hey Joe - thanks for your reply (and warm welcome)! Oh I see, so this converting thing is just a quirk of Excel's? the content of the .csv should still be good (text), but it needs to be viewed in a non-arrogant and over-complicating platform (like notepad or text editor) - ? well that makes sense then. if the data is still "good," that is all i really need.

thanks so much!

 

[Joe4]

Just be sure to open the CSV file and view it in a Text Editor to confirm that everything really is in your desired format.

I think this is a case where Microsoft "outsmarted" itself. When you import/open any other text file in Excel, it invokes the Import Wizard where you need to tell it the how the file is delimited and the format of each field. But with CSV files, Excel thinks it is smart enough to figure it out on its own, only sometimes it guesses wrong. Anything that looks like a number is treated like a number (Access has the same problem in importing Excel files).

It really irks me that Office sets Excel as the default program to open CSV files. I always change peoples' computers to use a text editor instead (we have an after-market one named UltraEdit). By the way, if you are looking for a free Text Editor that is more robust than NotePad, take a look at NoteTab Lite.