A challenge for the big dogs! (xml related)

[drapdv]

Okay, so this is Toyota's site for their dealers, and I need to query it to get information about parts. I am working with one of the reps to take care of a fairly obnoxious issue and when this script is finished it will be distributed to other dealers. I am definitely not the right person to be doing this but I volunteered because I'm stupid so please help me

The information will need to go to Excel. The website requires username and password AND that you be at a franchised location so unfortunately no one will be able to try this for themselves. I haven't found a way to get around all the scripting and verifications so that I can query the database directly, so at this point I use their GUI interface and the results are returned on the next page. The results are shown in a table but the values are in an XML reference toward the bottom of the page.

The easiest solution I can see (although I am MORE than open to better ideas) is to pull the information from a ConsoleWrite or _IEBodyRead of some sort, save it to a text file, then figure a way to get Excel to import the fields I need.

So when the results page loads I have AutoIt run this portion of the script:

$oTables = _IETableGetCollection ($oframeContent)
    For $oTable In $oTables
        If $oTable.id = "tblList2" Then
            $oXMLtable = $oTable
            ExitLoop
        EndIf
    Next

ConsoleWrite("here: " & $oXMLtable.innerhtml & @CRLF)

Which returns this result for (3) part numbers (pared down to what I actually need and modified with carriage returns for readability - the original has no breaks after <list><row>):

<!-- *** End of Button Pane *** --><!-- Begin XML data islands and XSL filter definitions -->
<XML id=datList class=DataFunctions LoadPage="/Parts/PartInfo/PartInfoTransmitDetail.aspx">
<list><row><MANUF_CODE>TY</MANUF_CODE><PART_NBR>9008091088</PART_NBR><PART_DESC>BELT, V-RIBBED</PART_DESC><SOURCE_CODE>01</SOURCE_CODE><DEALER_PRC>491</DEALER_PRC><WHLSALE_PRC>702</WHLSALE_PRC><RETAIL_PRC>825</RETAIL_PRC><CORE_VALUE>0000000</CORE_VALUE><CLASS_CODE>F</CLASS_CODE><ACCESS_CODE>O</ACCESS_CODE><GT_FLAG></GT_FLAG><QUANT_INPACK>1</QUANT_INPACK><QUANTITY>1</QUANTITY><PARTSUB_FLAG>O</PARTSUB_FLAG><INV_SUB_FLAG>1</INV_SUB_FLAG><CONTROL_CODE>YN</CONTROL_CODE><SUPERSESSION></SUPERSESSION><AVAILABILITY>N</AVAILABILITY><MAX_ORD_QTY>0</MAX_ORD_QTY><STATUS_CODE></STATUS_CODE><MAC_CODE></MAC_CODE><STOP_SALES></STOP_SALES><REFERRAL_CODE></REFERRAL_CODE><DEPOT_LIST><row><DEPOT_CODE>07</DEPOT_CODE><DEPOT_NAME>LEXUS SOUTHERN</DEPOT_NAME><ON_HAND>0</ON_HAND><IN_TRANSIT>0</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>08</DEPOT_CODE><DEPOT_NAME>LEXUS DALLAS</DEPOT_NAME><ON_HAND>0</ON_HAND><IN_TRANSIT>0</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>11</DEPOT_CODE><DEPOT_NAME>LOS ANGELES</DEPOT_NAME><ON_HAND>0</ON_HAND><IN_TRANSIT>0</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>1</FACING_PDC></row><row><DEPOT_CODE>12</DEPOT_CODE><DEPOT_NAME>SAN FRANCISCO</DEPOT_NAME><ON_HAND>0</ON_HAND><IN_TRANSIT>0</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>13</DEPOT_CODE><DEPOT_NAME>PORTLAND</DEPOT_NAME><ON_HAND>0</ON_HAND><IN_TRANSIT>0</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>16</DEPOT_CODE><DEPOT_NAME>NEW YORK</DEPOT_NAME><ON_HAND>0</ON_HAND><IN_TRANSIT>0</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>17</DEPOT_CODE><DEPOT_NAME>BOSTON</DEPOT_NAME><ON_HAND>0</ON_HAND><IN_TRANSIT>0</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>18</DEPOT_CODE><DEPOT_NAME>BALTIMORE PDC</DEPOT_NAME><ON_HAND>0</ON_HAND><IN_TRANSIT>0</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>21</DEPOT_CODE><DEPOT_NAME>CHICAGO</DEPOT_NAME><ON_HAND>0</ON_HAND><IN_TRANSIT>0</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>22</DEPOT_CODE><DEPOT_NAME>CINCINNATI</DEPOT_NAME><ON_HAND>0</ON_HAND><IN_TRANSIT>0</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>23</DEPOT_CODE><DEPOT_NAME>KANSAS CITY</DEPOT_NAME><ON_HAND>0</ON_HAND><IN_TRANSIT>0</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>O</DEPOT_CODE><DEPOT_NAME>Parts Center - NAPCC</DEPOT_NAME><ON_HAND>0</ON_HAND><IN_TRANSIT>0</IN_TRANSIT><BACK_ORDER></BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>T</DEPOT_CODE><DEPOT_NAME>Parts Center - NAPCK</DEPOT_NAME><ON_HAND>0</ON_HAND><IN_TRANSIT>0</IN_TRANSIT><BACK_ORDER></BACK_ORDER><FACING_PDC>0</FACING_PDC></row></DEPOT_LIST><DEPOT_ON_HAND_TOTAL>0</DEPOT_ON_HAND_TOTAL><DEPOT_IN_TRANSIT_TOTAL>0</DEPOT_IN_TRANSIT_TOTAL><DEPOT_BACK_ORDER_TOTAL>0</DEPOT_BACK_ORDER_TOTAL><FormattedPartNo>90080-91088</FormattedPartNo><FormattedDealerPrc>$4.91</FormattedDealerPrc><FormattedWhlsalePrc>$7.02</FormattedWhlsalePrc><FormattedRetailPrc>$8.25</FormattedRetailPrc><FormattedCoreValue>$0.00</FormattedCoreValue></row>


