Numbers converting to Scientific Formats in .csv files

lorencapps

New Member
Joined
Apr 12, 2011
Messages
2
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!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,755
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 12, 2011
Messages
2
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,755
Office Version
  1. 365
Platform
  1. Windows
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.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,611
Messages
5,765,414
Members
425,285
Latest member
andypandypoo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top