Choose Language Hide Translation Bar
tsolomon
Community Trekker

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

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

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions

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

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

View solution in original post

6 REPLIES 6
Highlighted
vince_faller
Super User

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

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

Vince Faller - Predictum
0 Kudos
tsolomon
Community Trekker

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

I have attached an example.

Thanks

TS

0 Kudos
sornasst
Community Trekker

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

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

0 Kudos

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

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

View solution in original post

tsolomon
Community Trekker

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

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

0 Kudos

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

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

0 Kudos