Import Data

"John Doe" is interpreted as a single string. Most programs (including JMP) read a quotation mark and ignore other field delimiters until the second quotation occurs.

•

If you include Strip Quotes(1), "John Doe" is interpreted as John Doe (one string without quotation marks).

Note that many word processors have a “smart quotation marks” feature that automatically converts double quotation marks ( " ) into left and right curled quotation marks ( “ ” ). Smart quotation marks are interpreted literally as characters when the text file is imported, even when JMP strips double quotation marks.

End of Line(CR | LF | CRLF | Semicolon | Other)

Specifies the character or characters that separate rows. The choices are as follows:

•

CR for carriage returns (typical for text files created on Macintosh OS up to version 9)

•

LF for linefeeds (typical for UNIX and Macintosh OS X text files)

•

CRLF for both a carriage return followed by a linefeed (typical for Windows text files).

All three characters are line delimiters by default.

Use the Other option to use an additional character for the row separator, which you must specify in the EOLOther argument. JMP interprets either this character or the default character as a row separator.

Specifies the character or characters used to separate fields.

注意：

•

The default field delimiter is Tab.

•

Use the Other option to use a different character for the field separator, which you must specify in the EOFOther argument.

•

The Space option uses a single space as a delimiter.

•

The Spaces option uses two or more spaces.

EOFOther, EOLOther

Specifies the character or characters used to separate fields or rows. For example, EOLOther("*") indicates that an asterisk separates rows in the text file.

Labels | Table Contains Column Headers

Indicates whether the first line of the text file contains column names. This is a Boolean value. The default value is true.

Year Rule | Two Digit Year Rule

Specifies how to import two-digit year values. If the earliest date is 1979, specify "1970". If the earliest date is 2001, specify "20xx".

Column Names Start | Column Names Are on Line

Specifies the starting line for column names. The following example specifies that the column names in the text file start on line three.

Open(

"$SAMPLE_IMPORT_DATA/Animals_line3.txt",

Columns(

Column( "species", Character, "Nominal" ),

Column( "subject", Numeric, "Continuous", Format( "Best", 10 ) ),

Column( "miles", Numeric, "Continuous", Format( "Best", 10 ) ),

Column( "season", Character, "Nominal" )

Column Names Start( 3 )

);

Data Starts | Data Starts on Line

Specifies the starting line for data.

The following example specifies that the data in the text file start on line five.

Open(

"$SAMPLE_IMPORT_DATA/Bigclass_L.txt",

Columns(

Column( "name", Character, "Nominal" ),

Column( "age", Numeric, "Continuous", Format( "Best", 10 ) ),

Column( "sex", Character, "Nominal" ),

Column( "height", Numeric, "Continuous", Format( "Best", 10 ) ),

Column( "weight", Numeric, "Continuous", Format( "Best", 10 ) )

Data Starts( 5 )

);

Lines to Read

Specifies the number of lines to include in the data table. JMP starts counting after column names are read.

The following example includes only the first 10 lines in the data table.

Open(

"$SAMPLE_IMPORT_DATA/Bigclass_L.txt",

Columns(

Column( "name", Character, "Nominal"),

Column( "age", Numeric, "Continuous", Format( "Best", 10 ) ),

Column( "sex", Character, "Nominal" ),

Column( "height", Numeric, "Continuous", Format( "Best", 10 ) ),

Column( "weight", Numeric, "Continuous", Format( "Best", 10 ) )

Lines To Read( 10 )

);

Use Apostrophe as Quotation Mark

For data that are enclosed in apostrophes, this option treats apostrophes as quotation marks and omits them. For example, ’2010’ is imported as 2. This is a Boolean value. The default value is false.

This option is not recommended unless your data comes from a nonstandard source that places apostrophes around data fields rather than quotation marks.

Set Text Import Preferences

If you want to set preferences for importing text, it can be helpful to first see a list of all preferences. To do so, use the Show Preferences (All) function.

Then, copy and paste only the section you want into a Preferences function. For example, to specify import settings, write the following expression:

Preferences(

Import Settings(

End Of Field( Tab, Spaces, Comma )

)

);

Import Data from a Microsoft Excel File

When you open a Microsoft Excel workbook in JMP, the file is converted to a data table. JMP supports .xls, .xlsm, and .xlsx formats. See “导入 Microsoft Excel 文件” in the Using JMP book for details about Microsoft Excel support.

Excel Preferences

In the JMP preferences, settings in the General group can help determine how worksheets are imported:

•

Excel Open Method specifies how a Microsoft Excel file should be opened by default, when using a non-specific open statement.

‒

Use Excel Wizard opens the Excel Import Wizard to import the file. This is the default setting.

‒

Open All Sheets opens all worksheets in the Microsoft Excel file.

‒

Select Individual Worksheets prompts users to select one or more worksheets when they open the file.

•

Use Excel Labels as Headings determines whether text in the first row of the worksheet is converted to column headings in the data table.

By default, JMP takes the best guess. If names have been defined for all cells in the first row, the text in those cells is converted to column heading. Otherwise, columns are named Column 1, Column 2, and so on.

To override a preference, include the corresponding argument described later in this section.

注意：See Create Excel Workbooks for information about exporting data tables to an Excel workbook.

Open() Function

Using the Open() function without additional arguments to open an Excel file has different behaviors depending on the context:

•

If the Open() function is a direct part of the script, the Excel files open into data tables using your Excel preferences. The following example opens both worksheets into data tables without using the wizard:

Path = Convert File Path( "$SAMPLE_IMPORT_DATA/Team Results.xlsx", absolute );

dt = Open( Path );

注意：To use the Excel Wizard, you must specify the Excel Wizard option in the argument as in Open( "$SAMPLE_IMPORT_DATA/Team Results.xlsx", "Excel Wizard" );.

•

However, if the Open() function is part of a script that is run from clicking a button, the Preview window opens and requires the user to interact with it. Run the following example and click the button to see the Excel Import Wizard:

Path = Convert File Path( "$SAMPLE_IMPORT_DATA/Team Results.xlsx", absolute );

New Window( "button", Button Box( "Open", dt = Open( Path ) ) );

•

To prevent the button script from opening the Preview window and importing the Excel file directly, provide additional arguments to the Open() function. Run the example and click the button. Both worksheets are opened into data tables without using the Excel Import Wizard.

Path = Convert File Path( "$SAMPLE_IMPORT_DATA/Team Results.xlsx", absolute );

New Window( "button", Button Box( "Open", dt = Open( Path, Use for all sheets(1) ) ) );

Alternatively, you can set the Excel Open Method preference to open all sheets, as follows:

Preference(Excel Open Method( "Open All Sheets" ));

Path = Convert File Path( "$SAMPLE_IMPORT_DATA/Team Results.xlsx", absolute );

New Window( "button", Button Box( "Open", dt = Open( Path ) ) );

Open a Workbook in the Excel Import Wizard

The Excel Import Wizard shows a preview of the data and lets you modify the settings before importing the data. Specify "Excel Wizard" as the argument.

dt = Open( "$SAMPLE_IMPORT_DATA/Team Results.xlsx", "Excel Wizard" );

You can also customize settings such as the number of rows in the column headers.

dt = Open(

"$SAMPLE_IMPORT_DATA/Bigclass.xls",

Worksheets( "Bigclass" ),

Use for all sheets( 1 ),

Concatenate Worksheets( 0 ),

Create Concatenation Column( 0 ),

Worksheet Settings(

Has Column Headers( 1 ),

Number of Rows in Headers( 1 ),

Headers Start on Row( 1 ),

Data Starts on Row( 2 ),

Data Starts on Column( 1 ),

Data Ends on Row( 0 ),

Data Ends on Column( 0 ),

Replicated Spanned Rows( 1 ),

Replicated Spanned Headers( 0 ),

Suppress Hidden Rows( 1 ),

Suppress Hidden Columns( 1 ),

Suppress Empty Columns( 1 ),

Treat as Hierarchy( 0 ),

Multiple Series Stack( 0 ),

Import Cell Colors( 0 ),

Limit Column Detect( 0 ),

Column Separator String( "-" )

), "Excel Wizard"

);

When you click a custom button to open a workbook, the Excel Open Method preference applies. To force the worksheet to open directly, specify the Excel Open Method in your script:

Preference( Excel Open Method( "Open All Sheets" ) );

Import Specific Worksheets

Suppose that you want to import data from specific worksheets in your workbook. Specify those worksheets using the Worksheets argument. In the following example, the worksheet named small is imported into JMP.

Open( "C:\My Data\cars.xlsx", Worksheets( "small" ) );

Or specify the number of the worksheet, the third worksheet in the following example:

Open( "C:\My Data\cars.xlsx", Worksheets( "3" ) );

Import multiple or all worksheets by including the worksheet names in a list:

Open( "C:\My Data\cars.xlsx", Worksheets( {"small", "medium", "large"} ) );

Highlight a Specific Worksheet

The worksheets in a workbook appear in the upper right corner of the Excel Import Wizard. You can highlight a specific worksheet using the following expression:

Open(

"C:/Data/MultipleWorksheets.xlsx", Excel Wizard,

Worksheets( "Planets" ) // highlighted worksheet

);

Import SAS Data Sets

Open a SAS file as a data table without connecting to a SAS server.

sasxpt = Open( "$SAMPLE_IMPORT_DATA/carpoll.xpt" );

To convert the labels to column headings, include the Use Labels for Var Names argument.

sasdbf = Open( "$SAMPLE_IMPORT_DATA/Bigclass.sas7bdat", Use Labels for Var Names( 1 ));

.xpt and .stx file formats are also supported.

On Windows, you can also open SAS data sets from a SAS server. See “Connect to a SAS Metadata Server” on page 716 in the “Extending JMP” chapter for details.

Import Web Pages and Remote Files

You can import data from websites or from other computers. The data might be a JMP data table, a table defined in a web page, or another file type that JMP supports.

Open or Import Data from a Website

In the Open() command, specify the quoted URL to open a file from a website. You can open JMP data tables or other supported file types this way.

Open( "http://company1.com/Repairs.jmp" );

Open( "http://company1.com/My Data.txt", text); // specify text on Macintosh

Import a Web Page

A web page can include data in tabular format. Import the table as a JMP data table as follows:

Open( "http://company1.com", HTML Table( n ) );

n identifies which table you want to import. For example, to import the fourth table on the page, specify HTML Table(4). If you omit the value, only the first table on the page is imported.

JMP attempts to preserve the table header defined in a <th> HTML tag. The table header is converted to column headings in the data table. If the <th> tag is wrong or missing, use ColumnNames(n) to specify the nth row. By default, DataStarts(n) will be the next row, or you can specify the DataStarts row.

Import Images from a Web Page

You can import images on the web into a data table, using the Expression data type. The following example creates a new data table and adds two images to it.

dt = New Table( "test", New Column( "Image", Expression ) );

dt << Add Rows( 1 );

dt:Image = New Image( Open( "http://www.jmp.com/support/help/14/images/MosaicPlot.png" ) );

dt << Add Rows( 1 );

dt:Image = New Image( Open( "http://www.jmp.com/support/help/14/images/tTest_NoLoc.png" ) );

Import Images from a Directory

You can use Multiple File Import() and an Event Handler script to create a data table of thumbnail images that link to a directory of full-size pictures. This example sets the cell height so that it fits the height of the tallest thumbnail.

注意：Typically, the Multiple File Import option is for importing multiple text files. However, this example shows how to use the option to import images.

path = "$SAMPLE_IMAGES/"; // images are in this directory

{dtx} = Multiple File Import(

<<Set Folder( path ),

<<Set Name Filter( "*.jpg; " ),

<<Set Name Enable( 1 ), // enable the ability to filter by name,

// in this example, files with the .jpg extension

<<Set Add File Name Column( 1 ), // must be included but can be hidden

<<Set Add File Size Column( 1 ) // for Show Sizes at end

) << Import Data;

dtx:FileName << Hide; // hide the column that contains the image file name.

// The column is required for the image links to work, but you don’t need

// to see it.

maxheight = 0;

For Each Row( // recreate each image to minimize size of data table

x = Eval( dtx:Picture );

x << Scale( .1 ); // scale the image by 10%

m = x << Get Pixels; // compress the image by recreating it

dtx:Picture = New Image( m );

maxheight = Max( maxheight, (dtx:Picture[] << Get Size)[2] );

// width x height is returned, [2] gets the height

);

Eval( /* insert the value in the path variable into the Click handler. The path variable won’t be available later, but the path value is needed to concatenate with the file name column’s value to build a link to the image on the computer. */

Eval Expr(

dtx:picture << Set Property(

"Event Handler",

Event Handler( // Event Handler column property

Click( JSL Quote( Function( {thisTable, thisColumn, iRow},

Open( Expr( path )||Char( thisTable:FileName[ iRow ] ) ); ); ) ),

// insert links to images

Tip( JSL Quote( Function( {thisTable, thisColumn, iRow},"Open " || Char( thisTable:FileName[ iRow ] ) || " in your viewer."; ); ) ),

// tooltip for the Tip event handler is shorter than the the tooltip

// inserted for the Click event handler

Color( JSL Quote( Function( {thisTable, thisColumn, iRow}, 5; );) )

) // color of the link -- blue

)

);

dtx << Set Cell Height( maxheight + 2 );

// add two pixels to the maximum height

dtx << Save( "$TEMP/Sample Images.jmp" );

Show( File Size( "$TEMP/Sample Images.jmp" ), Col Sum( dtx:FileSize ) );

// save the file to show that the data table is much smaller than the

// original file

Import a File from a Shared Computer

JMP can import files stored on a shared computer, such as another computer or a network drive. The file path can be absolute or relative. The following examples show how to open files from a shared computer named Data. If you plan to share the script, it’s safer to use a relative path to the computer, not a path to the mapped drive.

Open( "\\Data\Repairs.jmp" );

Open( "\\Data\My Data.txt" );

Import JSON Data

JSON (JavaScript Object Notation) is a data interchange format that is based on JavaScript. It shares many of the same principles as languages based on C and is easy to read and write. Because of these characteristics, JSON is highly portable and can be imported into JMP.

Representations of JSON in JSL
JSON	JSL
array	list (sequence of values)
object	associative array (set of named values)
number	number
string	string
true	1.0
false	0.0
null	. (missing value)

JSON consists of name-value pairs in an associative array. For example, here is JSON code that shows name-value pairs:

"[ { \!"key1\!": \!"a\!", \!"key2\!": \!"b\!"}, {\!"key1\!": \!"c\!", \!"key2\!": \!"d\!" }, {\!"key2\!": \!"f\!", \!"key1\!":\!"e\!", \!"key3\!": \!"g\!"}]";

Each list is imported as a column in the data table. “key1” is the name of the first column. The first value in the column is “a”. The second value is “c”, and the third value is “e”.

Use JSON To Data Table() to convert the JSON into a data table.

js = "[ { \!"key1\!": \!"a\!", \!"key2\!": \!"b\!"}, {\!"key1\!": \!"c\!", \!"key2\!": \!"d\!" }, {\!"key2\!": \!"f\!", \!"key1\!":\!"e\!", \!"key3\!": \!"g\!"}]";

d = JSON To Data Table( js ); // convert JSON to a data table

d << New Data View; // make a view of the data table

图 9.1 Imported JSON Data

You can also use JSON To List() to convert a string containing JSON into a list.

list = JSON To List(

"[ { \!"name\!": \!"KATIE\!", \!"age\!": 12, \!"sex\!": \!"F\!", \!"height\!": 59, \!"weight\!": 95 }, { \!"name\!": \!"LOUISE\!", \!"age\!": 12, \!"sex\!": \!"F\!", \!"height\!": 61, \!"weight\!": 123 }, { \!"name\!": \!"JANE\!", \!"age\!": 12, \!"sex\!": \!"F\!", \!"height\!": 55, \!"weight\!": 74 } ]"

);

Show( list );

// returns list = {{{"name", "KATIE"}, {"age", 12}, {"sex", "F"}, {"height", 59}, {"weight", 95}}, ...}}};

Exporting JSL to JSON

As JSON Expr() converts an associative array to a JSON string. You can then export the data into a JSON document.

list_of_associative_arrays = {["key1" => "a", "key2" => "b"], ["key1" => "c",

"key2" => "d"], ["key2" => "f", "key1" => "e", "key3" => "g"]};

jx = As JSON Expr( list_of_associative_arrays );

/* returns

"[{\!"key1\!":\!"a\!",\!"key2\!":\!"b\!"}..."[{\!"key1\!":\!"a\!",\!"key2\!":

\!"b\!"}

注意：To import the data table as invisible, include the argument Invisible(1) in JSON To Data Table() and omit New Data View.

Import HDF5 Files

Hierarchical Data Format, Version 5 (HDF5) is a portable file format for storing data. An HDF5 file consists of groups and datasets. When you import the file, JMP opens a group to present the names of the inner datasets.

JMP handles only tables with numeric (integer, float, double) and string types, and compound files with three or fewer dimensions that contain only simple types.

You can import up to 1,000,000 columns and an unbounded number of rows.

The syntax is as follows:

Open( "filename.h5", {"list_of", "dataset_names"});

Errors are written to the log when invalid data set names are passed.

Import ESRI Shapefiles

An ESRI shapefile is a geospatial vector data format used to create maps. JMP imports shapefiles as data tables. A .shp shapefile consists of coordinates for each shape. A .dbf shapefile includes values that refer to regions. To create maps in JMP, you modify the structure of the data and save the files with specific suffixes.

The following example imports a .shp file and saves it with the -XY suffix.

dt = Open( "$SAMPLE_IMPORT_DATA/Parishes.shp",

:X << Format( "Longitude DDD", 14, 4 );

:Y << Format( "Latitude DDD", 14, 4 ) );

dt << Save( "c:/Parishes-XY.jmp" );

Save the .dbf file with the -Name suffix.

dt = Open( "$SAMPLE_IMPORT_DATA/Parishes.dbf" );

dt << Save( "c:/Parishes-Name.jmp" );

Restructuring the data requires several steps, including adding a Map Role column property to names in the -Name.jmp file. For details, see “地图角色” in the Essential Graphing book.

Import a Database

Open Database() opens a database using Open Database Connectivity (ODBC) and extracts data into a JMP data table. See the “Database Access” on page 711 in the “Extending JMP” chapter for more information.

JMP also converts DataBase Files (.dbf) files to data table format.

sasdbf = Open( "$SAMPLE_IMPORT_DATA/Bigclass.dbf",

Use Labels for Var Names( 1 )

);