<row><MANUF_CODE>TY</MANUF_CODE><PART_NBR>9008091184</PART_NBR><PART_DESC>PLUG, SPARK</PART_DESC><SOURCE_CODE>01</SOURCE_CODE><DEALER_PRC>622</DEALER_PRC><WHLSALE_PRC>881</WHLSALE_PRC><RETAIL_PRC>1037</RETAIL_PRC><CORE_VALUE>0000000</CORE_VALUE><CLASS_CODE>A</CLASS_CODE><ACCESS_CODE></ACCESS_CODE><GT_FLAG></GT_FLAG><QUANT_INPACK>1</QUANT_INPACK><QUANTITY>1</QUANTITY><PARTSUB_FLAG>O</PARTSUB_FLAG><INV_SUB_FLAG></INV_SUB_FLAG><CONTROL_CODE>JN</CONTROL_CODE><SUPERSESSION>1</SUPERSESSION><AVAILABILITY>FACING</AVAILABILITY><MAX_ORD_QTY>0</MAX_ORD_QTY><STATUS_CODE></STATUS_CODE><MAC_CODE></MAC_CODE><STOP_SALES></STOP_SALES><REFERRAL_CODE></REFERRAL_CODE><DEPOT_LIST><row><DEPOT_CODE>07</DEPOT_CODE><DEPOT_NAME>LEXUS SOUTHERN</DEPOT_NAME><ON_HAND>0</ON_HAND><IN_TRANSIT>0</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>08</DEPOT_CODE><DEPOT_NAME>LEXUS DALLAS</DEPOT_NAME><ON_HAND>0</ON_HAND><IN_TRANSIT>0</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>11</DEPOT_CODE><DEPOT_NAME>LOS ANGELES</DEPOT_NAME><ON_HAND>503</ON_HAND><IN_TRANSIT>1200</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>1</FACING_PDC></row><row><DEPOT_CODE>12</DEPOT_CODE><DEPOT_NAME>SAN FRANCISCO</DEPOT_NAME><ON_HAND>632</ON_HAND><IN_TRANSIT>500</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>13</DEPOT_CODE><DEPOT_NAME>PORTLAND</DEPOT_NAME><ON_HAND>557</ON_HAND><IN_TRANSIT>0</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>16</DEPOT_CODE><DEPOT_NAME>NEW YORK</DEPOT_NAME><ON_HAND>458</ON_HAND><IN_TRANSIT>100</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>17</DEPOT_CODE><DEPOT_NAME>BOSTON</DEPOT_NAME><ON_HAND>460</ON_HAND><IN_TRANSIT>200</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>18</DEPOT_CODE><DEPOT_NAME>BALTIMORE PDC</DEPOT_NAME><ON_HAND>583</ON_HAND><IN_TRANSIT>300</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>21</DEPOT_CODE><DEPOT_NAME>CHICAGO</DEPOT_NAME><ON_HAND>503</ON_HAND><IN_TRANSIT>100</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>22</DEPOT_CODE><DEPOT_NAME>CINCINNATI</DEPOT_NAME><ON_HAND>376</ON_HAND><IN_TRANSIT>0</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>23</DEPOT_CODE><DEPOT_NAME>KANSAS CITY</DEPOT_NAME><ON_HAND>367</ON_HAND><IN_TRANSIT>0</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>O</DEPOT_CODE><DEPOT_NAME>Parts Center - NAPCC</DEPOT_NAME><ON_HAND>0</ON_HAND><IN_TRANSIT>0</IN_TRANSIT><BACK_ORDER></BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>T</DEPOT_CODE><DEPOT_NAME>Parts Center - NAPCK</DEPOT_NAME><ON_HAND>8582</ON_HAND><IN_TRANSIT>1700</IN_TRANSIT><BACK_ORDER></BACK_ORDER><FACING_PDC>0</FACING_PDC></row></DEPOT_LIST><DEPOT_ON_HAND_TOTAL>13021</DEPOT_ON_HAND_TOTAL><DEPOT_IN_TRANSIT_TOTAL>4100</DEPOT_IN_TRANSIT_TOTAL><DEPOT_BACK_ORDER_TOTAL>0</DEPOT_BACK_ORDER_TOTAL><FormattedPartNo>90080-91184</FormattedPartNo><FormattedDealerPrc>$6.22</FormattedDealerPrc><FormattedWhlsalePrc>$8.81</FormattedWhlsalePrc><FormattedRetailPrc>$10.37</FormattedRetailPrc><FormattedCoreValue>$0.00</FormattedCoreValue></row>


