cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
matthew5
Level II

Importing XML directly to data tables

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.

<FinalTest>

  <Lot lot_id="BLAH" tester_type="J750">

    <Die die_id="1" die_x="1" die_y="1">

      <Test test_name="TEST1" unit="A">

        <Pin pin_name="ABC" result="1234" />

        <Pin pin_name="DEF" result="2345" />

      </Test>

      <Test test_name="TEST2" unit="A">

        <Pin pin_name="XCV" result="414" />

        <Pin pin_name="VBN" result="1351" />

      </Test>

    </Die>

    <Die die_id="2" die_x="3" die_y="4">

      <Test test_name="TEST1" unit="A">

        <Pin pin_name="ABC" result="5433" />

        <Pin pin_name="DEF" result="4321" />

  </Test>

      <Test test_name="TEST2" unit="A">

        <Pin pin_name="XCV" result="6856" />

        <Pin pin_name="VBN" result="5345" />

      </Test>

  </Die>

  </Lot>

</FinalTest>

        

lot_idtester_typedie_iddie_xdie_ytest_nameunitpin_nameresult
BLAHJ750111TEST1AABC1234
BLAHJ750111TEST1ADEF2345
BLAHJ750111TEST2AXCV414
BLAHJ750111TEST2AVBN1351
BLAHJ750234TEST1AABC5433
BLAHJ750234TEST1ADEF4321
BLAHJ750234TEST2AXCV6856
BLAHJ750234TEST2AVBN5345
1 ACCEPTED SOLUTION

Accepted Solutions
stan_koprowski
Community Manager Community Manager

Re: Importing XML directly to data tables

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 = "\[

<FinalTest>

  <Lot lot_id="BLAH" tester_type="J750">

    <Die die_id="1" die_x="1" die_y="1">

      <Test test_name="TEST1" unit="A">

        <Pin pin_name="ABC" result="1234" />

        <Pin pin_name="DEF" result="2345" />

      </Test>

      <Test test_name="TEST2" unit="A">

        <Pin pin_name="XCV" result="414" />

        <Pin pin_name="VBN" result="1351" />

      </Test>

    </Die>

    <Die die_id="2" die_x="3" die_y="4">

      <Test test_name="TEST1" unit="A">

        <Pin pin_name="ABC" result="5433" />

        <Pin pin_name="DEF" result="4321" />

  </Test>

      <Test test_name="TEST2" unit="A">

        <Pin pin_name="XCV" result="6856" />

        <Pin pin_name="VBN" result="5345" />

      </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 );

  );

);

View solution in original post

10 REPLIES 10
stan_koprowski
Community Manager Community Manager

Re: Importing XML directly to data tables

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 = "\[

<FinalTest>

  <Lot lot_id="BLAH" tester_type="J750">

    <Die die_id="1" die_x="1" die_y="1">

      <Test test_name="TEST1" unit="A">

        <Pin pin_name="ABC" result="1234" />

        <Pin pin_name="DEF" result="2345" />

      </Test>

      <Test test_name="TEST2" unit="A">

        <Pin pin_name="XCV" result="414" />

        <Pin pin_name="VBN" result="1351" />

      </Test>

    </Die>

    <Die die_id="2" die_x="3" die_y="4">

      <Test test_name="TEST1" unit="A">

        <Pin pin_name="ABC" result="5433" />

        <Pin pin_name="DEF" result="4321" />

  </Test>

      <Test test_name="TEST2" unit="A">

        <Pin pin_name="XCV" result="6856" />

        <Pin pin_name="VBN" result="5345" />

      </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 );

  );

);

matthew5
Level II

Re: Importing XML directly to data tables

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.

stan_koprowski
Community Manager Community Manager

Re: Importing XML directly to data tables

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

matthew5
Level II

Re: Importing XML directly to data tables

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.

pmroz
Super User

Re: Importing XML directly to data tables

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.

stan_koprowski
Community Manager Community Manager

Re: Importing XML directly to data tables

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

robot
Level VI

Re: Importing XML directly to data tables

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

sfayer0
Level II

Re: Importing XML directly to data tables

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

Re: Importing XML directly to data tables

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.