Advanced Options in the Excel Import Wizard in JMP 13
Dec 8, 2016 10:27 AM
| Last Modified: Dec 8, 2016 12:39 PM
The Excel Import Wizard was introduced for Windows in JMP 11, and on the Mac in JMP 12. It has proved to be a popular and valuable feature for getting Excel data into JMP. JMP 12, as described in an earlier blog post (“Excel Import Wizard in JMP 12”), added some features to aid in specifying where data and columns ended, and to show all the rows if that was desired.
When deciding to add new features, there is always a tension between the desire to add more functionality and the danger of cluttering up the interface. We needed to feel confident that a feature is going to reach a wide enough audience to warrant its inclusion. JMP 13 adds an “Advanced Options” panel. This panel contains options that will be appealing to a smaller audience. To avoid cluttering the Excel Import Wizard dialog, the options are not shown be default and are made available only on the second pane of the Wizard.
Clicking the disclosure icon reveals the options. If you change one of the options, it will become the default operation and the Advanced Options pane will be opened when you invoke the dialog. The options are:
The Column Name Separator String is involved when you have more than one row of data being used for column headers. The Wizard takes the data from the cells involved and separates them with a hyphen. From the Wizard preview window, this might look like:
You can specify which character you would like to separate the items with the Column Name Separator String option. If you just want a space in between, you can enter a single space in the edit box. This would produce the result below:
You can also remove the character entirely by using the backspace. It is also possible to enter multiple characters for the separator. As with all Wizard options, when you generate a JMP table the Source script within the table will retain the options that you specified in creating the table.
I’ll discuss the Multiple Series Stack option at the end.
Replicate Headers in Spanned Rows is like the option that existed in prior versions for data. Excel worksheets can contain headers or data that span several rows. This might look like:
In this case, there are spanned headers and rows. JMP, by default, will make the header the text from the spanned rows. However, there are cases where you want the flexibility of replicating the column header, much like you can choose to replicate the data for each row within JMP. This will produce:
Now that JMP supports coloring cells with the data table, we can try to import those colors from Excel. On the Mac, only primary and secondary colors can be imported. Windows supports a broader range. Select the Import Cell Colors option to try this out. This is not the default, as cell coloring in JMP is not as frequently used as Excel, and the exact colors that are imported can be subtly different than Excel. The screenshot below shows an example with the option specified.
Now for the Multiple Series Stack option, which is a somewhat complex function used for a specific problem. The name derives from a similar, but not identical, function in the data table Stack.
Occasionally, data is organized in a way where there is a hierarchy of information in the column header. In the example below, some machines on a production line contain information on the number of parts produced in a given span, the number of lost parts due to defects, and the amount of waste material generated. This is fabricated data.
The initial version of the Wizard introduced “Treat Column Names as Hierarchies”. This would break out the column headers to create a different organization of the data that would be more useful for analysis. If we try that option with the data above, we get a result like:
We have successfully created a column that contains the machine information, with the following column containing the information from the sub-category.
However, it would be really nice in this case to break out the data by the machine number and the number of parts that were successfully produced. The Multiple Series Stack option will do this. By checking that box, the preview and resulting table will be organized as follows:
Since JMP doesn’t know the context of the initial header, it assigns the name “Label” to that column data. Now we have successfully broken out the data by the initial category, and the data in the sub-category is organized by column.
Please note that it is essential that the option for “Treat Multiple Column Names as Hierarchies” be selected before selecting “Multiple Series Stack”. If you don’t do this, the feature will not do anything.
All of the features in this blog post work on both the Windows and Mac versions of JMP 13. We hope that the additional options enhance your Excel import work.