Thread: Edit Output For Export Price List To Excel

I was wondering if it was easy to add a few more fields to this export so looked around for the XML package controlling the export. Couldn't find it.

After deciding is must not be an XML package at all started looking around at other file types and didn't stumble across anything that looked like a suspect.

Where is this file?

If I'm not mistaken, this will involve updates to code + stored procedure...so not a quick update.

Is your goal to be able to export, update and reimport data into your cart?

I'm currently trying to build product feeds for Google Base and others. If I can get the system to export a few more fields (such as weight) then I can manually build the feeds using Excel.

It seems many people use WSI instead, but after reading the manual and searching the forum I can't make heads or tails of it. I guess the manual assumes you already know how to use WSI, where to find it and how to set it up. If you already know WSI well I'm sure the manual is very handy.

Hi

Sorry if this is a silly question but if you just need Googlebase/Amazon type feeds, why not use the standard product feed functionality? Admin -> Product -> Feeds.

Using XML Packages, these can be tailored to extract any fields you need and to generate any type of output.

With a bit more work they can even be run automatically using Windows Task scheduler every x hours. We use them for Google, Googlebase, Affiliate Window, Webgains etc.

Adam

Hondo69, you might be interested in this utility which is a very powerful alternative to WSI exporting/importing and all the other utilities running off the WSI: http://www.ecommercecartmods.com/p-1...torefront.aspx

You can use it to export all the product/variant/mapping data and also use it for importing and updating existing records! ;-)

1] Jan - I would be interested in your utility but a little hesitant to plunk down $400 without a trial version. But at this point I'm pretty desparate so might just roll the dice.

2] Adam - the XML Package feed interested me greatly early on, but that didn't last long. Out of the box a "sample" feed is already setup called feed.googlbase.xml.config. Seemed logical to take a crack at downloading the package and edit it to my specifications. I found some notes in the forum stating it was outdated (by a few years).

That's when the trouble started. Using FTP, I searched folders on the server for such a package and found none. I did, however, find a similar file called feed.nextopia.xml.config located under the XML Packages folder. Well, OK, I'll give that one a go.

In Admin, when creating or editing a feed it provides a dropdown list of existing feeds to select from. The nextopia feed wasn't on the list. So, in conclusion, I have no idea where the feed XML packages are stored, but I can't find them. Searching the forum and manual didn't provide any clues.

Surely it can't be this difficult.

Hi

You'll find all the Googlebase type feeds in your admin folder within XmlPackages. E.g. /admin/XmlPackages if you haven't renamed your admin.

Of course, turn the auto-ftp feature off while testing.

I agree that you shouldn't have to pay for a GoogleBase feed and it would be good if Vortx actually issued updates to these feeds regularly - as far as I know they haven't been changed for a while now.

It's a (relatively) straightforward exercise to update the 'old' Googlebase XML feed to the new format (but I would say that because I'm a developer!).

It's a bit more work to make feeds such as this run as scheduled tasks but not a huge problem and once you have them running automatically these feeds are a bit of a hidden gem, we use them for:

- Affiliate Window
- Amazon
- Google sitemaps
- Googlebase
- Webgains
- (useful) Affiliate feed for cancelled orders so that you don't pay affiliate commission on returned/cancelled orders.

etc.

Great tip! I found the file.
The folder must have been renamed by the installer.

Any idea how to setup a test feed that I can play with before going live? I've seen notes in the forum how some have the feed it to their browser, but no actual details on how they do it. It would be nice to put it through its paces before sending to Google or another feed destination.

It sounds as if you are working via FTP only rather than Remote Desktop... still possible of course but you'll be tearing your hair out each time you upload the edited XML file only to find that you're missing a character somewhere and need to edit, ftp and re-run it. Try get an RDP connection into the box and work directly if you can, or create a dev version of the site locally as it'll be much safer and make your life easier.

From the admin product->feeds page, make sure the auto-ftp is switched off first, then each time you run the feed, at the top of the page, you'll see the filename that has been generated. This file is placed within the /images directory of your site so you can browse it easily. e.g:

www.mysite.com/images/filenameofthefeed

Adam

I should have better explained my normal steps for importing:
1] Start with Example file in Excel and populate
2] Save file as Excel 2003 version
3] In Admin, use the Products -> Import Products function


Thanks for the tip on generating a file in the images folder for testing. I was missing the step of switching off auto-ftp.

Sure, Hondo69 email me, Jan, at jsimacek@compunix.us and I will send you a demo...

I think I got in trouble early on by making some basic assumptions. We operate about a dozed web sites using various software packages, and most of these sites are shopping carts. All have a process, in one form or another, of importing and exporting.

Each has their own pro's and con's and some are a major pain. One in particular takes me about an hour of hand manipluation in Excel to create a Google feed. It really hacks me off. And I can't edit the code that drives the export. Though I'm not a devloper I can get around pretty well in ASP, PHP, etc. so usually have few problems.

But now that I've tried ASPDNSF I'm a whole other world. I've entered a rabbit hole where up is down, white is black.

Call me crazy (I'm beginning to doubt my own sanity so it's OK) but isn't importing and exporting a fundamental requirement? I keep having this dream where I bought a new car and went to pick it up and it's sitting on jacks. The salesman then walks around the corner smiling, "Isn't she a beaut?"

After inquiring about the missing tires and wheels he says, "Oh, you wanted wheels with that. Let's see, that'll be another $200 if you want to actually drive the thing".

So past analogies that don't fully compare to a learning curve. What exact issue are you having with using the Google base feed? Is it just an issue of trying to add a few more fields like the weight? If that is the case I can give an xmlpackage example that adds the weight with not much issue.

That would be great.

I figured if I ever came up with a decent XML package for Google I'd just posts it online for everyone to see. Of course, I'll have to figure out the code required to tweak the XML, but I'm guessing I can work through that in short order.

I'm am going to just give the example xml package that just has the weight field added at this point then. Due note that this is not add the weight from the RTShipping.PackageExtraWeight appconfig. Here is the example code:

C#/VB.NET Code:

<?xml version="1.0" encoding="utf-8" ?>
<!-- ###################################################################################################### -->
<!-- Copyright AspDotNetStorefront.com, 1995-2011.  All Rights Reserved.                                    -->
<!-- http://www.aspdotnetstorefront.com                                                                        -->
<!-- For details on this license please visit  the product homepage at the URL above.                        -->
<!-- THE ABOVE NOTICE MUST REMAIN INTACT.                                                                   -->
<!-- ###################################################################################################### -->
<package displayname="Google Base RSS 2.0 Feed" version="2.1" debug="false" includeentityhelper="false">
    <query name="Products" rowElementName="Product">
        <sql>
        <![CDATA[
            SELECT temp.*
            FROM
            (
            select p.productid, p.name, isnull(pv.name, '') VariantName, p.description, p.sename, p.ImageFileNameOverride, p.SKU, isnull(p.FroogleDescription, '') ProductFroogleDescription, p.SEKeywords,
                p.ManufacturerPartNumber, pv.price, isnull(pv.saleprice, 0) saleprice, isnull(pv.FroogleDescription, '') VariantFroogleDescription, isnull(pv.description, '') VariantDescr, pv.Condition,
                ROW_NUMBER() OVER (ORDER BY p.productid) AS ROW, pv.Weight
            from dbo.product p 
                join dbo.productvariant pv on p.productid = pv.productid
                left join (select variantid, sum(quan) inventory from dbo.inventory group by variantid) i on pv.variantid = i.variantid
            where p.IsSystem=0 
                and p.deleted = 0 
                and p.published = 1 
                and p.ExcludeFromPriceFeeds = 0 
                and pv.isdefault = 1
                and case p.TrackInventoryBySizeAndColor when 1 then isnull(i.inventory, 0) else pv.inventory end >= @HideProductsWithLessThanThisInventoryLevel
            ) AS temp
            WHERE temp.ROW >= @LowerBound AND temp.ROW <= @UpperBound and ProductID IN (SELECT DISTINCT ProductID from ProductStore WHERE StoreID = @SID)
        ]]>
        </sql>
        <queryparam paramname="@HideProductsWithLessThanThisInventoryLevel" paramtype="appconfig" requestparamname="HideProductsWithLessThanThisInventoryLevel" sqlDataType="int" defvalue="0"  validationpattern="" />
            <queryparam paramname="@LowerBound" paramtype="runtime" requestparamname="LowerBound" sqlDataType="int" defvalue="0"  validationpattern="" />
            <queryparam paramname="@UpperBound" paramtype="runtime" requestparamname="UpperBound" sqlDataType="int" defvalue="0"  validationpattern="" />
        <queryparam paramname="@SID"    paramtype="runtime" requestparamname="SID"    sqlDataType="int" defvalue="2"  validationpattern="" />
      </query>

    <query name="StoreSite" rowElementName="Store">
      <sql>
        <![CDATA[
                  SELECT TOP 1 * FROM Store WHERE StoreID = @SID
              ]]>
      </sql>
      <queryparam paramname="@SID" paramtype="runtime" requestparamname="SID" sqlDataType="int" defvalue="2" validationpattern="" />
    </query>

  <PackageTransform>
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:aspdnsf="urn:aspdnsf"   xmlns:g="http://base.google.com/ns/1.0" exclude-result-prefixes="aspdnsf">
      <xsl:output method="xml" omit-xml-declaration="no" encoding="utf-8" indent="no" />

      <xsl:param name="EntityName">
        <xsl:value-of select="/root/QueryString/entity" />
      </xsl:param>

      <xsl:param name="ShowInGrid">true</xsl:param>
      <xsl:param name="GridCols">4</xsl:param>
      <xsl:param name="UseDescr" select="aspdnsf:AppConfig(@SID, 'Froogle_UseDescriptionsIfFroogleDescEmpty')" />

      <xsl:param name="LocaleSetting" select="/root/Runtime/LocaleSetting" />
      <xsl:param name="WebConfigLocaleSetting" select="/root/Runtime/WebConfigLocaleSetting" />

      <xsl:template match="/">
        <rss version="2.0">
          <channel>
            <title>
              <xsl:value-of select="/root/StoreSite/Store/Name" />
            </title>
            <link>
              <xsl:value-of select="/root/StoreSite/Store/ProductionURI"/>
            </link>
            <description>
              <xsl:value-of select="aspdnsf:AppConfig(@SID, 'RSSChannelDescr')" />
            </description>
            <language>
              <xsl:value-of select="$WebConfigLocaleSetting" />
            </language>
            <pubDate>
              <xsl:value-of select="/root/System/CurrentDateTime" />
            </pubDate>
            <lastBuildDate>
              <xsl:value-of select="/root/System/CurrentDateTime" />
            </lastBuildDate>
            <xsl:apply-templates select="/root/Products/Product" />
          </channel>
        </rss>
      </xsl:template>

      <xsl:template match="Product">
        <xsl:param name="ProductName" select="aspdnsf:GetMLValue(name)"></xsl:param>
        <xsl:param name="ProductVariantName" select="aspdnsf:GetMLValue(VariantName)"></xsl:param>
        <xsl:param name="ProductDescr" select="aspdnsf:GetMLValue(description)"></xsl:param>
        <xsl:param name="VariantDescr" select="aspdnsf:GetMLValue(VariantDescr)"></xsl:param>
        <xsl:param name="ProductFroogleDescription" select="aspdnsf:GetMLValue(ProductFroogleDescription)"></xsl:param>
        <xsl:param name="VariantFroogleDescription" select="aspdnsf:GetMLValue(VariantFroogleDescription)"></xsl:param>
        <xsl:param name="ItemImage" select="aspdnsf:ProductImageUrl(productid, ImageFileNameOverride, SKU, 'medium', 1)"></xsl:param>
        <xsl:param name="ManufacturerPartNumber" select="aspdnsf:GetMLValue(ManufacturerPartNumber)"></xsl:param>
        <xsl:param name="StoreURL" select="/root/StoreSite/Store/ProductionURI"></xsl:param>
        <item>
          <title>
            <xsl:value-of select="$ProductName" />
          </title>
          <link>
            <xsl:value-of select="$StoreURL" />
            <xsl:value-of select="aspdnsf:ProductLink(productid, sename, 0, '')" />
          </link>
          <description>
            <xsl:choose>
              <xsl:when test="$VariantFroogleDescription!=''">
                <xsl:value-of select="$VariantFroogleDescription" />
              </xsl:when>
              <xsl:when test="$ProductFroogleDescription!=''">
                <xsl:value-of select="$ProductFroogleDescription" />
              </xsl:when>
              <xsl:when test="$VariantDescr!=''">
                <xsl:value-of select="$VariantDescr" />
              </xsl:when>
              <xsl:otherwise>
                <xsl:value-of select="$ProductDescr" />
              </xsl:otherwise>
            </xsl:choose>
          </description>
          <g:image_link>
            <xsl:if test="not(contains($ItemImage, 'nopicture'))">
              <xsl:value-of select="$ItemImage" />
            </xsl:if>
          </g:image_link>
          <g:price>
            <xsl:choose>
              <xsl:when test="number(saleprice)=0">
                <xsl:value-of select="format-number(price, '###0.00')" />
              </xsl:when>
              <xsl:otherwise>
                <xsl:value-of select="format-number(saleprice, '###0.00')" />
              </xsl:otherwise>
            </xsl:choose>
          </g:price>
          <g:mpn>
            <xsl:value-of select="$ManufacturerPartNumber" />
          </g:mpn>
            <g:condition>
                <xsl:choose>
                    <xsl:when test="number(Condition)=0">
                        New
                    </xsl:when>
                    <xsl:when test="number(Condition)=1">
                        Used
                    </xsl:when>
                    <xsl:otherwise>
                        Refurbished
                    </xsl:otherwise>
                </xsl:choose>
            </g:condition>
            <g:shipping_weight>
                <xsl:value-of select="Weight" />
            </g:shipping_weight>  
        </item>
      </xsl:template>
    </xsl:stylesheet>
  </PackageTransform>
</package>

In this case this is really small modification to the xmlpackage. It comes down to adding the weight field to the sql statement and then outputting the newly added weight field in xslt.