Import Microsoft Excel Files

Microsoft Excel files open in the Excel Import Wizard by default. The wizard shows a preview of the data. You can then modify the settings before importing the data. For example, you might indicate which row the data begin on and whether the worksheet contains column headers or hidden rows or columns. Microsoft Excel .xls, .xlsm (on Windows), and .xlsx file formats are supported.

For information about opening a Microsoft Excel file outside the wizard, see Import a Microsoft Excel File Directly.

Notes:

•	Password-protected Microsoft Excel .xlsx files cannot be opened in JMP.

•

Between Windows and Macintosh, the number of digits after a decimal point and the date format of imported data might differ. For example, “10/25/2012” might be formatted as “25Oct2012” on Macintosh. Columns might be imported as character columns on Macintosh but not on Windows. For more details about formatting dates, see Pasting Dates from Another Application.

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:

1.	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

Team Results.xlsx Worksheet

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

The Open Data File window appears.

3.	Select the Excel file.

The worksheet opens in the Excel Import Wizard, where a preview of the data appears along with import options (Example Initial Data Preview).

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.

4.	Type 3 for Column headers start on row

5.	Type 4 for Data starts on row.

6.	Select Ungrouped Team Results in the Worksheets pane.

Only this worksheet will be imported.

7.	Deselect Use for all worksheets.

These settings apply only to Ungrouped Team Results.

Selecting the Column Header Row shows your changes.

Selecting the Column Header Row

See Individual Worksheet Settings for details about all options.

8.	Click Next to configure other import settings.

The window displays additional import settings.

9.	For Data ends with row, type 9.

10.	For Data ends with column, type 5.

Specifying the Last Column shows your changes.

Specifying the Last Column

See Additional Individual Worksheet Settings for details about all options.

11.	Click Import to convert the worksheet as you specified (Final Data Table).

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

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. The option is selected by default.

Suppress hidden columns

Prevents hidden columns from appearing in the data table. The option is selected by default.

Suppress empty columns

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

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.

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.

•	As you experiment with settings for a large worksheet, the data preview might be slightly delayed. To speed up the preview, 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. The Original Data in Excel and Final Data in JMP 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.

The Original Data in Excel and Final Data in JMP

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

1.	In JMP, select File > Open.

2.	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.

Excel Import Wizard Preview

Excel Import Wizard Preview 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.

3.	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.

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

Notice that Data starts on row automatically updates to 3.

Updated Settings on Page One shows the updated settings.

Updated Settings on Page One

5.	Click Next.

6.	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.

7.	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 9, which is where the data ends in the spreadsheet before the first three empty rows are removed. Click the plus sign

next to Data ends with row.

Updated Settings on Page Two shows the updated settings.

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.

8.	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 (The Final Data). Your import settings are also saved the next time you open the worksheet in the Excel Import Wizard in any JMP session.

The Final Data

Import a Microsoft Excel File Directly

Microsoft Excel files open in the Excel Import Wizard by default. This option is helpful when the structure of data in the worksheet is irregular. For example, you might want to exclude hidden columns or convert text in the third row to column headings.

Instead of opening spreadsheets in the Excel Import Wizard, you can select File > Open to open the file. By default, JMP detects whether the first row contains labels and converts them to column headings. You can change this setting in the General preferences. The Excel Open Method preferences are in File > Preferences > General (Windows) and JMP > Preferences > General (Macintosh). From the Use Excel Labels for Headings list, select Always or Never.

To set the Excel Open Method preference

To always open Microsoft Excel files outside the wizard, change the Excel Open Method preference. Choose to open all worksheets at once or select them from a list.

To open a Microsoft Excel file (Windows)

1.	After you set the Excel Open Method as described above, select File > Open.

2.	Select the Excel Files file type, select the file, or enter the URL.

3.	To convert text in the first row to column headings, select Always next to Always enforce Excel Row 1 as labels.

4.	To import all worksheets, click Open.

To select the worksheets that you want to open, click the Open button arrow, and then select Open Selected Worksheets. Select one or more worksheets and click OK.

To open a Microsoft Excel file (Macintosh)

1.	Select File > Open and select the file.

2.	(.xls only) To convert text in the first row to column headings, select Use Excel Labels as Headings.

3.	(.xls only) To open specific worksheets, select Select Individual Excel Worksheets.

4.	Click Open.

If you chose to open specific worksheets, select those worksheets from the list, and then click OK. You can also click Select All if you change your mind and want to import all worksheets.

If you selected an .xlsx file, a preview of the data appears in the Excel Wizard. See Preview and Import the Microsoft Excel Data for details.