Excel ‘helpfully’ converting numbers longer than 15 digits to scientific notation when importing from .csv

When you attempt to open or import .csv data into Excel, long numbers are helpfully converted into scientific notation. In most situations, this is fine – it make it easier to see the number in a cell and the original number can be seen in full if the cell number format is changed.

However, when numbers are longer than 15 digits, Excel silently rounds anything after the 15th digit – a real problem with GUIDs in general and QuestionMark Perception Question IDs in particular.

There are a number of potential solutions to this, any of which may work for you:

  1. Change the file’s .csv extension to .txt or  choose Data | Get External Data | From Text to bring up the Text Import Wizard and, in Step 3 of 3 where you set the data format, select the appropriate column (or columns with Shift held down), set the Column data format to Text. However, this appears to fail with very high numbers of columns (in this case >200), leading me to solution 2:
  2. Upload the .csv file into Google Docs, making sure that Convert documents, presentations, spreadsheets, and drawings to the corresponding Google Docs format is ticked. Then export as Excel by File | Download As | Microsoft Excel. Thanks to kpm for this. However, this still seems to randomly covert a few GUIDs into 15 significant figure sceintific numbers.
  3. The solution which worked for me was to open the .csv file in OpenOffice 3 Calc and, as with Excel, in the Text Import dialogue, select all the  columns in the Fields section and then change their Column type to Text. Unlike Excel, this did reliably import all >200 columns without converting any GUIDs.

 

 

One Reply to “Excel ‘helpfully’ converting numbers longer than 15 digits to scientific notation when importing from .csv”

  1. Hi, friend this is very nice post, which is very informative for me. I am sure other people are like this post. Now some days I was finding excel tutorial and now I think this is my end place where I get perfect information that I needs.

Leave a Reply

Your email address will not be published. Required fields are marked *