<row><MANUF_CODE>TY</MANUF_CODE><PART_NBR>04152YZZA1</PART_NBR><PART_DESC>REPLACEABLE ELEMENT</PART_DESC><SOURCE_CODE>01</SOURCE_CODE><DEALER_PRC>324</DEALER_PRC><WHLSALE_PRC>460</WHLSALE_PRC><RETAIL_PRC>541</RETAIL_PRC><CORE_VALUE>0000000</CORE_VALUE><CLASS_CODE>A</CLASS_CODE><ACCESS_CODE></ACCESS_CODE><GT_FLAG></GT_FLAG><QUANT_INPACK>10</QUANT_INPACK><QUANTITY>1</QUANTITY><PARTSUB_FLAG>O</PARTSUB_FLAG><INV_SUB_FLAG></INV_SUB_FLAG><CONTROL_CODE>JC</CONTROL_CODE><SUPERSESSION>1</SUPERSESSION><AVAILABILITY>FACING</AVAILABILITY><MAX_ORD_QTY>0</MAX_ORD_QTY><STATUS_CODE></STATUS_CODE><MAC_CODE></MAC_CODE><STOP_SALES></STOP_SALES><REFERRAL_CODE></REFERRAL_CODE><DEPOT_LIST><row><DEPOT_CODE>07</DEPOT_CODE><DEPOT_NAME>LEXUS SOUTHERN</DEPOT_NAME><ON_HAND>4851</ON_HAND><IN_TRANSIT>2400</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>08</DEPOT_CODE><DEPOT_NAME>LEXUS DALLAS</DEPOT_NAME><ON_HAND>4350</ON_HAND><IN_TRANSIT>1920</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>11</DEPOT_CODE><DEPOT_NAME>LOS ANGELES</DEPOT_NAME><ON_HAND>9540</ON_HAND><IN_TRANSIT>5280</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>1</FACING_PDC></row><row><DEPOT_CODE>12</DEPOT_CODE><DEPOT_NAME>SAN FRANCISCO</DEPOT_NAME><ON_HAND>4472</ON_HAND><IN_TRANSIT>0</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>13</DEPOT_CODE><DEPOT_NAME>PORTLAND</DEPOT_NAME><ON_HAND>3020</ON_HAND><IN_TRANSIT>960</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>16</DEPOT_CODE><DEPOT_NAME>NEW YORK</DEPOT_NAME><ON_HAND>14080</ON_HAND><IN_TRANSIT>7680</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>17</DEPOT_CODE><DEPOT_NAME>BOSTON</DEPOT_NAME><ON_HAND>7740</ON_HAND><IN_TRANSIT>1920</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>18</DEPOT_CODE><DEPOT_NAME>BALTIMORE PDC</DEPOT_NAME><ON_HAND>6094</ON_HAND><IN_TRANSIT>9120</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>21</DEPOT_CODE><DEPOT_NAME>CHICAGO</DEPOT_NAME><ON_HAND>10048</ON_HAND><IN_TRANSIT>9600</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>22</DEPOT_CODE><DEPOT_NAME>CINCINNATI</DEPOT_NAME><ON_HAND>5850</ON_HAND><IN_TRANSIT>5760</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>23</DEPOT_CODE><DEPOT_NAME>KANSAS CITY</DEPOT_NAME><ON_HAND>4890</ON_HAND><IN_TRANSIT>1920</IN_TRANSIT><BACK_ORDER>0</BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>O</DEPOT_CODE><DEPOT_NAME>Parts Center - NAPCC</DEPOT_NAME><ON_HAND>55429</ON_HAND><IN_TRANSIT>2880</IN_TRANSIT><BACK_ORDER></BACK_ORDER><FACING_PDC>0</FACING_PDC></row><row><DEPOT_CODE>T</DEPOT_CODE><DEPOT_NAME>Parts Center - NAPCK</DEPOT_NAME><ON_HAND>0</ON_HAND><IN_TRANSIT>0</IN_TRANSIT><BACK_ORDER></BACK_ORDER><FACING_PDC>0</FACING_PDC></row></DEPOT_LIST><DEPOT_ON_HAND_TOTAL>130364</DEPOT_ON_HAND_TOTAL><DEPOT_IN_TRANSIT_TOTAL>49440</DEPOT_IN_TRANSIT_TOTAL><DEPOT_BACK_ORDER_TOTAL>0</DEPOT_BACK_ORDER_TOTAL><FormattedPartNo>04152-YZZA1</FormattedPartNo><FormattedDealerPrc>$3.24</FormattedDealerPrc><FormattedWhlsalePrc>$4.60</FormattedWhlsalePrc><FormattedRetailPrc>$5.41</FormattedRetailPrc><FormattedCoreValue>$0.00</FormattedCoreValue></row>
</list></XML>

