Preview and Import the Microsoft Excel Data

Before you import a worksheet, open the spreadsheet in Excel and decide how you want the data to be structured in the final data table. For example, you need to know whether the worksheet includes hidden or merged cells. In the wizard, you can then exclude hidden columns or rows.

To import a Microsoft Excel file that contains several worksheets, follow these steps:

Open the worksheet in Microsoft Excel.

For the figures in this example, we used the Team Results.xlsx file located in the JMP Samples/Import Data folder. The file has the following characteristics:

–

the data begin on row 4, column 2 and end on row 9, column 5

–

two worksheets

–

the second worksheet has two sets of merged cells

–

no hidden rows or columns

Figure 2.2 Team Results.xlsx Worksheet

To open an Excel file in JMP, select File > Open.

The Open Data File window appears.

Select the Excel file.

The worksheet opens in the Excel Import Wizard, where a preview of the data appears along with import options (Figure 2.3).

Figure 2.3 Example Initial Data Preview

Note the following characteristics in the Data Preview:

–

Both worksheets are selected for import in the upper right corner.

–

The first column has been automatically been removed.

–

Text from the first row of the worksheet appears as the column headings. However, you want the text in row 3 of the worksheet to be used as the column headings.

–

The first data row is empty.

Note: JMP remembers your previous changes each time you import a worksheet, even after closing and reopening JMP. This feature is helpful when you want to reimport the same worksheet several times and experiment with options. To clear those changes when you import a different worksheet, click Restore Default Settings.

Type 3 for Column headers start on row

Type 4 for Data starts on row.

Select Ungrouped Team Results in the Worksheets pane.

Only this worksheet will be imported.

Deselect Use for all worksheets.

These settings apply only to Ungrouped Team Results.

Figure 2.4 shows your changes.

Figure 2.4 Selecting the Column Header Row

See Individual Worksheet Settings for details about all options.

Click Next to configure other import settings.

The window displays additional import settings.

For Data ends with row, type 9.

10.

For Data ends with column, type 5.

Figure 2.5 shows your changes.

Figure 2.5 Specifying the Last Column

See Additional Individual Worksheet Settings for details about all options.

11.

Click Import to convert the worksheet as you specified (Figure 2.6).

Figure 2.6 Final Data Table

The following sections describe options in the Excel Import Wizard.

Individual Worksheet Settings

Worksheet contains column headers

Select if the worksheet contains rows with column headers.

Column headers start on row

Indicates which row the column headers begin on in the worksheet. Click the up arrow

until the headers begin on the correct row, or enter the row number and press Enter.

Number of rows with column headers

Indicates whether the worksheet has multiple rows as column headers. Click the up arrow

until the header rows appear correctly, or enter the number of rows and press Enter.

Data starts on row

Indicates which row the data start on in the worksheet.

Data starts on column

Indicates which column the data start on in the worksheet.

Concatenate worksheets and try to match columns

Merges all worksheets into one data table. JMP matches columns that have the same header.

Create column with worksheet name when concatenating

Adds a new Source Table column that lists the worksheet name for each imported table. This option is available after you select the preceding concatenate option.

Use for all worksheets

Applies the current import settings to all worksheets that are selected in the upper right corner.

Additional Individual Worksheet Settings

Treat multiple column header lines as hierarchies

Indicates that the worksheet contains multiple rows as column headers and you want these headers to be hierarchies. This option is only for stackable data.

Replicate data in spanned rows

Indicates cells are merged in the worksheet across rows. JMP unspan the cells and copy the cell contents into all of the resulting cells. The option is selected by default.

If you deselect Replicate data in spanned rows, JMP unspans the cells and copies the cell contents into the topmost cell. The remaining unspanned cells are left empty.

Suppress hidden rows

Prevents hidden rows from appearing in the data table.

Suppress hidden columns

Prevents hidden columns from appearing in the data table.

Suppress empty columns

Indicates whether an empty column that has a column header is imported. Deselect the option to import the column.

Data ends with row

Indicates the last row in the worksheet that contains data.

Data ends with column

Indicates the last column in the worksheet that contains data.

Advanced Options

Column Name Separator String

