Looking for a tool to simplify my life

[Kane234]

I am looking for a tool that will help automate my order process procedure. I get orders from Amazon and eBay in XML. I currently have a pretty good system in place but I feel that there is a way to automate it even more. Here are the steps currently go through.

1. I have a C# program the grabs the XML from Amazon and eBay
2. I then upload the XML to my web server
3. I navigate to the PHP Parsing file using $xml = simplexml_load_file($_GET["file"]); then

http://www.domainname.com/XMLParse.php?file=ordernumber.xml.

changing the order number for every order.
4. I then copy the result into an XML file
5. Then I save that file into my API in folder.

Is there a way that I can change that to something like this...

1. Use the C# to get the XML (currently is grabs all the orders and puts them into separate XML files in one folder)
2. Run "something" that goes through all the XML files in that folder, parses the XML into the format that my API can read, then saves the resulting file to my API in.

I am not at all looking for someone to do this for me. I just need a gentle nudge in the right direction. Are there any software packages that can help with this, should I try a BAT file...

Like I mentioned it is a pretty good system but if I start getting north of 50 or 60 orders a day it will be too cumbersome.

Thanks,
Kane

 

[k5tm]

It is unclear whether the format that API can read is XML, or some other format.

Since you are using C# and suggest BAT, I am assuming Windows.

XSLT is a tool that can transform XML into XML, or text (or HTML for that matter). It can also do a limited amount of computation. Certainly replacing order numbers is possible if there is some reasonable way to know what the new order number should be. Just a couple days ago, I supplied a mechanism for splitting out the orders from an Amazon XML feed. See thread426-1741596. It is not unreasonable to modify the second XSLT in that example to do everything you need - split out the order into separate file while adjusting the data for the API of the consuming application, and changing the order number.

So, can you be a bit more descriptive?

Tom Morrison
Hill Country Software

 

[Kane234]

Thank you for the reply.

The API does except XML and the procedure I am currently using works but I can see that it will get too cumbersome if orders increase. Here is a snippet of what I get from the program, the PHP I wrote to parse it and the output.

The C# gives me this,

<LineItemSKUList>
      <OrderLineItemItem xsi:type="OrderLineItemItemResponse">
        <LineItemType>SKU</LineItemType>
        <UnitPrice>61.9500</UnitPrice>
        <LineItemID>410486</LineItemID>
        <AllowNegativeQuantity>false</AllowNegativeQuantity>
        <Quantity>1</Quantity>
        <ItemSaleSource>AMAZON_US</ItemSaleSource>
        <SKU>CSAW6040</SKU>
        <Title>JVC Arsenal CS-AW6040 10" Dual 4ohm Subwoofer B001JT97NW</Title>
        <BuyerUserID>whatever@marketplace.amazon.com</BuyerUserID>
        <BuyerFeedbackRating>0</BuyerFeedbackRating>
        <SalesSourceID>19443127456538</SalesSourceID>
        <VATRate>0</VATRate>
        <TaxCost>0.0000</TaxCost>
        <ShippingCost>14.3900</ShippingCost>
        <ShippingTaxCost>0.0000</ShippingTaxCost>
        <GiftWrapCost>0.0000</GiftWrapCost>
        <GiftWrapTaxCost>0.0000</GiftWrapTaxCost>
        <GiftMessage />
        <GiftWrapLevel />
        <RecyclingFee>0.0000</RecyclingFee>
        <UnitWeight UnitOfMeasure="LB">13.2</UnitWeight>
        <WarehouseLocation />
        <UserName />
        <DistributionCenterCode>Rodney Building #104</DistributionCenterCode>
        <IsFBA>false</IsFBA>
      </OrderLineItemItem>
    </LineItemSKUList>

My PHP does this,

<?php

