Export all products and properties from uCommerce

Update: I have just published an updated version of this script which works for more recent versions of Ucommerce and also exports the category hierarchy which may be useful. You can find how to export all products and categories from Ucommerce here.

One of the most time consuming parts of building an e-commerce site is getting the data right and often it requires numerous exports.

Although uCommerce is generally awesome, mass updating product data is somewhat of a laborious task so we generally export the data to Excel and get the customers to update it there. We’ve got various import scripts to do that which we’ll share another time but today we thought we’d start by sharing a script that outputs all the products with their properties in a handy table.

You’ll need SQL Sever 2005+ I think to take advantage of the PIVOT function but the script will basically list all uCommerce products and have a separate column for each product property i.e. colour, size etc. It’s totally dynamic so it should work with any of your uCommerce databases (tested on 6+) without an issue.

Hopefully it’s of help/use to someone out there.

DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX);

SET @cols = STUFF((
            SELECT ',' + QUOTENAME(pdf.Name)
            FROM uCommerce_ProductDefinitionField AS pdf LEFT JOIN uCommerce_ProductDefinition pd ON pdf.ProductDefinitionId = pd.ProductDefinitionId
            WHERE pdf.Deleted='0' AND pd.Deleted='0'
            GROUP BY pdf.Name
            ORDER BY MIN(pdf.SortOrder) ASC
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),1,1,'')

SET @query = 'SELECT 
    pvt.ProductId
    ,    p.Sku
    ,    p.VariantSku
    ,    p.Name
    ,   pgp.Price
    ,    CASE p.DisplayOnSite WHEN ''1'' THEN ''Y'' ELSE ''N'' END AS [Show On Site]
    ,    CASE p.AllowOrdering WHEN ''1'' THEN ''Y'' ELSE ''N'' END AS [Allow Ordering]
    ,   def.Name AS [Product Type]
    ,    p.CreatedOn
    ,    p.ModifiedOn
    ,    pd.DisplayName
    ,    pd.ShortDescription
    ,    pd.LongDescription
    ,    p.Rating
    , ' + @cols + '
    FROM
    (
        SELECT
                pp.ProductId
            ,    ppdf.Name AS [PropertyName]
            ,    pp.Value AS [PropertyValue]
        FROM
            uCommerce_ProductProperty pp
                LEFT JOIN uCommerce_ProductDefinitionField ppdf ON pp.ProductDefinitionFieldId = ppdf.ProductDefinitionFieldId
        WHERE
            ppdf.Deleted = ''0''

    UNION ALL

        SELECT
                pd.ProductId
            ,    pdpdf.Name AS [PropertyName]
            ,    pdp.Value AS [PropertyValue]
        FROM
            uCommerce_ProductDescription pd
                LEFT JOIN uCommerce_ProductDescriptionProperty pdp ON pd.ProductDescriptionId = pdp.ProductDescriptionId
                LEFT JOIN uCommerce_ProductDefinitionField pdpdf ON pdp.ProductDefinitionFieldId = pdpdf.ProductDefinitionFieldId
        WHERE
            pdpdf.Deleted = ''0''
    ) AS x
    PIVOT 
    (
        MAX([PropertyValue])
        FOR [PropertyName] IN (' + @cols + ')
    ) AS pvt
        LEFT JOIN uCommerce_Product p ON pvt.ProductId = p.ProductId
        LEFT JOIN uCommerce_ProductDefinition def ON p.ProductDefinitionId = def.ProductDefinitionId
        LEFT JOIN uCommerce_ProductDescription pd ON p.ProductId = pd.ProductId
        LEFT JOIN uCommerce_PriceGroupPrice pgp ON p.ProductId = pgp.ProductId
        INNER JOIN uCommerce_PriceGroup pg ON pgp.PriceGroupId = pg.PriceGroupId
    WHERE
        def.Deleted = ''0''
        AND pg.Deleted = ''0''
    ORDER BY
        p.Sku
        , p.VariantSku
        , p.Name
'

PRINT(@query)
EXECUTE(@query);