topic Re: how to create a script to import xls to jmp in Discussions

how to create a script to import xls to jmp

alexching — Sat, 10 Jun 2023 23:15:35 GMT

i have a problem about import xls file to jmp using script

i want to import this file using row "7" as header and add new column "Lot id" fill data as B3 cell data (B6C141-02B5) into new column

how can i do this

thank you

Re: how to create a script to import xls to jmp

txnelson — Thu, 02 Jul 2020 10:01:34 GMT

Here is the script to input your data

I used the JMP Excel Wizard to open the file interactively and then copied the Source script that JMP created to place into the script. The script reads in the file twice. First to read the data, and then the second time to read in the Lot ID. I then just added the code to let you select the Excel file to import, and the JSL required to create the new Lot ID column.

names default to here(1);

path = Pick File();
dt = Open(
	path,
	Worksheets( "Sheet1" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 7 ),
		Headers Start on Row( 6 ),
		Data Starts on Row( 13 ),
		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( "-" )
	)
);

dtLotID = Open(
	path,
	Worksheets( "Sheet1" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 0 ),
		Number of Rows in Headers( 7 ),
		Headers Start on Row( 6 ),
		Data Starts on Row( 3 ),
		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( "-" )
	)
);

lotID = dtLotID:Column 2[1];
close(dtLotID, nosave );

dt << New Column("Lot ID", Character, set each value(lotID));

Re: how to create a script to import xls to jmp

ian_jmp — Thu, 02 Jul 2020 10:52:33 GMT

I think you will need to know a little JSL, and this should get you started. The 'best' approach will be determined by what you can (and can't) assume about the structure of the data beyond the example you have given.

This code is not at all robust, and assumes your file is in the 'downloads' folder. If you look at 'Help > Books > Scripting Guide' you can probably figure out what it's doing (depending on how much coding experience you have).

NamesDefaultToHere(1);

dt = Open("$DOWNLOADS/test_import.xlsx");

lid = Column(dt, 2)[2];
colNames = {};
InsertInto(colNames, Column(dt, 1)[11]);
InsertInto(colNames, Column(dt, 2)[11]);
InsertInto(colNames, Column(dt, 3)[7]);
InsertInto(colNames, Column(dt, 4)[7]);
InsertInto(colNames, Column(dt, 5)[7]);
InsertInto(colNames, Column(dt, 6)[7]);
InsertInto(colNames, Column(dt, 1)[2]);

dt << deleteRows(1::11);
for(c=1, c<=NItems(colNames), c++, Column(dt, c) << setName(colNames[c]));
Column(dt, colNames[NItems(colNames)]) << setValues(Repeat(lid, NRows(dt)));
dt << setName("Data for lot "||lid);

Re: how to create a script to import xls to jmp

martindemel — Fri, 03 Jul 2020 12:56:40 GMT

As both txNelson and Ian mentioned, your data table does not have a really good format for a statistical analysis, however this is unfortunately quite often the case in industry, especially when spreadsheet tools are mainly used. Statistical data should be in columns and rows in a rectangular form whenever possible. Otherwise you need to specify each cell for the calculation instead of using vectors and matrices, which would be much more efficient. That's why txNelson imported the sheet twice and extracted the information, as Ian did in a similar fasion.

Some time ago I had a discovery presentation about tips for importing data from Excel I believe still holds: Getting-around-common-pitfalls-working-with-Excel-data-in-JMP (2016 JMP Discovery Summit Europe)

As well wrote some blogs about Importing from Excel. Different cases than yours but maybe useful for other situations:

Blogs from Martin about Excel and JMP