Indicates the separator between each word in a column heading if the headings were originally in different rows. Specify the number of rows with column headers on the first Excel Import Wizard window. Then enter a character or space in the Column Name Separator String box. The default string, a hyphen, results in a column heading such as “First-Second-Third”.

Multiple Series Stack

Divides subcategories into separate columns in a worksheet with hierarchical headings. You must also select Treat multiple column header lines as hierarchies. The main category is imported as the Label column.

Replicate headers in spanned rows

Repeats the header text in each cell for rows that are spanned in the worksheet. For example, the State column heading shown in Figure 2.7 was in a merged cell in the worksheet. On the left, the heading is replicated. Deselect the option to avoid repeating the heading as shown on the right.

Figure 2.7 Replicated Headers in a Microsoft Excel File

Import cell colors

Applies the cell coloring from the worksheet to the data table. On the Mac, only primary and secondary colors can be reliably imported.

Limit column type detection

Scans a maximum of 100 rows to determine the column type. Select this option to speed up the import of large worksheets.

Tips:

•

JMP remembers your previous changes each time you import a worksheet, even after closing and reopening JMP. This feature is very helpful when you want to reimport the same worksheet several times and experiment with options. To clear those changes when you import a different worksheet, click Restore Default Settings.

•

Your import settings are saved in a data table script named Source. To reimport the worksheet using the same settings, run the script. The script includes the path to the worksheet, so make sure that other users have access to that location.

•

To speed up the data preview in large worksheets, deselect Update settings on any change on the first wizard window. Modify the settings and then click Update now to refresh the data preview.

•

To view all rows in the Data Preview pane, select Show all rows. The preview might be slightly delayed depending on the size of the spreadsheet.

•

You can combine two worksheets from the same workbook into one data table. The column names are matched on import, so the order of the columns is irrelevant.

Importing a Microsoft Excel File with Hierarchical Headings

In an Excel worksheet, multiple header rows can have an implied hierarchy; the second header row contains data that are categories of the first header row. Figure 2.8 shows an example. In the worksheet at the top, the seasons “Winter” and “Spring” are in spanned cells above the months within those seasons. In the JMP data table, you want the seasons in one column and their corresponding months in another column.

Figure 2.8 The Original Data in Excel and Final Data in JMP

To import the worksheets and maintain multiple column hierarchies, follow these steps.

In JMP, select File > Open.

In the Open Data File window, select Texas Precipitation.xlsx, located in the JMP Samples/Import Data folder, and then click Open.

The worksheet opens in the Excel Import Wizard, where a preview of the data appears along with import options.

Figure 2.9 Excel Import Wizard Preview

Figure 2.9 shows the default settings for this worksheet:

1	All worksheets are selected for import.
2	Each season is split over several columns.
3	Data from the second heading row of the worksheet appear in the first row.
4	The empty rows at the top have been removed.

Under Preview Pane Refresh, make sure that Update settings on any change is selected.

This means that the Data Preview automatically refreshes when you make changes.

Next to Number of rows with column headers, click the up arrow once.

Notice that Data starts on row automatically updates to 3.

Figure 2.10 shows the updated settings.

Figure 2.10 Updated Settings on Page One

Click Next.

Select Treat multiple column header lines as hierarchies.

Each season and month in the header rows of the worksheet will become categories in the data table.

Next to Data ends with row, type 6 and press Enter.

After the first three empty rows are removed from the worksheet, the data end with row 6.

Tip: Instead of you typing the end row number, JMP can calculate the row for you. In the Data Preview pane, select row 4. Click the plus sign

next to Data ends with row.

Figure 2.11 shows the updated settings.

Figure 2.11 Updated Settings on Page Two

Notice that Replicate data in spanned rows is selected by default. JMP unmerges data that were merged in the worksheet and copies the cell contents as separate categories. “TX” was originally in a merged cell. In JMP, it will be copied into separate cells.

Click Import.

The four worksheets open as separate data tables. The data tables contain a Source script, which you can run to import the data into new data tables using the same import settings (Figure 2.12). Your import settings are also saved the next time you open the worksheet in the Excel Wizard in any JMP session.

Figure 2.12 The Final Data