topic Re: Importing XML directly to data tables in Discussions

Importing XML directly to data tables

matthew5 — Mon, 16 Jun 2014 18:10:09 GMT

Hello,

I want to import a data set from (well formed) XML directly into a data table. This is something that Excel can do so I'm sure JMP can! The XML and the final data tables look like the examples below; there are a set of tests with results for individual pins, associated with a die, all part of an overall lot, with associated attributes such as tester or x,y coordinates (it's based on semiconductor test data, as you may have guessed!). The dataset could potentially be very long.

I know there is a Parse XML JSL function but I can't get my head around how to implement it in this situation. Any suggestions gratefully received!

Thanks, Matthew.

</Test>

</Test>

</Die>

</Test>

</Test>

</Die>

</Lot>

</FinalTest>

lot_id	tester_type	die_id	die_x	die_y	test_name	unit	pin_name	result
BLAH	J750	1	1	1	TEST1	A	ABC	1234
BLAH	J750	1	1	1	TEST1	A	DEF	2345
BLAH	J750	1	1	1	TEST2	A	XCV	414
BLAH	J750	1	1	1	TEST2	A	VBN	1351
BLAH	J750	2	3	4	TEST1	A	ABC	5433
BLAH	J750	2	3	4	TEST1	A	DEF	4321
BLAH	J750	2	3	4	TEST2	A	XCV	6856
BLAH	J750	2	3	4	TEST2	A	VBN	5345

Re: Importing XML directly to data tables

stan_koprowski — Wed, 18 Jun 2014 03:29:56 GMT

Hi Matt,

JMP currently does not have a direct way to import XML files using the File open dialog.

However, you could use the JSL to accomplish this task.

See below for an example of one way to do this.

Perhaps someone else can simplify the script to be more concise.

Cheers,

Stan

// assign xml text

xmlTxtFile = "\[

</Test>

</Test>

</Die>

</Test>

</Test>

</Die>

</Lot>

</FinalTest> ]\"

;

// Parse the XML

Parse XML( xmlTxtFile,

// Create a new table

On Element( "FinalTest",

StartTag(

dt = New Table( "Wafer Table",

// Create new columns

New Column ( "Lot ID", Character),

New Column ( "Tester Type", Character),

New Column ( "Die ID", Numeric),

New Column ( "Die X", Numeric),

New Column ( "Die Y", Numeric),

New Column ( "Test Name", Character),

New Column ( "Unit Name", Character),

New Column ( "Pin Name", Character),

New Column ( "Pin Result", Numeric),

);

row=1;

) // End Start Tag, FinalTest On Element

// Get next element

On Element( "Lot",

StartTag(

If(row>0, dt<< Add Rows(1));

column(dt, "Lot ID")[row()] = XML Attr( "lot_id" );

column(dt, "Tester Type")[row()] = XML Attr( "tester_type" );

EndTag()

On Element( "Die",

StartTag(

column(dt, "Die ID")[row()] = Num(XML Attr( "die_id" ));

column(dt, "Die X")[row()] = Num(XML Attr( "die_x" ));

column(dt, "Die Y")[row()] = Num(XML Attr( "die_y" ));

)

On Element( "Test",

StartTag(

column(dt, "Test Name")[row()] = XML Attr( "test_name" );

column(dt, "Unit Name")[row()] = XML Attr( "unit" );

)

On Element( "Pin",

StartTag(

column(dt, "Pin Name")[row()] = XML Attr( "pin_name" );

column(dt, "Pin Result")[row()] = Num(XML Attr( "result" ));

EndTag(

If(row>0, dt<< Add Rows(1));

)

);

// delete extra row

nr = NRow(dt);

dt << Go To Row(nr);

dt<<Get Selected();

dt<<Delete Rows;

// Fill down missing row values

For Each Row(

cr = row();

If( cr != 1 ,

If(:Lot ID == "", :Lot ID = Lag( :Lot ID, 1 ), :Lot ID = :Lot ID );

If(:Tester Type == "", :Tester Type = Lag( :Tester Type, 1 ), :Tester Type = :Tester Type );

If(IsMissing(:Die ID) == 1, :Die ID = Lag( :Die ID, 1 ), :Die ID = :Die ID );

If(IsMissing(:Die X) == 1,:Die X = Lag( :Die X, 1 ), :Die X = :Die X ) ;

If(IsMissing(:Die Y) == 1, :Die Y = Lag( :Die Y, 1 ), :Die Y = :Die Y );

If(:Test Name == "", :Test Name = Lag( :Test Name, 1 ), :Test Name = :Test Name );

If(:Unit Name == "", :Unit Name = Lag( :Unit Name, 1 ), :Unit Name = :Unit Name );

);

Re: Importing XML directly to data tables

matthew5 — Wed, 18 Jun 2014 08:30:01 GMT

Hi Stan,

that works well, thank you very much! I will have a play with it to fully understand how the JSL works.

In case anyone from JMP is reading this: the option to import a well formed XML data file directly into a data table (without knowing the structure or depth of the data) would be a useful addition. Is this feasible or possible?

Thanks,

Matthew.

Re: Importing XML directly to data tables

pmroz — Wed, 18 Jun 2014 20:28:30 GMT

Stan that's a really cool example of using PARSE XML. Is it documented somewhere? Couldn't find much in the JMP 11 on-line help or scripting books.

Re: Importing XML directly to data tables

stan_koprowski — Wed, 18 Jun 2014 23:22:41 GMT

Hi Peter,

Thank you. I'm glad you found the example helpful. I applied my implementation using the examples from the scripting index. I searched "XML" in the scripting index and it returned several results.

I will submit a request to have the documentation updated to have some additional examples.

Cheers,

Stan

Re: Importing XML directly to data tables

stan_koprowski — Wed, 18 Jun 2014 23:26:19 GMT

Matt,

Great! I'm glad you found it useful. Our support team is fantastic and are always ready to assist customers with any questions or comments you might have.

The best way to have features added to a future release of JMP is to submit the request directly to support@jmp.com.

Cheers,

Stan

Re: Importing XML directly to data tables

matthew5 — Fri, 20 Jun 2014 08:59:19 GMT

Hi Stan,

I didn't realise you were a JMP man, but now I see the little JMP icon under your name so that explains it :) Thanks again for your help and I will indeed suggest this as a future enhancement.

Regards,

Matthew.

Re: Importing XML directly to data tables

robot — Sun, 15 May 2016 16:40:20 GMT

Great example. I found this much easier to understand than either the Scripting Guide or Scripting Index. Thanks!

Re: Importing XML directly to data tables

sfayer0 — Mon, 11 Sep 2017 23:00:51 GMT

Load Text File() is an alternative to Open() for XML and other text files.

Re: Importing XML directly to data tables

Audrey_Shull — Fri, 11 Jan 2019 20:26:07 GMT

For those interested in XML (it seems to pop up more and more these days), i can report that an XML Import Wizard is under active development in JMP 15. It allows point-and-click selection to make sure that JMP understands the proper format of your XML. JMP 15 will ship in Fall 2019.

Re: Importing XML directly to data tables

archu — Wed, 20 Mar 2019 06:55:30 GMT

Hi I got an error when i wrote this

Parse XML ( QasFile,

On Element("IndicatorGroups",

Start Tag (

dt = New table("TestJMPTable");

dt << New column ("N", Numeric);

dt << New column ("GraphicsFile",Character);

dt << New column("Description", Character) ;

));

row = 1;

on Element ("N",

Starttag(

if(row>0,dt<< Addrows(1));

Column(dt,"N")[row()] = XML Attr("N");

)),

EndTag()

);