Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Numbers converting to Scientific Formats in .csv files

  1. #1
    New Member
    Join Date
    Apr 2011
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Numbers converting to Scientific Formats in .csv files

    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!

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    47,279
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Numbers converting to Scientific Formats in .csv files

    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).
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Apr 2011
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Numbers converting to Scientific Formats in .csv files

    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!

  4. #4
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    47,279
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Numbers converting to Scientific Formats in .csv files

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •