Export to csv format

05-31-2008, 05:27 AM

Hi,

I want to know if "export data to csv format" feature is already available in Storefront? I am using Storefront 7.0.2.1 and will use SQL Server 2005.

If yes, then how is to be used? It will be also useful if you could provide me info for where should I look for code.

Thanks for the info in advance.

06-03-2008, 08:58 AM

Yes, it's available. The following data is exported:

ProductID
VariantID
KitItemID
ProductName
KitGroup
SKU
SKUSuffix
MaufacturerPartNumber
Cost
MSRP
Price
SalePrice
Inventory

01-06-2010, 09:27 AM

Is there a way to add description onto this? I've had serious issues trying to install WSI and all I want is to add description to this list.

01-06-2010, 10:38 AM

There are a couple different ways you can do this. To automatically hook this up in the export from the admin section you'll have to do two things. First you'll need to modify the aspdnsf_ExportProductlist stored procedure to return the product description (simple enough, just add p.Description to the 2 select statements (just make sure to account for the Description in the 2 Unions with just a simple blank quote - '')). For example (new stuff in RED):

Code:

SELECT p.ProductID, 
       pv.VariantID, 
       '' KitItemID,
       p.Name,
       p.Description, 
       0 KitGroupID,
       '' KitGroup,
       isnull(p.SKU, '') SKU,
      ...
      ...
      ...
WHERE p.deleted = 0 and pv.deleted = 0 
UNION ALL
SELECT p.ProductID, 
       pv.VariantID, 
       convert(varchar(10), KitItemID) KitItemID,
       k.Name,
       '', 
       kg.KitGroupID,
       kg.name,
       '',
       '',

Then you'll need to modify the Admin/XmlPackages/ProductPricingExportCSV.xslt file to use the description. Add the header

Code:

<xsl:template match="root">
<xsl:comment><xsl:value-of select="'RecordType'"/>,</xsl:comment>
<xsl:value-of select="'ProductID (do not edit)'"/>,<xsl:value-of select="'VariantID (do not edit)'"/>,<xsl:value-of select="'KitItemID'"/>,<xsl:value-of select="'ProductName'"/>,<xsl:value-of select="'ProductDescription'"/>,<xsl:value-of select="'KitGroup'"/>

then add the param to account for multiple locales

Code:

<xsl:template match="product">
      <xsl:param name="pName">
          <xsl:choose>
              <xsl:when test="Name/ml">
                  <xsl:value-of select="Name/ml/locale[@name=$locale]"/>
              </xsl:when>
              <xsl:otherwise>
                  <xsl:value-of select="Name"/>
              </xsl:otherwise>
          </xsl:choose>
      </xsl:param>
      <xsl:param name="pDescription">
        <xsl:choose>
          <xsl:when test="Description/ml">
            <xsl:value-of select="Description/ml/locale[@name=$locale]"/>
          </xsl:when>
          <xsl:otherwise>
            <xsl:value-of select="Description"/>
          </xsl:otherwise>
        </xsl:choose>
      </xsl:param>
      <xsl:variable name="pKitGroup">

then pull it out in the final select

Code:

<xsl:value-of select="ProductID"/>,<xsl:value-of select="VariantID"/>,<xsl:value-of select="KitItemID"/>,&quot;<xsl:value-of select="translate($pName, ',', '')"/>&quot;,&quot;<xsl:value-of select="translate($pDescription, ',', '')"/>&quot;,&quot;<xsl:value-of select="translate($pKitGroup, ',', '')"/>&quot;

As an alternative method, you could just open SQL Server Management Studio, write your own select statement to retrieve the data you wanted, execute the query (make sure you are using Results To Grid), then right click the results and do a Save Results As...it will prompt you to save the results as a CSV file

01-06-2010, 10:39 AM

What kind of issues are you having installing WSI?

01-07-2010, 07:34 AM

Hi George

Too many issues trying to install WSI to even begin listing. I got to the point where I installed WSI, copied all the files over and managed to connect fine. But then everytime I run I got a continual time out message. I tried to add #define WSE3 to the App_Code\ASPDNSFUsernameTokenManager.cs file but this made our website collapse and we tried to replicate this on a dev server but kept hitting this brick wall.

Were launching 5 new websites this year and I'm trying to convince all to stay with ASPDNSF but getting WSi to work would do this!

Anyway..I've got to the last point of your great advice.

The end of the file I have reads like this

<?xml version="1.0"?>






<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xslutput method="xml" version="1.0" omit-xml-declaration="yes" indent="yes" standalone="yes"/>
<xslaram name="locale"></xslaram>

<xsl:template match="root">
<productlist>
<xsl:apply-templates select="product" />
</productlist>
</xsl:template>

<xsl:template match="product">
<xslaram name="pName">
<xsl:choose>
<xsl:when test="Name/ml">
<xsl:value-of select="Name/ml/locale[@name=$locale]"/>
</xsl:when>
<xsltherwise>
<xsl:value-of select="Name"/>
</xsltherwise>
</xsl:choose>
</xslaram>
<xslaram name="pDescription">
<xsl:choose>
<xsl:when test="Description/ml">
<xsl:value-of select="Description/ml/locale[@name=$locale]"/>
</xsl:when>
<xsltherwise>
<xsl:value-of select="Description"/>
</xsltherwise>
</xsl:choose>
</xslaram>
<xsl:variable name="pKitGroup">
<xsl:choose>
<xsl:when test="KitGroup/ml">
<xsl:value-of select="KitGroup/ml/locale[@name=$locale]" />
</xsl:when>
<xsltherwise>
<xsl:value-of select="KitGroup" />
</xsltherwise>
</xsl:choose>
</xsl:variable>
<productvariant>
<xsl:copy-of select="ProductID"/>
<xsl:copy-of select="VariantID"/>
<xsl:copy-of select="KitItemID"/>
<Name>
<xsl:value-of select="$pName"/>
</Name>
<KitGroup>
<xsl:value-of select="$pKitGroup" />
</KitGroup>
<xsl:copy-of select="SKU"/>
<xsl:copy-of select="SKUSuffix"/>
<xsl:copy-of select="ManufacturerPartNumber"/>
<xsl:copy-of select="Cost"/>
<xsl:copy-of select="MSRP"/>
<xsl:copy-of select="Price"/>
<xsl:copy-of select="SalePrice"/>
<xsl:copy-of select="Inventory"/>
</productvariant>
</xsl:template>

<xsl:template match="kitgroup">
<xslaram name="pName">
<xsl:choose>
<xsl:when test="Name/ml">
<xsl:value-of select="Name/ml/locale[@name=$locale]"/>
</xsl:when>
<xsltherwise>
<xsl:value-of select="Name"/>
</xsltherwise>
</xsl:choose>
</xslaram>
<kitgroup>
<xsl:attribute name="KitGroupID"><xsl:value-of select="KitGroupID" /></xsl:attribute>
<xsl:attribute name="Name"><xsl:value-of select="$pName" /></xsl:attribute>
<xsl:apply-templates select="kititem" />
</kitgroup>
</xsl:template>

<xsl:template match="kititem">
<xslaram name="pName">
<xsl:choose>
<xsl:when test="Name/ml">
<xsl:value-of select="Name/ml/locale[@name=$locale]"/>
</xsl:when>
<xsltherwise>
<xsl:value-of select="Name"/>
</xsltherwise>
</xsl:choose>
</xslaram>
<kititem>
<xsl:copy-of select="KitItemID"/>
<Name><xsl:value-of select="$pName"/></Name>
<xsl:copy-of select="PriceDelta"/>
</kititem>
</xsl:template>

</xsl:stylesheet>

Can you please confirm the Description code that I need to add.

Cheers!

01-07-2010, 08:10 AM

