Like many of the posters I read through on here I’m a fairly new scripting n00b. I’m not a complete n00b as I have several working custom scripts that I’ve written but this one’s giving me heartburn. I’m trying to convert some data I get in excel that looks something like this:
Header junk
header junk 2
header junk 3
PO External ID Part text1 Description Cost Qty
A123 999999 1001 text text 19.99 6
A123 999999 1003 text text 19.99 12
A123 999999 1004 text text 29.99 6
B234 999999 1003 text text 19.99 9
B234 999999 1007 text text 39.99 12
I’m trying to convert it to an XML document that looks like this for example:
<?xml version="1.0" encoding="ISO-8859-1"?>
<client id="500" client-name="Our Client">
<sales_document id="A123">
<field name="order_date">11/01/2011</field>
<field name="invoice_number">A123</field>
<invoicedetail id="A123-1" part-number="1001">
<field name="part number">1001</field>
<field name="qty ordered">6</field>
<field name="sell">19.99</field>
<invoicedetail id="A123-2" part-number="1003">
<field name="part number">1003</field>
<field name="qty ordered">12</field>
<field name="sell">19.99</field>
<invoicedetail id="A123-3" part-number="1004">
<field name="part number">1004</field>
<field name="qty ordered">6</field>
<field name="sell">29.99</field>
</invoicedetail>
</sales_document>
</client>
Then a 2nd or 3rd and so forth file would be created for the next PO group like:
<?xml version="1.0" encoding="ISO-8859-1"?>
<client id="500" client-name="Our Client">
<sales_document id="B234">
<field name="order_date">11/02/2011</field>
<field name="invoice_number">B234</field>
<invoicedetail id="B234-1" part-number="1003">
<field name="part number">1003</field>
<field name="qty ordered">9</field>
<field name="sell">19.99</field>
<invoicedetail id="B234-2" part-number="1007">
<field name="part number">1007</field>
<field name="qty ordered">12</field>
<field name="sell">39.99</field>
</invoicedetail>
</sales_document>
</client>
I can muddle through opening the file and setting up basic scripts to query the lines and return line by line data but I’m having trouble with grouping and listing part number detail under 1 PO group, saving it. Then starting the whole format process over again for the next set of PO numbers.
The scripting I’ve written so far is to get the data formatted just right in the XML layout so it’s probably not of much use here. I’ve spent the last several days combing through the older posts on here and other places looking for some good direction but I was turning up nothing that would work for me.
If someone would like to take over the whole process of writing the script I’d be very open to paying someone to do it right. Just use the email or PM link on the left and we can get in contact directly.
Thanks,
Stephen