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?
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
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.
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?
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.