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
- :
- Blank values imported from Excel into JMP as zeros...

Topic Options

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

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

Nov 30, 2010 11:19 AM
(1899 views)

1 ACCEPTED SOLUTION

Accepted Solutions

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

Nov 6, 2017 7:56 AM
(1914 views)

Solution

As stated previously,

First, I am still on JMP 8.0 and when I import an Excel file, either from 97-2003 or 2007 formats, the empty cells come across as empty cells. You might have some luck if you can strip down the file to the minimum formatting before transferring, or save it in another format (like *.CSV).

Second, if you have to use Excel and you have to do this often and you never have zeros that you want, I would write a jsl script to process the file for you (even though you asked for a way to avoid checking each column).

You can test this out by adding a new column and creating a formula that says something like:

Substitute( :Column with Zeros, 0, Empty() )

A simple script that makes a new table, populates the new table with only the non-zero entries, adjusts any formatting mistakes, and closes the old table can be written in a few lines and run very quickly.

Duane Hayes

3 REPLIES

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

Nov 30, 2010 1:54 PM
(1744 views)

Second, if you have to use Excel and you have to do this often and you never have zeros that you want, I would write a jsl script to process the file for you (even though you asked for a way to avoid checking each column).

You can test this out by adding a new column and creating a formula that says something like:

Substitute( :Column with Zeros, 0, Empty() )

A simple script that makes a new table, populates the new table with only the non-zero entries, adjusts any formatting mistakes, and closes the old table can be written in a few lines and run very quickly.

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

Dec 4, 2010 2:44 PM
(1744 views)

Thanks again :)

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

Nov 6, 2017 7:56 AM
(1915 views)

As stated previously,

Second, if you have to use Excel and you have to do this often and you never have zeros that you want, I would write a jsl script to process the file for you (even though you asked for a way to avoid checking each column).

You can test this out by adding a new column and creating a formula that says something like:

Substitute( :Column with Zeros, 0, Empty() )

A simple script that makes a new table, populates the new table with only the non-zero entries, adjusts any formatting mistakes, and closes the old table can be written in a few lines and run very quickly.

Duane Hayes