See how to open Excel files and use the Excel Import Wizard built into JMP to guide you through pulling in data that could be anywhere - visible or not - within one or more Excel worksheets.
JMP provides a structured row/column approach to data, where we want columns that represent a single construct, variable or attribute. Usually, we want only one column associated with that attribute, unless the analysis necessitates otherwise. Often JMP uses a row to represent a single unit or observation, so that all the data across all the rest of the columns reflect only that one unit or observation.
Default JMP Preferences are set to open the Excel Import Wizard when you open an Excel file. Check this setting (on Windows) from File>Preferences>Third Party Data.
Excel Import Wizard Preference setting (Windows).Excel Import Wizard Preference setting (Windows).
Simple Introductory Example
- Double click on an Excel file to open the file into the Excel Import Wizard.
- Select the desired Worksheet and view Data Preview to see what will happen using current Worksheet Settings.
- After import, view Modeling Types for each variable/column on Column Panel and number of rows to make sure import brought in all your rows.
Example: Merged Cells and Multiple Column Headers
- See in Data Preview how JMP handles Merged Cells by default, then select NEXT to view and/or change those default Merged Cell settings.
- In this example it used Replicate data in spanned rows.
- In this example, uncheck Suppress hidden columns to see what JMP brought in. In this example though, we don’t want those hidden cells, so leave the default setting with the box checked to Suppress.
Example: Two Tables in One Excel Worksheet
- There are two tables, process data and associated spec limits, but only one column header.
- Though the column headers are below one of the tables, the Worksheet Settings are flexible, allowing the data above to be paired with headers below. Use the NEXT button and select where the data end to pull in the table just right – providing one table for process data and one for associated spec limits.
- Bonus: See how to load spec limits to data table as Column Properties meta data.
Example: Multiple Tables Across Multiple Worksheets
- 5 different Excel tables have the same format and are basically the same content, just spread across different Excel Worksheets.
- Check Use for all Worksheets check box.
- Select worksheets from the Worksheets Window.
- Check the box to concatenate Worksheets and let JMP match columns.
- Optionally, choose Add Column that includes Worksheet Name.
- See how JMP might change Data Type if it finds an inconsistency of data type in Excel, and how to find and handle the inconsistent data.
Example: Handling Formulas
- JMP imports data, not the formulas, and Excel allows different formulas in various cells. That inconsistency makes analysis difficult and potentially costly, so we clean/recreate the correct formulas in JMP.
- In this example, we use Stack to format the imported data to give a table that will take advantage of a single formula column that accurately reflects the nature of the relationships and provides the calculated data so it can be analyzed in JMP.
Example: Workflow Automation (Also avaiable as separate video.)
- After we use the Excel Import Wizard to import data, we can use the associated script that JMP provides for the sequence of steps we followed during and after import.
- In JMP 16 and later, use the Enhanced Log (Action Recorder) to capture all import steps, data preparation steps, and final reports. In this case those are Import, Stack, create a Graph created using Graph Builder, and a create a Stability Analysis (using the Degradation Report).
- Save Script to Script Window from the Enhanced Log and Hit the Run/Play icon on the Menu Bar.
- In versions earlier than JMP 16, you can combine the Import, Stack, Graph Builder, and Degradation Report scripts manually.
Tips
- Restore Default Settings if you want to get rid of past work importing Excel files using the Import Wizard.
Multiple Worksheet OptionsMultiple Worksheet Options
Data PreviewData Preview
Merged CellsMerged Cells
Default Treatment of Merged CellsDefault Treatment of Merged Cells
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: From Attendee Mark Dayton @MarkDayton : Here is the formula for adding leading zeros to Zip Code: Right(Char(:col), 5, "0").