So what do you guys think? Ideally it would transfer data like an array into Excel where...

<MANUF_CODE>TY</MANUF_CODE><PART_NBR>04152YZZA1</PART_NBR><PART_DESC>REPLACEABLE ELEMENT</PART_DESC>

...would put "TY" into column A, "04152YZZA1" into column B, "REPLACEABLE ELEMENT" into column C and so on and then move on to the next part number. And if it matters, this may be for as many as 3000 part numbers at a time, although it can be done in smaller increments if necessary.

[Juvigy]

Excel 2007 opens the xml file as a table wich would work fine for your purposes i think.

[PsaltyDS]

Your XML declaration is formatted wrong. So I had to slightly modify it (your script could take care of this):

<?xml version="1.0"?>

<!-- *** End of Button Pane *** --><!-- Begin XML data islands and XSL filter definitions -->
<!-- XML id=datList class=DataFunctions LoadPage="/Parts/PartInfo/PartInfoTransmitDetail.aspx" -->
<list>
<row>

  ...rest of the data here

</row>
</list>

Note there is no trailing "</XML>", either.

Once that was fixed, this script works fine:

#include <Array.au3>
#include <_XMLDOMWrapper.au3>

$sXML = @ScriptDir & "\Test_1.xml"
$iRET = _XMLFileOpen($sXML)
$iErrSav = @error
If $iErrSav Then
    MsgBox(16, "Error", "Failed to open file: " & $sXML)
    Exit
EndIf

; Get "row" count
$iCnt = _XMLGetNodeCount("/list/row")
ConsoleWrite("$iCnt = " & $iCnt & @LF)
If $iCnt < 1 Then
    MsgBox(16, "Error", "No Rows to read")
    Exit
EndIf

; Create 2D data array
Global $aData[$iCnt + 1][23] = [["MANUF_CODE", "PART_NBR", "PART_DESC", "SOURCE_CODE", "DEALER_PRC", _
        "WHLSALE_PRC", "RETAIL_PRC", "CORE_VALUE", "CLASS_CODE", "ACCESS_CODE", _
        "GT_FLAG", "QUANT_INPACK", "QUANTITY", "PARTSUB_FLAG", "INV_SUB_FLAG", _
        "CONTROL_CODE", "SUPERSESSION", "AVAILABILITY", "MAX_ORD_QTY", "STATUS_CODE", _
        "MAC_CODE", "STOP_SALES", "REFERRAL_CODE"]]

; Populate array from xml
For $r = 1 To $iCnt
    For $c = 0 To UBound($aData, 2) - 1
        $aValue = _XMLGetValue("/list/row[" & $r & "]/" & $aData[0][$c])
        If (@error = 0) And IsArray($aValue) And ($aValue[0] >= 1) Then $aData[$r][$c] = $aValue[1]
    Next
Next

; Show result
_ArrayDisplay($aData, "$aData")

Now that you have the data in a 2D array, it can be written directly to an Excel worksheet with _ExcelWriteSheetFromArray().

[drapdv]

Thank you so much for your time and effort on this, Psalty! Although the adjusted script is Toyota's, not mine, so I'll have to see how it plays out, but I think this gets me pointed in the right direction.

Thank you again, I'll let you know how it turns out!