Importing, Consolidating & Maximizing the Value of Excel Data

[#1]

Use the JMP Excel Import Wizard to import Excel data for simple and complicated Excel file structures. 

See how to:

• Easily source the data from Excel files with data
  • Anywhere in the worksheet, visible or hidden
  • Multiple worksheets
  • Merged cells
  • Multiple row or hierarchical column headers

• Identify your Excel structure before import to plan your approach
• Cell-grid based – properties, formulas, etc. that may differ between any pair of cells
• Files that contain multiple worksheets
• Charts that “float” over worksheet or as separate tab
• Worksheets with multiple tables or random content anywhere
• Row 1 that contains anything (column headers, data, or nothing)
• Tables that have multiple rows of column header names (or none)
• Understand JMP table structure
  
  • JMP file contains a single table
  • Each Column
    • Includes information that can be set after import (Data Type - Character or Number- and Modeling Type -Nominal, Ordinal or Continuous
    • Column Information/Properties support interactive analysis
    • Is named at top with unique name and reflects a single attribute or measure for all rows
  • Each row is a single unit or observation, e.g., one experimental run outcome, one sample, one wafer, one patient, one event, etc.
    • Row 1 contains first data point
• Use the JMP Excel Import Wizard
  • Manage merged cells and repeating data
  • Handle hidden, empty and nested columns
  • Handle replicate data in spanned rows
  • Handle multiple tables in one worksheet
  • Combine multiple tables
  • Handle different types of data using Format Patterns
  • Handle Excel formulas because cells with formulas will import as the static value when Excel file saved, so consider doing calculations in JMP as formulas after import
• Save work to JMP17 Workflow Builder

Manage Importing Excel Merged Cells to JMP

Questions answered by Greg Flexman @GregF_JMP and Clark Ledbetter @Clark_Ledbetter at the 2024 live webinar.

Q: I've noticed that if the excel file is filtered, and we try to import it into JMP, it only brings in the rows that are shown as a result of the filter. Is there a way to bring in the entire file even if we forgot to remove the filters? (i.e. is there a setting we can change to default to always NOT supressing hidden rows?)

A: Yes, in the import wizard on the second page, i.e. after you select "NEXT" there is a setting to "suppress hidden rows". You can toggle that to bring in the entire data sheet. The wizard by default assumes if filters on are in Excel that is what you want, but you can overrride with this "suppress" setting.

Q: Is the workflow builder only for 17?

A: Correct. Workflow Builder was released in JMP 17. If your maintenance is up to date you can upgrade to latest JMP version for free.

Q: How do you concatenate two jmp files? Is the same approach used as is for excel files?

A:  Open the files then Tables>Concatenate

Q: Sometimes when I import Numeric Data it imports as Character Data, for example dates or a string that has an exponent symbol.
A: If there is any Character (Non-number) data in a column, the entire column with be imported as Character Data and you will need to find the problem data and correct it before changing the Column Info>Data Type to Numeric.

Q: How can I correct the loss of leading zeros, like in zip codes such as 06518?
A: Here is the formula for adding leading zeros to Zip Code: Right(Char(:col), 5, "0").

Resources

• Documentation on importing Excel files
• Workflow Builder demo