Subscribe Bookmark RSS Feed

Importing XML directly to data tables

matthew5

Community Trekker

Joined:

Jun 16, 2014

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
Solution

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

  );

);

8 REPLIES
Solution

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

Community Trekker

Joined:

Jun 16, 2014

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

Joined:

Aug 8, 2012

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

Community Trekker

Joined:

Jun 16, 2014

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

Joined:

Jun 23, 2011

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

Joined:

Aug 8, 2012

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

Community Trekker

Joined:

Feb 27, 2012

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

sfayer0

Community Trekker

Joined:

Jul 6, 2016

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