See how to import Excel files and use the Excel Import Wizard.
The Excel Import Wizard is part of JMP and helps get Excel data into the right format for analysis.
Part 1: Introduction to the Excel Import Wizard
We need to get data into the right structure for analysis in JMP, and the Excel Wizard will help us accomplish this. JMP has a structured row/column approach to data, where columns represent a single construct, variable or attribute. JMP uses a row to represent a single unit or observation, so that all the data across the row in all the rest of the columns reflect only that one unit or observation.
Use the File -> Open command to open an Excel worksheet or workbook in JMP. By default, the Excel file should be opened in 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.
Part 2: Dealing with merged cells and spanned rows in Excel
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.
Part 3: Importing and applying a spec limit table that's part of an Excel data table
- 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.
Part 4: Importing and Combining mulitple worksheets into a single JMP table
- Several 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.
Part 5: Dealing with nested column labels and using hierarchical information to create variables
Part 6: Multi-series stack of columns, recoding data, and 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.
Tips
- Restore Default Settings if you want to get rid of past work importing Excel files using the Import Wizard.
Additionally, here is a short video on automating Excel importing and making a report:
Excel and Automation
This is part of the Get Going with JMP series.