cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
jerseygirl
Level II

Blank values imported from Excel into JMP as zeros: how to change them to missing?

I am somewhat new to JMP and import excel files regularly. There are many cells within the Excel spreadsheet that are intentionally left blank and when I put them into JMP they display as 0. I want them to be missing. How can I do this without doing list or range checks for each column in my dataset?

1 ACCEPTED SOLUTION

Accepted Solutions

Re: How to change all zero value's to missing

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

View solution in original post

3 REPLIES 3

Re: How to change all zero value's to missing

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.
jerseygirl
Level II

Re: How to change all zero value's to missing

Thanks for the info. I tried to add the Substitute formula but it didn't really work. However, I did save the Excel file in a *.CSV format and that did the trick. I would try writing a script but I know nothing about writing scripts - that would take me forever to figure out.

Thanks again :)

Re: How to change all zero value's to missing

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