Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- How to keep numeric data from changing to scientific notation during import from...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 11, 2020 1:57 PM
(684 views)

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

Wende

3 ACCEPTED SOLUTIONS

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

Craige

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Wende

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

5 REPLIES 5

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

Craige

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

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

Wende

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

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

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

Thierry R. Sornasse

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Wende

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Article Labels

There are no labels assigned to this post.