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

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