$xml = simplexml_load_file($_GET["file"]);
echo '	&lt;OrderDetail&gt;<br>';
foreach ($xml->ShoppingCart->LineItemSKUList->OrderLineItemItem as $OrderLineItemItem){
	echo '		&lt;LineItemInfo&gt;<br>';
	echo '			&lt;WarehouseID&gt;01&lt;/WarehouseID&gt;<br>';
	echo '			&lt;ItemNumber&gt;'  . $OrderLineItemItem->SKU .  '&lt;/ItemNumber&gt;<br>';
	echo '			&lt;OrderQty&gt;' . $OrderLineItemItem->Quantity . '&lt;/OrderQty&gt;<br>';
	echo '			&lt;ActualSellPrice&gt;' . $OrderLineItemItem->UnitPrice . '&lt;/ActualSellPrice&gt;<br>';
	echo '			&lt;Cost&gt;0.0&lt;/Cost&gt;<br>';
	echo '			&lt;ChargeType/&gt;<br>';
	echo '			&lt;DropShip/&gt;<br>';
	echo '			&lt;DueDate/&gt;<br>';
	echo '			&lt;ExtendedWeight&gt;0.0&lt;/ExtendedWeight&gt;<br>';
	echo '			&lt;ItemDescription1&gt;' . $OrderLineItemItem->Title . '&lt;/ItemDescription1&gt;<br>';
	echo '			&lt;ItemDescription2/&gt;<br>';
	echo '			&lt;ItemID&gt;0&lt;/ItemID&gt;<br>';
	echo '			&lt;LineItemType/&gt;<br>';
	echo '			&lt;ListPrice/&gt;<br>';
	echo '			&lt;NonStockFlag&gt;false&lt;/NonStockFlag&gt;<br>';
	echo '			&lt;SequenceNumber/&gt;<br>';
	echo '			&lt;ShipInstructionType&gt;false&lt;/ShipInstructionType&gt;<br>';
	echo '			&lt;UnitOfMeasure&gt;EA&lt;/UnitOfMeasure&gt;<br>';
	echo '		&lt;/LineItemInfo&gt;<br>';
}

and it comes out like this,

<OrderDetail>
<LineItemInfo>
<WarehouseID>01</WarehouseID>
<ItemNumber>CSAW6040</ItemNumber>
<OrderQty>1</OrderQty>
<ActualSellPrice>61.9500</ActualSellPrice>
<Cost>0.0</Cost>
<ChargeType/>
<DropShip/>
<DueDate/>
<ExtendedWeight>0.0</ExtendedWeight>
<ItemDescription1>JVC Arsenal CS-AW6040 10" Dual 4ohm Subwoofer B001JT97NW</ItemDescription1>
<ItemDescription2/>
<ItemID>0</ItemID>
<LineItemType/>
<ListPrice/>
<NonStockFlag>false</NonStockFlag>
<SequenceNumber/>
<ShipInstructionType>false</ShipInstructionType>
<UnitOfMeasure>EA</UnitOfMeasure>
</LineItemInfo>
</OrderDetail>

The XML is much larger that this but you can get the idea from here. The C# saves each order as the order number. So if I have 15 orders I have 15 files named with the perspective order number. What I would like to have happen is something to grab those 15 orders, parse them, then save them to another folder. It would probably have to move them after it saved them to keep the "working" folder with only unprocessed orders. Does that make more sense? Do you know of a software or process that would grab the XML files out of a folder, parse them, save the output to a different folder, then move the file to an archived folder?

Thanks again,
Kane

 

[k5tm]

Kane,

Have a look at the thread I referenced in my first reply. In particular download MSXSL.EXE.

Then, a small homework assignment, since I will not be able to do anything on this until tomorrow. I would ask that you peruse the XSLT tutorial at W3Schools starting here. I just want you to get an idea of the concepts, because your PHP script is essentially doing what XSLT can do for you.

We can combine XSLT to do the XML transform work with BAT/CMD, which is quite good at doing the file manipulations you describe in your last paragraph. You will soon be welcoming those days where there are hundreds of orders!


Tom Morrison
Hill Country Software

 

[k5tm]

Hi Kane,

I hope you had time to go look at the thread. I won't repeat the instructions for downloading MSXSL.EXE.

So, as I understand it, your C# program leaves you with a directory of XML files (presumed to have extension xml), so you need a mechanism to translate the order into the new order. Taking your PHP script, here is an XSLT that will do the identical transformation:

[small]<xsl:stylesheet version="1.0" xmlns:xsl="[URL unfurl="true"]http://www.w3.org/1999/XSL/Transform">[/URL]
<xsl:output indent="yes" encoding="utf-8"/>

<xsl:template match="/">
<OrderDetail>
	<xsl:apply-templates select="ShoppingCart/LineItemSKUList/OrderLineItemItem"/>
</OrderDetail>
</xsl:template>

<xsl:template match="OrderLineItemItem">
<LineItemInfo>
	<WarehouseID>01</WarehouseID>
	<ItemNumber><xsl:value-of select="SKU"/></ItemNumber>
	<OrderQty><xsl:value-of select="Quantity"/></OrderQty>
	<ActualSellPrice><xsl:value-of select="UnitPrice"/></ActualSellPrice>
	<Cost>0.0</Cost>
	<ChargeType/>
	<DropShip/>
	<DueDate/>
	<ExtendedWeight>0.0</ExtendedWeight>
	<ItemDescription1><xsl:value-of select="Title"/></ItemDescription1>
	<ItemDescription2/>
	<ItemID>0</ItemID>
	<LineItemType/>
	<ListPrice/>
	<NonStockFlag>false</NonStockFlag>
	<SequenceNumber/>
	<ShipInstructionType>false</ShipInstructionType>
	<UnitOfMeasure>EA</UnitOfMeasure>
</LineItemInfo>

</xsl:template>[/small]

You can apply this transform using MSXSL.EXE:

MSXSL.EXE [i]amazonorder[/i].xml TranslateShoppingCart.xsl -o [i]MyAPIOrder[/i].xml

Now, one way to run a command, or set of commands, for all the files in a directory is to use the FOR Windows CMD statement. Here it is in a simple BAT file that you should modify to meet your needs.

@SETLOCAL
@ECHO OFF
FOR %%I IN (*.xml) DO MSXSL.EXE %%I TranslateShoppingCart.xsl -o OutputDir\%%~nI-new.xml 
ENDLOCAL

The FOR ... IN (filespec) command allows you to process all the files in a directory meeting the filespec criterion. The variable %%I has the entire filename, and you can use %%~nI to get the file name (without extension), and %%~xI to get the extension (more here).

If you want to something more complex than a single line (as you describe in your final paragraph), then write a BAT file that takes the filename as a parameter. The batch file will be written to do all the tasks for a single file. Inside the BAT file, that parameter can be referred to as %1, and its file name (without extension) as %~n1, and the extension as %~x1. You would then call the batch file in the manner of

 FOR %%I IN (filespec) DO CMD /C MYBATCH.BAT %%I

Without the CMD /C, the batch file will not be run in a new process, causing premature termination.

Now, regarding substituting in that new order number - this will require a very simple change to the TranslateShoppingCart.xsl and the MSXSL.EXE command line. You say that the C# program "saves each order as the order number." This is convenient, since %~n1 returns just the file name. Let me know where the new order ID is to be injected in the output, and I will show you how.

I finish with my sample input and output for TranslateShoppingCart.xsl, based on your example above.

