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

[#1]

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

[#2]

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

[#3]

I have attached an example.

Thanks

TS

[#4]

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

[#5 · Accepted solution]

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

[#6]

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

[#7]

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