Importing XML directly to data tables<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />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.
| 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 tablesHi 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()
);
Re: Importing XML directly to data tablesFor 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 tablesLoad Text File() is an alternative to Open() for XML and other text files.Re: Importing XML directly to data tablesGreat example. I found this much easier to understand than either the Scripting Guide or Scripting Index. Thanks!
Re: Importing XML directly to data tablesHi 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 tablesMatt,
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 tablesHi 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 tablesStan 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 tablesHi 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 tablesHi 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 = "\[
]\"
;
// 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 );
);
);