[small]<ShoppingCart>
   <LineItemSKUList>
      <OrderLineItemItem >
        <LineItemType>SKU</LineItemType>
        <UnitPrice>61.9500</UnitPrice>
        <LineItemID>410486</LineItemID>
        <AllowNegativeQuantity>false</AllowNegativeQuantity>
        <Quantity>1</Quantity>
        <ItemSaleSource>AMAZON_US</ItemSaleSource>
        <SKU>CSAW6040</SKU>
        <Title>JVC Arsenal CS-AW6040 10" Dual 4ohm Subwoofer B001JT97NW</Title>
        <BuyerUserID>whatever@marketplace.amazon.com</BuyerUserID>
        <BuyerFeedbackRating>0</BuyerFeedbackRating>
        <SalesSourceID>19443127456538</SalesSourceID>
        <VATRate>0</VATRate>
        <TaxCost>0.0000</TaxCost>
        <ShippingCost>14.3900</ShippingCost>
        <ShippingTaxCost>0.0000</ShippingTaxCost>
        <GiftWrapCost>0.0000</GiftWrapCost>
        <GiftWrapTaxCost>0.0000</GiftWrapTaxCost>
        <GiftMessage />
        <GiftWrapLevel />
        <RecyclingFee>0.0000</RecyclingFee>
        <UnitWeight UnitOfMeasure="LB">13.2</UnitWeight>
        <WarehouseLocation />
        <UserName />
        <DistributionCenterCode>Rodney Building #104</DistributionCenterCode>
        <IsFBA>false</IsFBA>
      </OrderLineItemItem>
      <OrderLineItemItem >
        <LineItemType>SKU</LineItemType>
        <UnitPrice>31.9500</UnitPrice>
        <LineItemID>410487</LineItemID>
        <AllowNegativeQuantity>false</AllowNegativeQuantity>
        <Quantity>1</Quantity>
        <ItemSaleSource>AMAZON_US</ItemSaleSource>
        <SKU>CSAW6040</SKU>
        <Title>a different speaker</Title>
        <BuyerUserID>whatever@marketplace.amazon.com</BuyerUserID>
        <BuyerFeedbackRating>0</BuyerFeedbackRating>
        <SalesSourceID>19443127456538</SalesSourceID>
        <VATRate>0</VATRate>
        <TaxCost>0.0000</TaxCost>
        <ShippingCost>5.9000</ShippingCost>
        <ShippingTaxCost>0.0000</ShippingTaxCost>
        <GiftWrapCost>0.0000</GiftWrapCost>
        <GiftWrapTaxCost>0.0000</GiftWrapTaxCost>
        <GiftMessage />
        <GiftWrapLevel />
        <RecyclingFee>0.0000</RecyclingFee>
        <UnitWeight UnitOfMeasure="LB">13.2</UnitWeight>
        <WarehouseLocation />
        <UserName />
        <DistributionCenterCode>Rodney Building #104</DistributionCenterCode>
        <IsFBA>false</IsFBA>
      </OrderLineItemItem>
    </LineItemSKUList>
</ShoppingCart>
[/small]

[small]<OrderDetail>
  <LineItemInfo>
    <WarehouseID>01</WarehouseID>
    <ItemNumber>CSAW6040</ItemNumber>
    <OrderQty>1</OrderQty>
    <ActualSellPrice>61.9500</ActualSellPrice>
    <Cost>0.0</Cost>
    <ChargeType/>
    <DropShip/>
    <DueDate/>
    <ExtendedWeight>0.0</ExtendedWeight>
    <ItemDescription1>JVC Arsenal CS-AW6040 10" Dual 4ohm Subwoofer B001JT97NW</ItemDescription1>
    <ItemDescription2/>
    <ItemID>0</ItemID>
    <LineItemType/>
    <ListPrice/>
    <NonStockFlag>false</NonStockFlag>
    <SequenceNumber/>
    <ShipInstructionType>false</ShipInstructionType>
    <UnitOfMeasure>EA</UnitOfMeasure>
  </LineItemInfo>
  <LineItemInfo>
    <WarehouseID>01</WarehouseID>
    <ItemNumber>CSAW6040</ItemNumber>
    <OrderQty>1</OrderQty>
    <ActualSellPrice>31.9500</ActualSellPrice>
    <Cost>0.0</Cost>
    <ChargeType/>
    <DropShip/>
    <DueDate/>
    <ExtendedWeight>0.0</ExtendedWeight>
    <ItemDescription1>a different speaker</ItemDescription1>
    <ItemDescription2/>
    <ItemID>0</ItemID>
    <LineItemType/>
    <ListPrice/>
    <NonStockFlag>false</NonStockFlag>
    <SequenceNumber/>
    <ShipInstructionType>false</ShipInstructionType>
    <UnitOfMeasure>EA</UnitOfMeasure>
  </LineItemInfo>
</OrderDetail>[/small]

I anticipate that you might have some questions...

Tom Morrison
Hill Country Software

 

[Kane234]

Tom,

Thank you so much for this. If things go well today I will have some time to devote to this project. I will let you know how it goes.

Thanks,
Kane

 

[ChrisHirst]

Why not let the server handle it all?


wget (or CURL) can fetch the XML from Amazon or ebay

sed and/or awk can manipulate and split the files into the format you need.



Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum