topic How to keep numeric data from changing to scientific notation during import from Excel file in Discussions

How to keep numeric data from changing to scientific notation during import from Excel file

Wende — Mon, 11 May 2020 20:57:24 GMT

Hello,

I am importing data from an Excel file. My data becomes scientific notation and then I lose the details from the last two digits.

How can I import numbers from an Excel file so that JMP does not change the format to scientific notation?

Here is an example of the data:

Number
20160609144108701
20160609144108702
20160609144108703
20160609144108704
20160609144108705
20160609144108706
20160609144108707

Thank you for your help!

Wende

Re: How to keep numeric data from changing to scientific notation during import from Excel file

Craige_Hales — Mon, 11 May 2020 21:43:32 GMT

What does the data represent? If it is a date-time in 2016, precise to the millisecond, the answer might be for converting it to a JMP date-time value. If it is a part number you should keep it as character data.

Is it a CSV file? Are you using text file import or Multiple File Import or Excel Import?

I'm not an Excel expert, but I think those values are string data in Excel.

Most programs (JMP included) use a double precision floating point representation that can't handle exact integers bigger than 2^53, which is smaller than those numbers.

You can make JMP use a format that will look like an integer, but least significant digits are not really there.

Format( "Fixed Dec", 18, 0 )

There are at most 15-16 significant digits in a double.

big integer values

Re: How to keep numeric data from changing to scientific notation during import from Excel file

Thierry_S — Mon, 11 May 2020 21:53:03 GMT

Hi Wende,
I can confirm that for long numerical sequences (like you have), Import from Excel caps the number of significant digits to 15.
Here is a work around suggestion (not pretty but it should work):

If your values are not truly numerical but are strings made of digits, try adding one character at the end of the Excel column named Number. That will force JMP to load these as character. You can then extract the necessary information with Character functions.

Another way is to export your Excel file as TAB delimited and use the interactive dialog to set the data type

Finally, it looks like your data may be dates and times plus something else. If it is the case, it might be useful to look into data/time formatting.

Best regards,

TS

Re: How to keep numeric data from changing to scientific notation during import from Excel file

Wende — Fri, 15 May 2020 15:14:11 GMT

Thank you very much for your response!

I wound up adding a letter at the beginning so that the data would import correctly.

The format is a blend of date format and sequential numbering which causes the date to not work directly.

Thanks again.

Wende

Re: How to keep numeric data from changing to scientific notation during import from Excel file

Wende — Fri, 15 May 2020 15:21:34 GMT

Thank you very much for the knowledge about 2^53. I bet that some programmer thought there was no way that someone would ever need that big of a number when programming years and years ago on a 256 kb computer :)

I changed the file to import to a text file and also added a leading character to solve the issue.

Wende

Re: How to keep numeric data from changing to scientific notation during import from Excel file

dhwkoo — Thu, 09 Jul 2020 06:16:44 GMT

If using the import tool, you can select character for the column data type.