Subscribe Bookmark RSS Feed
briancorcoran

Joined:

Jun 23, 2011

Excel Import Wizard in JMP 12

The Excel Import Wizard was introduced in JMP 11 for Windows. The goal of the Wizard was to simplify the task of importing data that often did not follow the rule of “line 1 contains headers, line 2 starts the data.” The feedback we've received suggests that we were successful.

This blog post does not detail how to use the Wizard. The documentation is a good resource for this, as is Chuck Pirrello’s video “Working with Excel Data.” Rather, I will aim to give you an overview of the changes to the Excel Import Wizard in JMP 12 and show you how easy it is to import your Excel data into JMP.

First, the wizard is available on the Mac in JMP 12. We apologize for the delay with this, but please know that all of the functionality that you have had in the Windows release of JMP is now available on the Mac. All of the new features that I describe in this post are in the Mac version of the wizard.  Potentially of even greater significance for Mac users is that the wizard allows you to open .XLSX formatted files. XLSX is the newer Excel format introduced with Excel 2007. Prior versions of JMP required Mac users to open .XLSX files via an ODBC driver, which could be cumbersome.

The Mac support is the biggest news in the latest version of JMP. Because of user feedback, we have added a few more features. The first is the “Show all rows” support. This aims to address two requests. Some users want to see all of the data in large tables, not just the first 100 rows. By checking this box, you will now get a preview of the entire worksheet. This also makes another important adjustment that you can’t see. It uses all of the data in the column to guess the data type for the column. Users were running into cases where they had a large number of rows, and the last few rows contained some character data. Since JMP would use the first 100 rows to guess the data type, it could guess a numeric type. If row 205 contained character data, it could be lost when the worksheet was imported because the character data could not be represented in a numeric cell. By checking the “Show all rows” option, character data that is discovered far down in the column will cause the column to be typed as Character to preserve all of the data. Please know that if you select this option, large tables could take a considerable time to load.

ShowAllRows

The green plus signs that you see next to the settings labels are new in JMP 12. If you first select a row or column, and then press the green plus button next to the desired setting, JMP calculates the correct row or column number for the setting. This can be important if have hidden rows or columns, or where the data does not start on the second row but you are no longer viewing the empty rows prior to the start of the data.

An example of using the green plus is the data below. There are observations and text below the last country, Kenya, that I don’t want to import into JMP. I can select the row that highlights Kenya.

DataEndsOne

JMP will figure out that the row in question is in fact row 40, and it will fill in the field and update the display.

DataEndsTwo

Finally, the screenshot above also shows one final option. Sometimes workbooks contain columns that are empty, but the column header itself conveys important information. By unchecking the “Suppress empty columns” option, the empty column will be imported. By default, JMP tries to remove this type of column.

Note: A version of this blog post appeared first in Brian's JMP User Community blog, The Rest of the World.

Article Tags