Make changes to XML file based upon matches found in a Parms csv file

wacreekm · Aug 05, 2008#12008-08-05T15:02+00:00

I have a need for a UE script to read a xml file, make changes to one element in the xml file based upon matches found in three of the elements against a match found in a parms csv formated file. I would appreciate any help given. It is similar to a previous post request I noticed called "Read one file and use it to parse another 1 liner". I was hoping mabe jorrasdk or another UE advance user might want to make a stab at it and help out me, a beginner UE script user. Below are the xml and parm file examples.
Thank you in advance for your assistance!!! UE rocks!

Instructions for script:
There are multiple xml invoice files per xml file. This sample has 4 xml invoice files in it. Each file has a header level area and a line level area.
In the line level area there are some line items with a Buyer Part Number of "999" (<edi:BuyerPartNumber>999</edi:BuyerPartNumber>), that needs the '999' change to the correct code found in the parms csv file, based upon matches found in three other elements. The elements to match agains in the xml file are, <edi:SellerPartNumber> (which is the first field in the Parms csv file), <edi:PartNumberDescription> (which is the second field in the Parms csv file), <edi:SellerMfrCode> (which is the third field in the Parms csv file). If a match is found in the csv file for a line in the xml file that contains a '999' in the <edi:BuyerPartNumber> element, then replace the '999' in the xml file with the fourth field in the matching csv file line. If a match is not found, make not change to the xml file. If the xml line does not contain a '999' in the Buyer Part Number, then make no change to the xml file.

File samples:
>>>>>>>>>>>>>>>>>>>
Parm_File.csv

41-601,SPARK PLUG,AC,222,
41-602,SPARK PLUG,AC,222,
34352229018,NIF Part,XBO,237
INV PR DIFF,NIF Part,XBO,222
1275602,NIF Part,WPC,222

>>>>>>>>>>>>>>>>>>>>>>>
XML file to be changed:

sample_batchfile_xml.txt

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>
<edi:NAInvoice xmlns:edi="http://www.carquest.com/edi">
  <edi:Sender>
    <edi:Qualifier>01</edi:Qualifier>
    <edi:ID>101735868</edi:ID>
  </edi:Sender>
  <edi:Receiver>
    <edi:Qualifier>01</edi:Qualifier>
    <edi:ID>004467924</edi:ID>
  </edi:Receiver>
  <edi:BatchNumber>31</edi:BatchNumber>
  <edi:NAInvoiceHeader>
    <edi:StoreKey>2988</edi:StoreKey>
    <edi:POSAccountCode>K1780</edi:POSAccountCode>
    <edi:SellerAssignedCustomerID>459470</edi:SellerAssignedCustomerID>
    <edi:MemberCode>GPI-JV</edi:MemberCode>
    <edi:BuyerAssignedCustomerID>1034</edi:BuyerAssignedCustomerID>
    <edi:BillTo>
      <edi:Name>GOODYEAR 1034</edi:Name>
      <edi:Address1>5913 PENN CIR N</edi:Address1>
      <edi:Address2/>
      <edi:City>PITTSBURGH</edi:City>
      <edi:StateOrProvince>PA</edi:StateOrProvince>
      <edi:PostalCode>15206</edi:PostalCode>
      <edi:CountryCode>US</edi:CountryCode>
    </edi:BillTo>
    <edi:Payee>
      <edi:Name>27779</edi:Name>
    </edi:Payee>
    <edi:ShipTo>
      <edi:Name>GOODYEAR</edi:Name>
      <edi:Address1>5913 PENN CIRCLE NORTH</edi:Address1>
      <edi:Address2></edi:Address2>
      <edi:City>PITTSBURGH</edi:City>
      <edi:StateOrProvince>PA</edi:StateOrProvince>
      <edi:PostalCode>15206</edi:PostalCode>
      <edi:CountryCode></edi:CountryCode>
    </edi:ShipTo>
    <edi:RemitTo>
      <edi:Address1>CARQUEST AUTO PARTS STORES</edi:Address1>
      <edi:Address2>PO BOX 503628</edi:Address2>
      <edi:City>ST LOUIS</edi:City>
      <edi:StateOrProvince>MO</edi:StateOrProvince>
      <edi:PostalCode>63150-3628</edi:PostalCode>
      <edi:CountryCode>United States</edi:CountryCode>
    </edi:RemitTo>
    <edi:CurrencyCode>USD</edi:CurrencyCode>
    <edi:InvoiceDate>2008-07-24</edi:InvoiceDate>
    <edi:CorporateInvoiceNumber>2988-ID-73806</edi:CorporateInvoiceNumber>
    <edi:StoreInvoiceNumber>73806</edi:StoreInvoiceNumber>
    <edi:PrintedInvoiceNumber>2988-73806</edi:PrintedInvoiceNumber>
    <edi:ManualInvoiceNumber>2988-ID-73806</edi:ManualInvoiceNumber>
    <edi:ModeTwoInvoiceNumber></edi:ModeTwoInvoiceNumber>
    <edi:PONumber>159768</edi:PONumber>
    <edi:PODate>2008-07-24</edi:PODate>
    <edi:ShippedDate>2008-07-24</edi:ShippedDate>
    <edi:TransactionType>INV</edi:TransactionType>
    <edi:TransactionTotal>18.85</edi:TransactionTotal>
    <edi:StateSalesTax>0.0000</edi:StateSalesTax>
    <edi:CountySalesTax>0.0000</edi:CountySalesTax>
    <edi:OtherSalesTax>0.0000</edi:OtherSalesTax>
    <edi:SalesTaxTotal>0.0000</edi:SalesTaxTotal>
    <edi:FreightTotal>0.0000</edi:FreightTotal>
    <edi:LaborTotal>0.0000</edi:LaborTotal>
    <edi:CoreTotal>0.0000</edi:CoreTotal>
    <edi:InvoiceDiscountTotal>0.0000</edi:InvoiceDiscountTotal>
    <edi:ServiceCharge>0.0000</edi:ServiceCharge>
    <edi:SpecialUse>0.0000</edi:SpecialUse>
    <edi:StateSurcharge>0.0000</edi:StateSurcharge>
    <edi:CustomerTrxId>278031715</edi:CustomerTrxId>
  </edi:NAInvoiceHeader>
  <edi:NAInvoiceLine>
    <edi:LineNumber>1</edi:LineNumber>
    <edi:SellerMfrCode>XBO</edi:SellerMfrCode>
    <edi:SellerPartNumber>34352229018</edi:SellerPartNumber>
    <edi:BuyerPartNumber>999</edi:BuyerPartNumber>
    <edi:PartNumberDescription>NIF Part</edi:PartNumberDescription>
    <edi:QuantityInvoiced>1</edi:QuantityInvoiced>
    <edi:UnitOfMeasureCode>EA</edi:UnitOfMeasureCode>
    <edi:UnitPrice>18.8500</edi:UnitPrice>
    <edi:InvoiceDiscount>0.0000</edi:InvoiceDiscount>
    <edi:ExtendedAmount>18.8500</edi:ExtendedAmount>
    <edi:CoreCharge>0.0000</edi:CoreCharge>
    <edi:LineItemMessage></edi:LineItemMessage>
    <edi:OrderedElectronically>false</edi:OrderedElectronically>
    <edi:ItemType>PART</edi:ItemType>
    <edi:ServiceCharge>0.0000</edi:ServiceCharge>
    <edi:SpecialUse>0.0000</edi:SpecialUse>
    <edi:Tax>0.00000</edi:Tax>
    <edi:StateSurcharge>0.0000</edi:StateSurcharge>
  </edi:NAInvoiceLine>
</edi:NAInvoice><?xml version="1.0" encoding="UTF-8"?>
<edi:NAInvoice xmlns:edi="http://www.carquest.com/edi">
  <edi:Sender>
    <edi:Qualifier>01</edi:Qualifier>
    <edi:ID>101735868</edi:ID>
  </edi:Sender>
  <edi:Receiver>
    <edi:Qualifier>01</edi:Qualifier>
    <edi:ID>004467924</edi:ID>
  </edi:Receiver>
  <edi:BatchNumber>31</edi:BatchNumber>
  <edi:NAInvoiceHeader>
    <edi:StoreKey>2149</edi:StoreKey>
    <edi:POSAccountCode>031176</edi:POSAccountCode>
    <edi:SellerAssignedCustomerID>608861</edi:SellerAssignedCustomerID>
    <edi:MemberCode>GPI-JV</edi:MemberCode>
    <edi:BuyerAssignedCustomerID>6720</edi:BuyerAssignedCustomerID>
    <edi:BillTo>
      <edi:Name>GOODYEAR 6720 DOWNTOWN</edi:Name>
      <edi:Address1>627 NORTH DELAWARE ST</edi:Address1>
      <edi:Address2/>
      <edi:City>INDIANAPOLIS</edi:City>
      <edi:StateOrProvince>IN</edi:StateOrProvince>
      <edi:PostalCode>46204</edi:PostalCode>
      <edi:CountryCode>US</edi:CountryCode>
    </edi:BillTo>
    <edi:Payee>
      <edi:Name>27779</edi:Name>
    </edi:Payee>
    <edi:ShipTo>
      <edi:Name>GOODYEAR DOWNTOWN</edi:Name>
      <edi:Address1>627 N DELAWARE ST</edi:Address1>
      <edi:Address2></edi:Address2>
      <edi:City>INDIANAPOLIS</edi:City>
      <edi:StateOrProvince>IN</edi:StateOrProvince>
      <edi:PostalCode></edi:PostalCode>
      <edi:CountryCode></edi:CountryCode>
    </edi:ShipTo>
    <edi:RemitTo>
      <edi:Address1>CARQUEST AUTO PARTS STORES</edi:Address1>
      <edi:Address2>PO BOX 503601</edi:Address2>
      <edi:City>ST LOUIS</edi:City>
      <edi:StateOrProvince>MO</edi:StateOrProvince>
      <edi:PostalCode>63150-3601</edi:PostalCode>
      <edi:CountryCode>United States</edi:CountryCode>
    </edi:RemitTo>
    <edi:CurrencyCode>USD</edi:CurrencyCode>
    <edi:InvoiceDate>2008-07-24</edi:InvoiceDate>
    <edi:CorporateInvoiceNumber>2149-ID-104139</edi:CorporateInvoiceNumber>
    <edi:StoreInvoiceNumber>104139</edi:StoreInvoiceNumber>
    <edi:PrintedInvoiceNumber>2149-104139</edi:PrintedInvoiceNumber>
    <edi:ManualInvoiceNumber>2149-ID-104139</edi:ManualInvoiceNumber>
    <edi:ModeTwoInvoiceNumber></edi:ModeTwoInvoiceNumber>
    <edi:PONumber>6720</edi:PONumber>
    <edi:PODate>2008-07-24</edi:PODate>
    <edi:ShippedDate>2008-07-24</edi:ShippedDate>
    <edi:TransactionType>INV</edi:TransactionType>
    <edi:TransactionTotal>103.44</edi:TransactionTotal>
    <edi:StateSalesTax>0.0000</edi:StateSalesTax>
    <edi:CountySalesTax>0.0000</edi:CountySalesTax>
    <edi:OtherSalesTax>0.0000</edi:OtherSalesTax>
    <edi:SalesTaxTotal>0.0000</edi:SalesTaxTotal>
    <edi:FreightTotal>0.0000</edi:FreightTotal>
    <edi:LaborTotal>0.0000</edi:LaborTotal>
    <edi:CoreTotal>0.0000</edi:CoreTotal>
    <edi:InvoiceDiscountTotal>0.0000</edi:InvoiceDiscountTotal>
    <edi:ServiceCharge>0.0000</edi:ServiceCharge>
    <edi:SpecialUse>0.0000</edi:SpecialUse>
    <edi:StateSurcharge>0.0000</edi:StateSurcharge>
    <edi:CustomerTrxId>278023523</edi:CustomerTrxId>
  </edi:NAInvoiceHeader>
  <edi:NAInvoiceLine>
    <edi:LineNumber>1</edi:LineNumber>
    <edi:SellerMfrCode>XBO</edi:SellerMfrCode>
    <edi:SellerPartNumber>12378508</edi:SellerPartNumber>
    <edi:BuyerPartNumber>237</edi:BuyerPartNumber>
    <edi:PartNumberDescription>NIF Part</edi:PartNumberDescription>
    <edi:QuantityInvoiced>12</edi:QuantityInvoiced>
    <edi:UnitOfMeasureCode>EA</edi:UnitOfMeasureCode>
    <edi:UnitPrice>8.6200</edi:UnitPrice>
    <edi:InvoiceDiscount>0.0000</edi:InvoiceDiscount>
    <edi:ExtendedAmount>103.4400</edi:ExtendedAmount>
    <edi:CoreCharge>0.0000</edi:CoreCharge>
    <edi:LineItemMessage></edi:LineItemMessage>
    <edi:OrderedElectronically>false</edi:OrderedElectronically>
    <edi:ItemType>PART</edi:ItemType>
    <edi:ServiceCharge>0.0000</edi:ServiceCharge>
    <edi:SpecialUse>0.0000</edi:SpecialUse>
    <edi:Tax>0.00000</edi:Tax>
    <edi:StateSurcharge>0.0000</edi:StateSurcharge>
  </edi:NAInvoiceLine>
</edi:NAInvoice><?xml version="1.0" encoding="UTF-8"?>
<edi:NAInvoice xmlns:edi="http://www.carquest.com/edi">
  <edi:Sender>
    <edi:Qualifier>01</edi:Qualifier>
    <edi:ID>101735868</edi:ID>
  </edi:Sender>
  <edi:Receiver>
    <edi:Qualifier>01</edi:Qualifier>
    <edi:ID>004467924</edi:ID>
  </edi:Receiver>
  <edi:BatchNumber>31</edi:BatchNumber>
  <edi:NAInvoiceHeader>
    <edi:StoreKey>12966</edi:StoreKey>
    <edi:POSAccountCode>510165</edi:POSAccountCode>
    <edi:SellerAssignedCustomerID>992478</edi:SellerAssignedCustomerID>
    <edi:MemberCode>GPI-JV</edi:MemberCode>
    <edi:BuyerAssignedCustomerID>6651</edi:BuyerAssignedCustomerID>
    <edi:BillTo>
      <edi:Name>GOODYEAR  6651</edi:Name>
      <edi:Address1>3690 BIRD RD</edi:Address1>
      <edi:Address2/>
      <edi:City>CORAL GABLES</edi:City>
      <edi:StateOrProvince>FL</edi:StateOrProvince>
      <edi:PostalCode>33133</edi:PostalCode>
      <edi:CountryCode>US</edi:CountryCode>
    </edi:BillTo>
    <edi:Payee>
      <edi:Name>27779</edi:Name>
    </edi:Payee>
    <edi:ShipTo>
      <edi:Name>GOODYEAR 6651</edi:Name>
      <edi:Address1>3690 BIRD RD</edi:Address1>
      <edi:Address2></edi:Address2>
      <edi:City>CORAL GABLES</edi:City>
      <edi:StateOrProvince>FL</edi:StateOrProvince>
      <edi:PostalCode>33133</edi:PostalCode>
      <edi:CountryCode></edi:CountryCode>
    </edi:ShipTo>
    <edi:RemitTo>
      <edi:Address1>CARQUEST AUTO PARTS STORES</edi:Address1>
      <edi:Address2>PO  Box 404875</edi:Address2>
      <edi:City>ATLANTA</edi:City>
      <edi:StateOrProvince>GA</edi:StateOrProvince>
      <edi:PostalCode>30384-4875</edi:PostalCode>
      <edi:CountryCode>United States</edi:CountryCode>
    </edi:RemitTo>
    <edi:CurrencyCode>USD</edi:CurrencyCode>
    <edi:InvoiceDate>2008-07-24</edi:InvoiceDate>
    <edi:CorporateInvoiceNumber>12966-ID-1145</edi:CorporateInvoiceNumber>
    <edi:StoreInvoiceNumber>1145</edi:StoreInvoiceNumber>
    <edi:PrintedInvoiceNumber>12966-1145</edi:PrintedInvoiceNumber>
    <edi:ManualInvoiceNumber>12966-ID-1145</edi:ManualInvoiceNumber>
    <edi:ModeTwoInvoiceNumber></edi:ModeTwoInvoiceNumber>
    <edi:PONumber>134399</edi:PONumber>
    <edi:PODate>2008-07-24</edi:PODate>
    <edi:ShippedDate>2008-07-24</edi:ShippedDate>
    <edi:TransactionType>INV</edi:TransactionType>
    <edi:TransactionTotal>65.88</edi:TransactionTotal>
    <edi:StateSalesTax>0.0000</edi:StateSalesTax>
    <edi:CountySalesTax>0.0000</edi:CountySalesTax>
    <edi:OtherSalesTax>0.0000</edi:OtherSalesTax>
    <edi:SalesTaxTotal>0.0000</edi:SalesTaxTotal>
    <edi:FreightTotal>0.0000</edi:FreightTotal>
    <edi:LaborTotal>0.0000</edi:LaborTotal>
    <edi:CoreTotal>0.0000</edi:CoreTotal>
    <edi:InvoiceDiscountTotal>0.0000</edi:InvoiceDiscountTotal>
    <edi:ServiceCharge>0.0000</edi:ServiceCharge>
    <edi:SpecialUse>0.0000</edi:SpecialUse>
    <edi:StateSurcharge>0.0000</edi:StateSurcharge>
    <edi:CustomerTrxId>278070030</edi:CustomerTrxId>
  </edi:NAInvoiceHeader>
  <edi:NAInvoiceLine>
    <edi:LineNumber>1</edi:LineNumber>
    <edi:SellerMfrCode>WPC</edi:SellerMfrCode>
    <edi:SellerPartNumber>1275602</edi:SellerPartNumber>
    <edi:BuyerPartNumber>999</edi:BuyerPartNumber>
    <edi:PartNumberDescription>NIF Part</edi:PartNumberDescription>
    <edi:QuantityInvoiced>1</edi:QuantityInvoiced>
    <edi:UnitOfMeasureCode>EA</edi:UnitOfMeasureCode>
    <edi:UnitPrice>65.8800</edi:UnitPrice>
    <edi:InvoiceDiscount>0.0000</edi:InvoiceDiscount>
    <edi:ExtendedAmount>65.8800</edi:ExtendedAmount>
    <edi:CoreCharge>0.0000</edi:CoreCharge>
    <edi:LineItemMessage></edi:LineItemMessage>
    <edi:OrderedElectronically>false</edi:OrderedElectronically>
    <edi:ItemType>PART</edi:ItemType>
    <edi:ServiceCharge>0.0000</edi:ServiceCharge>
    <edi:SpecialUse>0.0000</edi:SpecialUse>
    <edi:Tax>0.00000</edi:Tax>
    <edi:StateSurcharge>0.0000</edi:StateSurcharge>
  </edi:NAInvoiceLine>
</edi:NAInvoice><?xml version="1.0" encoding="UTF-8"?>
<edi:NAInvoice xmlns:edi="http://www.carquest.com/edi">
  <edi:Sender>
    <edi:Qualifier>01</edi:Qualifier>
    <edi:ID>101735868</edi:ID>
  </edi:Sender>
  <edi:Receiver>
    <edi:Qualifier>01</edi:Qualifier>
    <edi:ID>004467924</edi:ID>
  </edi:Receiver>
  <edi:BatchNumber>31</edi:BatchNumber>
  <edi:NAInvoiceHeader>
    <edi:StoreKey>11599</edi:StoreKey>
    <edi:POSAccountCode>720001</edi:POSAccountCode>
    <edi:SellerAssignedCustomerID>1013172</edi:SellerAssignedCustomerID>
    <edi:MemberCode>GPI-JV</edi:MemberCode>
    <edi:BuyerAssignedCustomerID>2433</edi:BuyerAssignedCustomerID>
    <edi:BillTo>
      <edi:Name>GOODYEAR 2433</edi:Name>
      <edi:Address1>675 SKYMARKS DR</edi:Address1>
      <edi:Address2/>
      <edi:City>JACKSONVILLE</edi:City>
      <edi:StateOrProvince>FL</edi:StateOrProvince>
      <edi:PostalCode>32218</edi:PostalCode>
      <edi:CountryCode>US</edi:CountryCode>
    </edi:BillTo>
    <edi:Payee>
      <edi:Name>27779</edi:Name>
    </edi:Payee>
    <edi:ShipTo>
      <edi:Name>GOODYEAR #2433</edi:Name>
      <edi:Address1>675 SKYMARKS DR</edi:Address1>
      <edi:Address2></edi:Address2>
      <edi:City>JACKSONVILLE</edi:City>
      <edi:StateOrProvince>FL</edi:StateOrProvince>
      <edi:PostalCode>32218</edi:PostalCode>
      <edi:CountryCode></edi:CountryCode>
    </edi:ShipTo>
    <edi:RemitTo>
      <edi:Address1>CARQUEST AUTO PARTS STORES</edi:Address1>
      <edi:Address2>PO  Box 404875</edi:Address2>
      <edi:City>ATLANTA</edi:City>
      <edi:StateOrProvince>GA</edi:StateOrProvince>
      <edi:PostalCode>30384-4875</edi:PostalCode>
      <edi:CountryCode>United States</edi:CountryCode>
    </edi:RemitTo>
    <edi:CurrencyCode>USD</edi:CurrencyCode>
    <edi:InvoiceDate>2008-07-24</edi:InvoiceDate>
    <edi:CorporateInvoiceNumber>11599-ID-20368</edi:CorporateInvoiceNumber>
    <edi:StoreInvoiceNumber>20368</edi:StoreInvoiceNumber>
    <edi:PrintedInvoiceNumber>11599-20368</edi:PrintedInvoiceNumber>
    <edi:ManualInvoiceNumber>11599-ID-20368</edi:ManualInvoiceNumber>
    <edi:ModeTwoInvoiceNumber></edi:ModeTwoInvoiceNumber>
    <edi:PONumber>2433TP</edi:PONumber>
    <edi:PODate>2008-07-24</edi:PODate>
    <edi:ShippedDate>2008-07-24</edi:ShippedDate>
    <edi:TransactionType>INV</edi:TransactionType>
    <edi:TransactionTotal>132.06</edi:TransactionTotal>
    <edi:StateSalesTax>0.0000</edi:StateSalesTax>
    <edi:CountySalesTax>0.0000</edi:CountySalesTax>
    <edi:OtherSalesTax>0.0000</edi:OtherSalesTax>
    <edi:SalesTaxTotal>0.0000</edi:SalesTaxTotal>
    <edi:FreightTotal>0.0000</edi:FreightTotal>
    <edi:LaborTotal>0.0000</edi:LaborTotal>
    <edi:CoreTotal>0.0000</edi:CoreTotal>
    <edi:InvoiceDiscountTotal>0.0000</edi:InvoiceDiscountTotal>
    <edi:ServiceCharge>0.0000</edi:ServiceCharge>
    <edi:SpecialUse>0.0000</edi:SpecialUse>
    <edi:StateSurcharge>0.0000</edi:StateSurcharge>
    <edi:CustomerTrxId>278068737</edi:CustomerTrxId>
  </edi:NAInvoiceHeader>
  <edi:NAInvoiceLine>
    <edi:LineNumber>1</edi:LineNumber>
    <edi:SellerMfrCode>XBO</edi:SellerMfrCode>
    <edi:SellerPartNumber>ICMD308086</edi:SellerPartNumber>
    <edi:BuyerPartNumber>999</edi:BuyerPartNumber>
    <edi:PartNumberDescription>TIMING TENSIONER ACTUATOR</edi:PartNumberDescription>
    <edi:QuantityInvoiced>1</edi:QuantityInvoiced>
    <edi:UnitOfMeasureCode>EA</edi:UnitOfMeasureCode>
    <edi:UnitPrice>132.0600</edi:UnitPrice>
    <edi:InvoiceDiscount>0.0000</edi:InvoiceDiscount>
    <edi:ExtendedAmount>132.0600</edi:ExtendedAmount>
    <edi:CoreCharge>0.0000</edi:CoreCharge>
    <edi:LineItemMessage></edi:LineItemMessage>
    <edi:OrderedElectronically>false</edi:OrderedElectronically>
    <edi:ItemType>PART</edi:ItemType>
    <edi:ServiceCharge>0.0000</edi:ServiceCharge>
    <edi:SpecialUse>0.0000</edi:SpecialUse>
    <edi:Tax>0.00000</edi:Tax>
    <edi:StateSurcharge>0.0000</edi:StateSurcharge>
  </edi:NAInvoiceLine>
</edi:NAInvoice>

jorrasdk · Aug 05, 2008#22008-08-05T21:13+00:00

Hello wacreekm

Let me make a stab at it, but I fear I will not be able to respond sooner than Thursday, so if anybody else in the forum feel up to it, please go ahead.

Regards, jorrasdk

wacreekm · Aug 06, 2008#32008-08-06T16:45+00:00

Thank you jorrasdk,
Anything you can do to help me get started on it would be of great help!
Thanks again!

jorrasdk · Aug 07, 2008#42008-08-07T08:54+00:00

Ok, wacreekm, sit back and prepare for a lengthy script. My approach has been to use the native XML support of Javascript. Others might have experimented with regular expressions, and indeed it might be possible, but I chose the XML approach. The steps of the script is basically:

Setup enviroment, define constants and settings.
Open parms file and read parms into an array
Open xml file and read xml docs into an array of xml docs
(dump retrieved parms and some xml info to UltraEdit.outputWindow for trace purposes. (Remember to activate output window in view / views/list) - could be opened by the script, but I didn't bother this time).
Iterate though xml documents and find those matching parms and update BuyerPartNumber.
reWrite xml documents to file if a BuyerPartNumber was changed
final script "shut down processing".

I hope I have added sufficient comments in the script code. Read these carefully. Besides the comments you might want to study the E4X (EcmaScript for XML) tutorial for further info about the XML support in javascript.

Code: Select all

// Script to read and update XML documents from a CSV file
// Released into public domain.

// ---------------------------------------------
// Define file paths
var csvParmFilePath = "C:\\temp\\carquest.csv";

var xmlFilePath = "C:\\temp\\carquest.xml";
var xmlDocNew = false; /* true = write changed xml to new file, false = overwrite old file */

// ---------------------------------------------
// Get index of parm file if it's already open in editor
var csvDocIx = getDocumentIndex(csvParmFilePath);
var csvDocPreOpened = true; /* until proven otherwise */
// if not open, then open it
if (csvDocIx == -1) {
  csvDocPreOpened = false;
  UltraEdit.open(csvParmFilePath);
  // and get the docIx
  csvDocIx = getDocumentIndex(csvParmFilePath);
}

// ---------------------------------------------
// Get index of xml file if it's already open in editor
var xmlDocIx = getDocumentIndex(xmlFilePath);
var xmlDocPreOpened = true; /* until proven otherwise */
var xmlDocChange = false; /* signal changes to XML need to be rewritten into editor */
// if not open, then open it
if (xmlDocIx == -1) {
  xmlDocPreOpened = false;
  UltraEdit.open(xmlFilePath);
  // and get the docIx
  xmlDocIx = getDocumentIndex(xmlFilePath);
}

// ---------------------------------------------
// Misc options for global XML object:
// http://developer.mozilla.org/en/docs/E4X_Tutorial:The_global_XML_object
XML.ignoreComments = false;
XML.ignoreProcessingInstructions = false;
XML.ignoreWhitespace = true;
XML.prettyPrinting = true;
XML.prettyIndent = 2;

// ---------------------------------------------
// Define working environment for UltraEdit
UltraEdit.perlReOn();
UltraEdit.insertMode();
UltraEdit.columnModeOff();

// ---------------------------------------------
// Helper prototype: remove starting and ending / as UE does not recognize these for regexp
RegExp.prototype.toUEregexp = function() { return this.toString().replace(/^\/|\/$/g, ""); };

// ---------------------------------------------
// Setup global "short names" for UltraEdit documents
if ((csvDocIx != -1) && (xmlDocIx != -1)) {
  var csvDoc = UltraEdit.document[csvDocIx];
  var xmlDoc = UltraEdit.document[xmlDocIx];

  // Start main processing of script:
  updateXmlDocument();
}

// ---------------------------------------------
// close documents that was not open from start when script is ending:
if ((csvDocPreOpened == false) && (csvDocIx != -1)) UltraEdit.closeFile(csvDoc.path,2);
if ((xmlDocPreOpened == false) && (xmlDocIx != -1)) UltraEdit.closeFile(xmlDoc.path,2);

// ---------------------------------------------
// Main script function
function updateXmlDocument() {

  // Read parm file with parms for invoicelines to be updated
  var parmList = getParmList();

  // all invoices in XML is going to be extracted to an array of XML document objects:
  var NAInvoiceList = getNAInvoiceList();

  // Dump retrieved parms to UltraEdit output window for trace purposes (can be removed for performance later)
  if (parmList.length > 0) { /* parms found */
    dumpParms(parmList);
  }

  // Dump xml doc info to UltraEdit output window for trace purposes (can be removed for performance later)
  if (NAInvoiceList.length > 0) { /* Invoices found */
    dumpInvoices(NAInvoiceList);
  }

  // Now process all invoice XML docs and match agains parms
  processInvoiceXmlDocs(parmList, NAInvoiceList);

  // If at least one xml doc was changed, recreate whole document from xml invoice array:
  if (xmlDocChange==true) {
    if (xmlDocNew==true) { /* create new file */
      UltraEdit.newFile();
      var newDoc = UltraEdit.document[UltraEdit.document.length - 1];
      rewriteInvoiceXml(newDoc,NAInvoiceList);
    }
    else { /* overwrite */
      rewriteInvoiceXml(xmlDoc,NAInvoiceList);
      if (xmlDocPreOpened==false) { /* not open in editor, then save first */
        xmlDoc.setActive();
        UltraEdit.save();
      }
    }
  }
  else { /* no changes */
    if (xmlDocPreOpened==true) { /* opened in editor, then re-read */
      UltraEdit.closeFile(xmlFilePath,2);
      UltraEdit.open(xmlFilePath);
    }
  }
}

// Simple function for trace purposes to dump parms read from file:
function dumpParms(parmList) {
  for (var i=0;i<parmList.length;i++) {
    UltraEdit.outputWindow.write("Parms=[ sellerPartNumber="+parmList[i][0]+", partNumberDescription="+parmList[i][1]+", sellerMfrCode="+parmList[i][2]+", buyerPartNumber="+parmList[i][3]+" ]");
  }
}

// Simple function for trace purposes to dump info from xml docs:
function dumpInvoices(NAInvoiceList) {
  for (var i=0;i<NAInvoiceList.length;i++) {

    // register namespace:
    var edins = new Namespace('http://www.carquest.com/edi');

    // Get Xmllist of invoiceLines
    var invoiceLines = NAInvoiceList[i].edins::NAInvoiceLine;

    for (j in invoiceLines) {
      // Array to collect xml matchCrit:
      var matchCrit = new Array();

      // Get <edi:SellerPartNumber>
      matchCrit[0] = invoiceLines[j].edins::SellerPartNumber;

      // Get <edi:PartNumberDescription>
      matchCrit[1] = invoiceLines[j].edins::PartNumberDescription;

      // Get <edi:SellerMfrCode>
      matchCrit[2] = invoiceLines[j].edins::SellerMfrCode;

      // Get <edi:BuyerPartNumber>
      matchCrit[3] = invoiceLines[j].edins::BuyerPartNumber;

      // Get <edi:LineNumber>
      matchCrit[4] = invoiceLines[j].edins::LineNumber;

      UltraEdit.outputWindow.write("InvoiceLines=[ Line#="+matchCrit[4]+", sellerPartNumber="+matchCrit[0]+", partNumberDescription="+matchCrit[1]+", sellerMfrCode="+matchCrit[2]+", buyerPartNumber="+matchCrit[3]+" ]");
    }

  }
}

// Iterate through invoice XML docs and match agains parms
function processInvoiceXmlDocs(parmList, NAInvoiceList) {
  for (var i=0;i<NAInvoiceList.length;i++) {

    // register namespace:
    var edins = new Namespace('http://www.carquest.com/edi');

    // Get Xmllist of invoiceLines
    var invoiceLines = NAInvoiceList[i].edins::NAInvoiceLine;

    for (j in invoiceLines) {
      // Array to collect xml match criteria:
      var matchCrit = new Array();

      // Get <edi:SellerPartNumber>
      matchCrit[0] = invoiceLines[j].edins::SellerPartNumber;

      // Get <edi:PartNumberDescription>
      matchCrit[1] = invoiceLines[j].edins::PartNumberDescription;

      // Get <edi:SellerMfrCode>
      matchCrit[2] = invoiceLines[j].edins::SellerMfrCode;

      // Get <edi:BuyerPartNumber>
      matchCrit[3] = invoiceLines[j].edins::BuyerPartNumber;

      parmListLoop:
      for (l in parmList) {
        if ( (matchCrit[0] == parmList[l][0]) &&
        (matchCrit[1] == parmList[l][1]) &&
        (matchCrit[2] == parmList[l][2]) &&
        (matchCrit[3] == "999") ) {


          UltraEdit.outputWindow.write("HIT: InvoiceLines=[ sellerPartNumber="+matchCrit[0]+", partNumberDescription="+matchCrit[1]+", sellerMfrCode="+matchCrit[2]+", buyerPartNumber="+matchCrit[3]+" ]");

          invoiceLines[j].edins::BuyerPartNumber = parmList[l][3];
          xmlDocChange = true;
          UltraEdit.outputWindow.write("NEW: buyerPartNumber="+invoiceLines[j].edins::BuyerPartNumber);
          break parmListLoop;
        }
      }
    }

  }
}

// rewrite XML into editor
function rewriteInvoiceXml(UEdoc,NAInvoiceList) {
  var LF = "\n"; /* line feed */
  var sepLF = ""; /* seperate xml docs */

  for (var i=0;i<NAInvoiceList.length;i++) {
    UEdoc.write(sepLF+"<?xml version=\"1.0\" encoding=\"UTF-8\"?>"+LF);

    UEdoc.write(NAInvoiceList[i].toXMLString());

    sepLF = LF;
  }
}

// This function reads all parms from csvDoc and adds them to an array
function getParmList() {
  var parmList = new Array();

  // Define all the default find options we want
  csvDoc.findReplace.mode=0;
  csvDoc.findReplace.searchDown=true;
  csvDoc.findReplace.searchInColumn=false;
  csvDoc.findReplace.matchCase=false;
  csvDoc.findReplace.matchWord=false;
  csvDoc.findReplace.preserveCase=false;
  csvDoc.findReplace.regExp=true;
  csvDoc.findReplace.replaceAll=false;

  // Move cursor in current file to top of the file.
  csvDoc.top();

  //34352229018,NIF Part,XBO,237
  //(         ),(      ),( ),( )

  // regexp to match parms:
  var reParmMatch = /^([^,]+),([^,]+),([^,]+),(\d+)/;

  // Loop while finding parms:
  csvDoc.findReplace.find(reParmMatch.toUEregexp());
  while (csvDoc.isFound()) {
    // retrieve found text:
    var csvSelection = csvDoc.selection;

    // parse found text into variables using a destructuring assignment:
    var [ , sellerPartNumber, partNumberDescription, sellerMfrCode, buyerPartNumber] = reParmMatch.exec(csvSelection);

    // Push parm set to array:
    parmList.push( [sellerPartNumber, partNumberDescription, sellerMfrCode, buyerPartNumber] );

    // find next:
    csvDoc.findReplace.find(reParmMatch.toUEregexp());
  }

  // return parm array
  return parmList;
}


// This function reads all NAInvoices from xmlDoc and adds them to an array
function getNAInvoiceList() {
  var NAinvoiceList = new Array();

  // Define all the default find options we want
  xmlDoc.findReplace.mode=0;
  xmlDoc.findReplace.searchDown=true;
  xmlDoc.findReplace.searchInColumn=false;
  xmlDoc.findReplace.matchCase=false;
  xmlDoc.findReplace.matchWord=false;
  xmlDoc.findReplace.preserveCase=false;
  xmlDoc.findReplace.regExp=false;
  xmlDoc.findReplace.replaceAll=false;

  // Move cursor in current file to top of the file.
  xmlDoc.top();

  // Locate start of xml document:
  xmlDoc.findReplace.find("<?xml ");
  if (! xmlDoc.isFound() ) { /* not containing any XML docs */
    return NAinvoiceList; /* drop out with empty array */
  }

  while (true) {
    // is there more than one XML doc ?
    xmlDoc.findReplace.find("<?xml ");

    if(xmlDoc.isFound()) { /* lift found XML doc out */
      xmlDoc.selectToTop();
      // Extract found XML as XML object and pust to array using function getInvoiceXMLObject
      NAinvoiceList.push(getInvoiceXMLObject(xmlDoc.selection.substr(0,xmlDoc.selection.length - 6)));
      // remove the xml document
      xmlDoc.deleteText();
      // re-write path of next doc that was "cut off"
      xmlDoc.write("<?xml ");
    }
    else { /* last one or only one */
      xmlDoc.selectAll();
      // Extract found XML as XML object and pust to array using function getInvoiceXMLObject
      NAinvoiceList.push(getInvoiceXMLObject(xmlDoc.selection));
      // remove the xml document
      xmlDoc.deleteText();
      break;
    }
  }

  // return all NAinvoices
  return NAinvoiceList;
}

// Extract xml and return it as an XML object
function getInvoiceXMLObject(pmInvoiceXml) {

  // Simple regular expression to match
  // <?xml..?><!DOCTYPE..><root>...</root>
  var parsedDoc = /(<\?xml[^?]+\?>)?(<!DOCTYPE[^>]+>)?([^ÿ]*)/.exec(pmInvoiceXml);

  // Parsing was succesful
  if (parsedDoc) {
    // assign using "destructuring assignment", new in JavaScript 1.7
    var [, xmlVersion, xmlDoctype, invoiceXml] = parsedDoc;

    // Try and create a XML object from the document contents:
    try {
      var xmlRoot = new XML( invoiceXml );
    }
    catch (xmlException) {
      UltraEdit.messageBox("Document is not valid XML: "+invoiceXml.substr(0,30));
      xmlRoot = null;
      return;
    }

    return xmlRoot;
  }
  return new XML("<dummy></dummy>"); /* error in parsing, return a dummy xml object */
}


/* getDocumentIndex(filepath) */
/* If called without parameter = Find the tab index of the active document */
/* If called with parameter "filepath" = Find the tab index of that document. -1 = document not found */

function getDocumentIndex(filepath) {
  var tabindex = -1; /* start value */

  for (i = 0; i < UltraEdit.document.length; i++)
  {
    if(filepath) {
      if (UltraEdit.document[i].path.toLowerCase().indexOf(filepath.toLowerCase())!=-1) {
        tabindex = i;
        break;
      }
    }
    else {
      if (UltraEdit.activeDocument.path==UltraEdit.document[i].path) {
        tabindex = i;
        break;
      }
    }
  }
  return tabindex;
}

Further enhancement of the script could include more extensive error handling/reporting.

Feel free to ask questions and point out errors. Have fun!

wacreekm · Aug 07, 2008#52008-08-07T20:55+00:00

Jorrasdk,
Thank you!! You are a coding machine!
I'll get started testing it and let you know the results.
Thanks again!

Aug 08, 2008#62008-08-08T01:08+00:00

jorrasdk,
I ran the script using the test xml and parms files I supplied for a sample. Of course I renamed them using the names supplied in the script, placed them in the C: Temp folder. I'm getting the error below? Any help would be appreciated.

Code: Select all

Running script: C:\Documents and Settings\wacreekm\Desktop\UltraEdit_Script_999_Replace\999_Replace.js
========================================================================================================
Parms=[ sellerPartNumber=41-601, partNumberDescription=SPARK PLUG, sellerMfrCode=AC, buyerPartNumber=222 ]
Parms=[ sellerPartNumber=41-602, partNumberDescription=SPARK PLUG, sellerMfrCode=AC, buyerPartNumber=222 ]
Parms=[ sellerPartNumber=34352229018, partNumberDescription=NIF Part, sellerMfrCode=XBO, buyerPartNumber=237 ]
Parms=[ sellerPartNumber=INV PR DIFF, partNumberDescription=NIF Part, sellerMfrCode=XBO, buyerPartNumber=222 ]
Parms=[ sellerPartNumber=1275602, partNumberDescription=NIF Part, sellerMfrCode=WPC, buyerPartNumber=222 ]
An error occurred on line 131:
      var invoiceLines = NAInvoiceList[i].edins::NAInvoiceLine;
Script failed.

The popup shows ""Document is not valid XML: <"

Looks like it is having trouble with the parsing at :

Code: Select all

// Simple regular expression to match
  // <?xml..?><!DOCTYPE..><root>...</root>
  var parsedDoc = /(<\?xml[^?]+\?>)?(<!DOCTYPE[^>]+>)?([^ÿ]*)/.exec(pmInvoiceXml);

  // Parsing was succesful
  if (parsedDoc) {
    // assign using "destructuring assignment", new in JavaScript 1.7
    var [, xmlVersion, xmlDoctype, invoiceXml] = parsedDoc;

    // Try and create a XML object from the document contents:
    try {
      var xmlRoot = new XML( invoiceXml );
    }
    catch (xmlException) {
      UltraEdit.messageBox("Document is not valid XML: "+invoiceXml.substr(0,30));
      xmlRoot = null;
      return;

In these xml documents the first line is always:
<?xml version="1.0" encoding="UTF-8"?>

Followed by:
<edi:NAInvoice xmlns:edi="http://www.carquest.com/edi">

Is that non standard XML or something?
Does the "var parsedDoc" regular expression need to be changed you think?

There is a wierd character in the regular expression:
var parsedDoc = /(<\?xml[^?]+\?>)?(<!DOCTYPE[^>]+>)?([^ÿ]*)/.exec(pmInvoiceXml);

I think it is a non ASCII character that got into the code by mistake. But now I'm trying to figure out the correct character to us in place of it.

I've tried several options for this reg expression. I'm thinking the last part of the reg exp is to read in the root tag of the xml object. In this case that is "NAInvoice" but even when I get the script to run with out the error, no changes occur to the xml file. No 999 corrections.

jorrasdk · Aug 08, 2008#72008-08-08T06:21+00:00

Hi, I need to first establish your version of UE? (UE13 or UE14 - and what minor version?)

I think it has something to do with the XML file being UTF-8 (as the XML version identifier says). And as I recall scripting in UE13 had some issues with UTF-8 files because of the double byte characters. Try running a conversion from the file menu from UTF-8 to ASCII to see if the script then works. Of course this is not the permanent solution, but it would be nice to know.

I use a negated character set [^ÿ] with the weird character which is less likely to appear anywhere in your XML. [^ÿ] means match anything but ÿ.

wacreekm · Aug 08, 2008#82008-08-08T11:23+00:00

jorrasdk,
I'm running UE 13.20a+1 version.
I ran the conversion the on the XML file, and that did it.
At first glance, looks to be working.
Thank you very much!

Aug 15, 2008#92008-08-15T15:57+00:00

jorrasdk,
I've been using the script, it works great.
Thank you!

How hard would it be to modifiy this script to cut out an NAInvoice document based upon the <edi:CorporateInvoiceNumber> number provided in a list of Corporate Invoice numbers? I'm thinking the list would be a text file with a New Line after each Invoice Number.
The script only needs to read in the invoice number from the list, find the NAInvoice with that invoice number in the <edi:CorporateInvoiceNumber> tag and select the entire NAInvoice for that invoice number and delete it from the file. Then go to the next invoice number. When the invoice list is exhausted, the xml file will still have all the NAInvoice's minus the ones deleted out from the invoice list.

What do you think? I spent several hours trying to modify this one to do that but seems a little over my head still.
Can you help me out?

Any help would be great.
Thank you.

jorrasdk · Aug 18, 2008#102008-08-18T08:46+00:00

Ok, new script: My advantage is that I operate on "small" test files so performance isn't a problem for me. So I have made a slightly different version of my original script although a faster script might be possible based only on find with regexp.

I read a text file with invoice numbers, one on each line. Iterates through all XML documents and deletes those matching the invoice number. Deleted invoices are written to a seperate file. I hope I have included enough comments in the code to make it readable. Otherwise ask again in this thread.

Code: Select all

// Script to remove XML documents based on invoice numbers from a text file
// Released into public domain.

// ---------------------------------------------
// Define file paths
var txtParmFilePath = "C:\\temp\\carquest.txt";

var xmlFilePath = "C:\\temp\\carquest.xml";
var xmlDocNew = false; /* true = write changed xml to new file, false = overwrite old file */

var xmlDeleteFilePath = "C:\\temp\\carquest-deleted.xml";

// ---------------------------------------------
// Get index of parm file if it's already open in editor
var txtDocIx = getDocumentIndex(txtParmFilePath);
var txtDocPreOpened = true; /* until proven otherwise */
// if not open, then open it
if (txtDocIx == -1) {
  txtDocPreOpened = false;
  UltraEdit.open(txtParmFilePath);
  // and get the docIx
  txtDocIx = getDocumentIndex(txtParmFilePath);
}

// ---------------------------------------------
// Get index of xml file if it's already open in editor
var xmlDocIx = getDocumentIndex(xmlFilePath);
var xmlDocPreOpened = true; /* until proven otherwise */
var xmlDocChange = false; /* signal changes to XML need to be rewritten into editor */
// if not open, then open it
if (xmlDocIx == -1) {
  xmlDocPreOpened = false;
  UltraEdit.open(xmlFilePath);
  // and get the docIx
  xmlDocIx = getDocumentIndex(xmlFilePath);
}

// ---------------------------------------------
// Misc options for global XML object:
// http://developer.mozilla.org/en/docs/E4X_Tutorial:The_global_XML_object
XML.ignoreComments = false;
XML.ignoreProcessingInstructions = false;
XML.ignoreWhitespace = true;
XML.prettyPrinting = true;
XML.prettyIndent = 2;

// ---------------------------------------------
// Define working environment for UltraEdit
UltraEdit.perlReOn();
UltraEdit.insertMode();
UltraEdit.columnModeOff();

// ---------------------------------------------
// Helper prototype: remove starting and ending / as UE does not recognize these for regexp
RegExp.prototype.toUEregexp = function() { return this.toString().replace(/^\/|\/$/g, ""); };

// ---------------------------------------------
// Setup global "short names" for UltraEdit documents
if ((txtDocIx != -1) && (xmlDocIx != -1)) {
  var txtDoc = UltraEdit.document[txtDocIx];
  var xmlDoc = UltraEdit.document[xmlDocIx];

  // Start main processing of script:
  deleteXmlDocuments();
}

// ---------------------------------------------
// close documents that was not open from start when script is ending:
if ((txtDocPreOpened == false) && (txtDocIx != -1)) UltraEdit.closeFile(txtDoc.path,2);
if ((xmlDocPreOpened == false) && (xmlDocIx != -1)) UltraEdit.closeFile(xmlDoc.path,2);

// ---------------------------------------------
// Main script function
function deleteXmlDocuments() {

  // Read parm file with parms for invoiceHeader to be updated
  var parmList = getParmList();

  // all invoices in XML is going to be extracted to an array of XML document objects:
  var NAInvoiceList = getNAInvoiceList();

  // Empty array to be filled with deleted invoices
  var NAInvoiceListDeleted = new Array();

  // Dump retrieved parms to UltraEdit output window for trace purposes (can be removed for performance later)
  if (parmList.length > 0) { /* parms found */
    dumpParms(parmList);
  }

  // Dump xml doc info to UltraEdit output window for trace purposes (can be removed for performance later)
  if (NAInvoiceList.length > 0) { /* Invoices found */
    dumpInvoices(NAInvoiceList);
  }

  // Now process all invoice XML docs and match agains parms
  processInvoiceXmlDocs(parmList, NAInvoiceList, NAInvoiceListDeleted);

  // If at least one xml doc was changed, recreate whole document from xml invoice array:
  if (xmlDocChange==true) {
    if (xmlDocNew==true) { /* create new file */
      UltraEdit.newFile();
      var newDoc = UltraEdit.document[UltraEdit.document.length - 1];
      rewriteInvoiceXml(newDoc,NAInvoiceList);
    }
    else { /* overwrite */
      rewriteInvoiceXml(xmlDoc,NAInvoiceList);
      if (xmlDocPreOpened==false) { /* not open in editor, then save first */
        xmlDoc.setActive();
        UltraEdit.save();
      }
    }

    if (NAInvoiceListDeleted.length > 0) { /* any deleted XML docs ? */
      // Write deleted invoices to file:
      UltraEdit.newFile();
      var newDocDeleted = UltraEdit.document[UltraEdit.document.length - 1];
      rewriteInvoiceXml(newDocDeleted,NAInvoiceListDeleted);
      newDocDeleted.setActive();
      UltraEdit.saveAs(xmlDeleteFilePath);
      UltraEdit.closeFile(newDocDeleted.path,2);
    }
  }
  else { /* no changes */
    if (xmlDocPreOpened==true) { /* opened in editor, then re-read */
      UltraEdit.closeFile(xmlFilePath,2);
      UltraEdit.open(xmlFilePath);
    }
  }
}

// Simple function for trace purposes to dump parms read from file:
function dumpParms(parmList) {
  for (var i=0;i<parmList.length;i++) {
    UltraEdit.outputWindow.write("corporateInvoiceNumber to be deleted="+parmList[i]);
  }
}

// Simple function for trace purposes to dump info from xml docs:
function dumpInvoices(NAInvoiceList) {
  for (var i=0;i<NAInvoiceList.length;i++) {

    // register namespace:
    var edins = new Namespace('http://www.carquest.com/edi');

    // Get Xmllist of invoiceHeader
    var invoiceHeader = NAInvoiceList[i].edins::NAInvoiceHeader;

    for (j in invoiceHeader) {
      // Get <edi:CorporateInvoiceNumber>
      var corporateInvoiceNumber = invoiceHeader[j].edins::CorporateInvoiceNumber;

      UltraEdit.outputWindow.write("Invoices: invoiceHeader=[ corporateInvoiceNumber="+corporateInvoiceNumber+" ]");
    }

  }
}

// Iterate through invoice XML docs and match agains parms
function processInvoiceXmlDocs(parmList, NAInvoiceList, NAInvoiceListDeleted) {
  var i = 0;
  while (i<NAInvoiceList.length) {
    var deleteIx = false;

    // register namespace:
    var edins = new Namespace('http://www.carquest.com/edi');

    // Get Xmllist of invoiceHeader
    var invoiceHeader = NAInvoiceList[i].edins::NAInvoiceHeader;

    for (j in invoiceHeader) {
      // Get <edi:CorporateInvoiceNumber>
      var corporateInvoiceNumber = invoiceHeader[j].edins::CorporateInvoiceNumber;
      UltraEdit.outputWindow.write("processing corporateInvoiceNumber=[ "+corporateInvoiceNumber+" ]");

      parmListLoop:
      for (l in parmList) {
        UltraEdit.outputWindow.write("checking against parmList["+l+"]=[ "+parmList[l]+" ]");

        if ( corporateInvoiceNumber == parmList[l] ) {
          UltraEdit.outputWindow.write("DELETING: invoiceHeader=[ corporateInvoiceNumber="+corporateInvoiceNumber+" ]");

          NAInvoiceListDeleted.push(NAInvoiceList[i]);

          xmlDocChange = true;
          deleteIx = true; /* delete this invoice */
          break parmListLoop;
        }
      }
    }

    if (deleteIx === true) {
      NAInvoiceList.splice(i,1); /* delete this item from array */
    }
    else {
      i++; /* next item */
    }
  }
}

// rewrite XML into editor
function rewriteInvoiceXml(UEdoc,NAInvoiceList) {
  var LF = "\n"; /* line feed */
  var sepLF = ""; /* seperate xml docs */

  for (var i=0;i<NAInvoiceList.length;i++) {
    UEdoc.write(sepLF+"<?xml version=\"1.0\" encoding=\"UTF-8\"?>"+LF);

    UEdoc.write(NAInvoiceList[i].toXMLString());

    sepLF = LF;
  }
}

// This function reads all parms from txtDoc and adds them to an array
function getParmList() {
  var parmList = new Array();

  // Define all the default find options we want
  txtDoc.findReplace.mode=0;
  txtDoc.findReplace.searchDown=true;
  txtDoc.findReplace.searchInColumn=false;
  txtDoc.findReplace.matchCase=false;
  txtDoc.findReplace.matchWord=false;
  txtDoc.findReplace.preserveCase=false;
  txtDoc.findReplace.regExp=true;
  txtDoc.findReplace.replaceAll=false;

  // Move cursor in current file to top of the file.
  txtDoc.top();

  // regexp to match invoice numbers like "2988-ID-73806":
  var reParmMatch = new RegExp("^(\\d+-ID-\\d+)");

  // Loop while finding parms:
  txtDoc.findReplace.find(reParmMatch.toUEregexp());
  while (txtDoc.isFound()) {
    // retrieve found text:
    var txtSelection = txtDoc.selection;

    // parse found text into variables using a destructuring assignment:
    var [ , corporateInvoiceNumber] = reParmMatch.exec(txtSelection);

    // Push parm to array:
    parmList.push( corporateInvoiceNumber );

    // find next:
    txtDoc.findReplace.find(reParmMatch.toUEregexp());
  }

  // return parm array
  return parmList;
}


// This function reads all NAInvoices from xmlDoc and adds them to an array
function getNAInvoiceList() {
  var NAinvoiceList = new Array();

  // Define all the default find options we want
  xmlDoc.findReplace.mode=0;
  xmlDoc.findReplace.searchDown=true;
  xmlDoc.findReplace.searchInColumn=false;
  xmlDoc.findReplace.matchCase=false;
  xmlDoc.findReplace.matchWord=false;
  xmlDoc.findReplace.preserveCase=false;
  xmlDoc.findReplace.regExp=false;
  xmlDoc.findReplace.replaceAll=false;

  // Move cursor in current file to top of the file.
  xmlDoc.top();

  // Locate start of xml document:
  xmlDoc.findReplace.find("<?xml ");
  if (! xmlDoc.isFound() ) { /* not containing any XML docs */
    return NAinvoiceList; /* drop out with empty array */
  }

  while (true) {
    // is there more than one XML doc ?
    xmlDoc.findReplace.find("<?xml ");

    if(xmlDoc.isFound()) { /* lift found XML doc out */
      xmlDoc.selectToTop();
      // Extract found XML as XML object and pust to array using function getInvoiceXMLObject
      NAinvoiceList.push(getInvoiceXMLObject(xmlDoc.selection.substr(0,xmlDoc.selection.length - 6)));
      // remove the xml document
      xmlDoc.deleteText();
      // re-write path of next doc that was "cut off"
      xmlDoc.write("<?xml ");
    }
    else { /* last one or only one */
      xmlDoc.selectAll();
      // Extract found XML as XML object and pust to array using function getInvoiceXMLObject
      NAinvoiceList.push(getInvoiceXMLObject(xmlDoc.selection));
      // remove the xml document
      xmlDoc.deleteText();
      break;
    }
  }

  // return all NAinvoices
  return NAinvoiceList;
}

// Extract xml and return it as an XML object
function getInvoiceXMLObject(pmInvoiceXml) {

  // Simple regular expression to match
  // <?xml..?><!DOCTYPE..><root>...</root>
  var parsedDoc = /(<\?xml[^?]+\?>)?(<!DOCTYPE[^>]+>)?([^ÿ]*)/.exec(pmInvoiceXml);

  // Parsing was succesful
  if (parsedDoc) {
    // assign using "destructuring assignment", new in JavaScript 1.7
    var [, xmlVersion, xmlDoctype, invoiceXml] = parsedDoc;

    // Try and create a XML object from the document contents:
    try {
      var xmlRoot = new XML( invoiceXml );
    }
    catch (xmlException) {
      UltraEdit.messageBox("Document is not valid XML: "+invoiceXml.substr(0,30));
      xmlRoot = null;
      return;
    }

    return xmlRoot;
  }
  return new XML("<dummy></dummy>"); /* error in parsing, return a dummy xml object */
}


/* getDocumentIndex(filepath) */
/* If called without parameter = Find the tab index of the active document */
/* If called with parameter "filepath" = Find the tab index of that document. -1 = document not found */

function getDocumentIndex(filepath) {
  var tabindex = -1; /* start value */

  for (i = 0; i < UltraEdit.document.length; i++)
  {
    if(filepath) {
      if (UltraEdit.document[i].path.toLowerCase().indexOf(filepath.toLowerCase())!=-1) {
        tabindex = i;
        break;
      }
    }
    else {
      if (UltraEdit.activeDocument.path==UltraEdit.document[i].path) {
        tabindex = i;
        break;
      }
    }
  }
  return tabindex;
}

Parameter file with invoice numbers should look like:

Code: Select all

2988-ID-73806
12966-ID-1145

wacreekm · Aug 18, 2008#112008-08-18T14:16+00:00

Thank you jorrasdk!
I will run it and let you know how it goes.

Much appreciated!
wcreekm

Aug 18, 2008#122008-08-18T16:06+00:00

jorrasdk,
The remove invoice script works great.
However when searching the parms list for corporate invoice numbers to be removed, besides having the invoice numbers
in this format; 11161-ID-74826, 1531-ID-58396
They can also be in these formats; 1623-IC-110951, 7984-IC-19067, 123456.

Can a regular expression encompass all of these formats or do we need more than one regular expression?

The one in the script now is:

// regexp to match invoice numbers like "2988-ID-73806":
var reParmMatch = new RegExp("^(\\d+-ID-\\d+)");

Thanks for your help!

wacreekm

jorrasdk · Aug 18, 2008#132008-08-18T21:24+00:00

As I were reworking my script I didn't want to change too much. That's why I still use this design with the Regexp.

Just use a more "tolerant" Regexp like:

// regexp to match invoice numbers like "2988-ID-73806":
var reParmMatch = /^([\w-]+)/;

(basically: Match any word characters and dash.