As far as the WSI timeout, I'd need to see some specs on the machine and run a couple tests to see what's going on there (including the xml you're trying to pass and the result xml). I'd also be curious to see what happens if you try and browse directly to the service URL (don't post it here in the forums

) and try to execute a service call from there.

As far as the error with #define WSE3, my guess is that you don't have Microsofts Web Services Enhancements 3.0 installed on the machine (though I'd have to see the error after you've added that to confirm). If you'd like some help getting that up and running let us know.

Your file looks a tad different than what I have (it looks like you're using the Admin/XmlPackages/ProductPricingExport.xslt instead of Admin/XmlPackages/ProductPricingExportCSV.xslt). Here's the entire ProductPricingExportCSV.xslt file if you want to just copy/paste

Code:

<?xml version="1.0"?>
<!-- ###################################################################################################### -->
<!-- Copyright AspDotNetStorefront.com, 1995-2010.  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.                                                                   -->
<!-- ###################################################################################################### -->
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<xsl:output method="text" omit-xml-declaration="yes" indent="no" standalone="yes"/>
    <xsl:param name="locale"></xsl:param>

    <xsl:template match="root">
		<xsl:comment><xsl:value-of select="'RecordType'"/>,</xsl:comment>
		<xsl:value-of select="'ProductID (do not edit)'"/>,<xsl:value-of select="'VariantID (do not edit)'"/>,<xsl:value-of select="'KitItemID'"/>,<xsl:value-of select="'ProductName'"/>,<xsl:value-of select="'ProductDescription'"/>,<xsl:value-of select="'KitGroup'"/>,<xsl:value-of select="'SKU'"/>,<xsl:value-of select="'SKUSuffix'"/>,<xsl:value-of select="'MaufacturerPartNumber'"/>,<xsl:value-of select="'Cost'"/>,<xsl:value-of select="'MSRP'"/>,<xsl:value-of select="'Price'"/>,<xsl:value-of select="'SalePrice'"/>,<xsl:value-of select="'Inventory'"/><xsl:text>
</xsl:text>
		<xsl:apply-templates select="product" />
	</xsl:template>

	<xsl:template match="product">
    <xsl:param name="pName">
      <xsl:choose>
        <xsl:when test="Name/ml">
          <xsl:value-of select="Name/ml/locale[@name=$locale]"/>
        </xsl:when>
        <xsl:otherwise>
          <xsl:value-of select="Name"/>
        </xsl:otherwise>
      </xsl:choose>
    </xsl:param>
    <xsl:param name="pDescription">
      <xsl:choose>
        <xsl:when test="Description/ml">
          <xsl:value-of select="Description/ml/locale[@name=$locale]"/>
        </xsl:when>
        <xsl:otherwise>
          <xsl:value-of select="Description"/>
        </xsl:otherwise>
      </xsl:choose>
    </xsl:param>
    <xsl:variable name="pKitGroup">
      <xsl:choose>
        <xsl:when test="KitGroup/ml">
          <xsl:value-of select="KitGroup/ml/locale[@name=$locale]" />
        </xsl:when>
        <xsl:otherwise>
          <xsl:value-of select="KitGroup" />
        </xsl:otherwise>
      </xsl:choose>
    </xsl:variable>
    <xsl:value-of select="ProductID"/>,<xsl:value-of select="VariantID"/>,<xsl:value-of select="KitItemID"/>,&quot;<xsl:value-of select="translate($pName, ',', '')"/>&quot;,&quot;<xsl:value-of select="translate($pDescription, ',', '')"/>&quot;,&quot;<xsl:value-of select="translate($pKitGroup, ',', '')"/>&quot;,&quot;<xsl:value-of select="SKU"/>&quot;,&quot;<xsl:value-of select="SKUSuffix"/>&quot;,&quot;<xsl:value-of select="ManufacturerPartNumber"/>&quot;,<xsl:value-of select="Cost"/>,<xsl:value-of select="MSRP"/>,<xsl:value-of select="Price"/>,<xsl:value-of select="SalePrice"/>,<xsl:value-of select="Inventory"/><xsl:text>
</xsl:text>
  </xsl:template>
</xsl:stylesheet>

There are two <xsl:text></xsl:text> sections that I can't paste directly here. Make sure between those you have ampersand (Shift + 7)#13;ampersand (Shift + 7)#10; to correctly move the csv to a new line, eg. ~#13;~#10; (replace ~ with &);

01-07-2010, 09:04 AM

Hi

Cheers for the prompt reply.

Tried this but still only get in the csv export

ProductID
VariantID
KitItemID
ProductName
ProductDescription
KitGroup
SKU
SKUSuffix
MaufacturerPartNumber
Cost
MSRP
Price
SalePrice
Inventory

But it's ok as we have someone manually populating this

I'd rather try and focus on WSI. I started a thread before so maybe you can pick up some info from there (My comments are on Page 3 http://forums.aspdotnetstorefront.co...ad.php?t=12488)

We have WSE 3.0 installed on our server. Server details are

Windows 2003 Server SP2
Storefront version 7.1.1.0
Cannot use - http://www.********/ipx.asmx (No option to enter XML code)

So we are using C:\testsite\wwwroot\WSIEventSampleClient\WSIImport Tester\WindowsApplication1\bin\Debug\WindowsApplic ation1.exe

WSI - http://******.ip.asmx
Use WSI Authentication Token - No
Username - *******
Password - *******

Code trying to use

<AspDotNetStorefrontImport Verbose="false">

<Get Table="Orders" Name="NewOrders">

<XmlPackage>DumpOrder.xml.config</XmlPackage>

<OrderBy>OrderDate asc</OrderBy>

<Criteria IsNew="1"/>

</Get>

</AspDotNetStorefrontImport>

Result is

Unable to Connect to Remote Server

When we try this on the live site we get

System.Web.Services.Protocols.SoapHeaderException: Server unavailable, please try later ---> System.ApplicationException: WSE842: The service pipeline could not be created. ---> System.Configuration.ConfigurationErrorsException: WSE032: There was an error loading the microsoft.web.services3 configuration section. ---> System.Configuration.ConfigurationErrorsException: WSE040: Type AspDotNetStorefront.ASPDNSFUsernameTokenManager could not be loaded. Please check the configuration file. (C:\inetpub\wwwroot\Web\web.config line 192)
at System.Configuration.BaseConfigurationRecord.Evalu ateOne(String[] keys, SectionInput input, Boolean isTrusted, FactoryRecord factoryRecord, SectionRecord sectionRecord, Object parentResult)
at System.Configuration.BaseConfigurationRecord.Evalu ate(FactoryRecord factoryRecord, SectionRecord sectionRecord, Object parentResult, Boolean getLkg, Boolean getRuntimeObject, Object& result, Object& resultRuntimeObject)
at System.Configuration.BaseConfigurationRecord.GetSe ctionRecursive(String configKey, Boolean getLkg, Boolean checkPermission, Boolean getRuntimeObject, Boolean requestIsHere, Object& result, Object& resultRuntimeObject)
at System.Configuration.BaseConfigurationRecord.GetSe ction(String configKey, Boolean getLkg, Boolean checkPermission)
at System.Configuration.BaseConfigurationRecord.GetSe ction(String configKey)
at System.Web.HttpContext.GetSection(String sectionName)
at Microsoft.Web.Services3.Configuration.WebServicesC onfiguration.get_Current()
--- End of inner exception stack trace ---
at Microsoft.Web.Services3.Configuration.WebServicesC onfiguration.get_Current()
at Microsoft.Web.Services3.Configuration.WebServicesC onfiguration.get_MessagingConfiguration()
at Microsoft.Web.Services3.WseProtocol.ModifyInitiali zedExtensions(PriorityGroup group, SoapExtension[] extensions)
at System.Web.Services.Protocols.SoapServerProtocol.I nitialize()
--- End of inner exception stack trace ---
--- End of inner exception stack trace ---

01-07-2010, 12:58 PM

Tried this but still only get in the csv export

ProductID
VariantID
KitItemID
ProductName
ProductDescription
KitGroup
SKU
SKUSuffix
MaufacturerPartNumber
Cost
MSRP
Price
SalePrice
Inventory

I thought you were looking for the product description, or are you saying that although the header record is there for product description it's not adding the description underneath? Did you make sure to also modify the stored procedure? Did you check to make sure you aren't using something like the product summary or the variant description for your item descriptions on your site?

I just grabbed a clean copy of 7110 and installed WSI from scratch and didn't run into any issues getting it working. Can you confirm that the following steps have been completed?

ipx.asmx and ipx.xml have been added to the root directory of the site, and the permissions are correct for the site to access them
The ipx.cs and ASPDNSFUsernameTokenManager.cs files have been added to the App_Code directory and have the correct permissions for the site to access them (you don't have to add the #define WSE3 stuff in your version...those directives were only necessary starting in 8.0)
You have uncommented the 4 sections in the web.config file related to WSI (they will look like <!-- WSI Web Service Interface (Section # of 4) Uncomment the following line to enable WSI Web Service Interface ... the last error you sent over leads me to believe one or more sections are still commented out, or that the permissions for the ASPDNSFUsernameTokenManager.cs file are not correct and the site can't access it)
You have installed Microsofts Web Services Enhancements 3.0 on the machine where your site is running (you already confirmed this...but never hurts to double check).

If you run into any issues confirming any of these, or just want to be sure, you can go through support (we won't charge a support incident...just reference this forums thread) and we can send you the files I have here locally that are functioning properly (you can just merge your encrypt key and dbconn information from your web.config file into the one where I've enabled all of the WSI functionality) and then we can at least rule out any issues with your installation or with the code.

01-08-2010, 02:20 AM

Hi

Many thanks for the reply.

I can confirm that I have amended the stored procedure and that all the steps have been completed.

I will raise a ticket and see if this solves the problem. many thanks again for all your help so far!

Thread: Export to csv format

Export to csv format