Help With XML Parsing

Question

All, This is in connection to a different question on the forum (https://community.jmp.com/t5/Discussions/Can-JMP-Support-Excel-Formatting-through-JSL/m-p/86355#M38495) . I am providing a small subset of a really large XML file ( XML of a worksheet in Excel). I would like to see a good example on how to traverse the XML tree to get to a targeted element efficiently. Can someone provide a good example on what is the best way to use the Parse XML or a different function in JSL to get a handle to the value here and edit the value ? 0 0 I tried the following and got an empty table: Parse XML(xml, On Element("sheetData",Start Tag(New Table())), On Element( "col", End Tag( New Column( XML Attr( "row r = '1' ht ='24'" ), Set Values( Parse( XML Text() ) ) ) ) ) );

uday_guntupalli · Answer

@gzmorgan0, Thank you for a detailed response. Objective: Take a large Excel workbook with lot of formatting and edit the values in the sheet without changing the formatting. Options I have come across so far: * Use an R package (like XLConnect (too slow) or openxlsx (messes up formatting and slow) ) * Use JSL * Using JSL, the only viable option I have come across so far to preserve the formatting is based on Ian's suggestion i.e. to tap into the XML behind the Excel workbook which is what I am trying to do. * There may be a way to use XPath Query like so: XPath Query("\[1]","//v/text()"); It returns the right value in the cell, however then the question falls on how do you replace this value without performing a Global Replace. * Use VB script with Run Program as you suggested. Since, the XML behind sheet1 is so big (100,000 lines or more), I took a small snippet to provide an example to seek the community's help on how I can drill down the hierarchy efficiently. I was hoping to keep the entire solution within JSL and make it clean and efficient, but it looks like I have to rely on other programs if there are no other options to modify the XML. Do you think there are any other options that I am missing ? Is there a better way to handle this problem ?

gzmorgan0 · Answer

p.s. for the snippet of XML you had in your post, only cell A1 had a value. It was of type string and you would have to get its value from the file  "xl/sharedStrings.xml"

gzmorgan0 · Answer

@uday_guntupalli from your earlier post and this email string, I still am not sure what you are trying to do? Excel owns its format. I made a simple read from one xml related file and wrote to the file and saved the zipped file and when I opened it in Excel it said it was corrupted but when I selected the option to fix it, the Excel workbook contained my changed values. (Maybe I wrote to a formula cell??). Are you trying to use a large Excel file as a database to read and write? are you trying to change formatting? What is the specific task including scope (size, task, udate in place, add rows, delete rows, etc.). Below is a snippet from one XML file in a 3 sheet XLSX file craeted from Big Class.jmp, Iris.jmp, and Semiconductor Capability.jmp. This is the first sheet. Item to note teh dimension is A1:E41 since the first row is column names. Once you find the Tag sheetData, the data is organized by row and cell, not easy to replace a column. Also note the cells with strings have extra coding t =\!"s\!" denoting it is a string and the value is a number. If you need that string value you need to to lookup that string value in the file "xl/sharedStrings.xml". The reference numbering starts with 0. For this simple 3 sheet Excel file there are 490 unique strings, String 44 is "F" and 45 is "M" for gender. If you are interested in reading and writing XML there is a secion in JSL Companion 2nd Edition and several scripts and examples. But For what I "tink" you are trying to do, I wouldn't do it with XML. "xl/worksheets/sheet1.xml" " 0 1 2 3 4 5 12 44 59 95