Sign in to follow this  
Followers 0
drapdv

A challenge for the big dogs! (xml related)

4 posts in this topic

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.

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

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().

:(


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0