Subscribe Bookmark RSS Feed

Import numeric and character data with excel wizard results in missing data

tsolomon

Community Trekker

Joined:

Oct 28, 2015

I am trying to import multiple excel worksheets using the excel wizard. The data is a primarily numeric with some character data. The rows that have character data usually have < or > symbols. These cells show as no data when imported into JMP. How can import the rows as characters so they are visible in the JMP table?

Thanks

TS

6 REPLIES
vince_faller

Super User

Joined:

Mar 17, 2015

Do you have an example excel sheet?  JMP has always automatically picked the right column type for me. 

tsolomon

Community Trekker

Joined:

Oct 28, 2015

I have attached an example.

Thanks

TS

sornasst

Community Trekker

Joined:

Feb 2, 2016

Hi, as far as I know, JMP does not allow for multiple data types in one column. So, the trick that I usually use is to first load the data as characters, then using a formula in a new column convert the actual data into numeric [NUM(string)], convert the "<" as -999 (or another arbitrary small number) and the ">" as 999 (or another arbitrary large number).

Not very pretty but it works

briancorcoran

Joined:

Jun 23, 2011

JMP will generally cast the column to character type if it detects character data in the first 100 rows.  If the character data in question is beyond row 100, then it is possible that it will be turned into a missing value because the column has been "detected" as numeric.  If you have JMP 12, you can force JMP to scan all the rows before guessing the column type by selected the "Show all rows" checkbox in the "Preview Pane Refresh" panel.  This should prevent the problem from occurring.

Brian Corcoran

JMP Development

tsolomon

Community Trekker

Joined:

Oct 28, 2015

Hi Brian, thanks for the response. I have JMP 11. Just as a test, I added

character data on row 6 but when I import the excel file, the row 6 data is

missing. If I select the "Always enforce row 1 labels" as Never than the

characters in column C of my file show but column E is still missing?

Thanks

TS

briancorcoran

Joined:

Jun 23, 2011

I haven't been able to replicate your usage case.  Do you have a lot of empty cells in the data?  Because that will skew the result.  I know in subsequent releases we have made JMP more sensitive to retaining character data, with the upcoming JMP 13 being the most sensitive.  That said, if you the data types in your Excel file are actually formatting as Text rather than General, JMP should honor those settings.

Brian