Cactushop Export for Shopzilla

[gingham743]

Hi Everybody,

I am trying unsuccessfully to bulk upload into Shopzilla (Bizrate). I use Cacushop for my cart/business website, which has a feature for exporting .csv data, however, I am having trouble finding the relevant strings/code to obtain the details I need to fulfil Shopzilla's criteria.

I can create a froogle export from Cacushop, however the info does not contain SKU's, which is an essential requirement.

Example, the following string exports details of all orders from cactushop: -

-
SELECT C_ID, C_EmailAddress, C_CardholderName, C_ML_SignupDateTime, C_ML_SignupIP, C_LanguageID FROM tblCactuShop1Customers WHERE C_ML_SendMail='y'
C_ID, C_EmailAddress, C_CardholderName, C_ML_SignupDateTime, C_ML_SignupIP, C_LanguageID
n, s, s, s, s, s
comma
doublequote

-

Does anybody know the Cactushop strings that would allow me to export the info below: -

Category, Manufacturer, Title, Description, Link, Image, SKU, Quantity, Condition, Shipping Weight, Shipping Costs, Bid, Promo Text, UPC, Price

Thank you in advance, Jinnett

[collateit]

Hi Jinnett,

I'm sure I can help with CactuShop extract but I don't think you'll be able to extract the exact data you require for a couple of reasons:

1. The image URL is not stored in the database. It is derived by the system at the point at which it required to be displayed on screen, therefore you can't just produce a report on it. Basically when you add a product to Cactushop it gives it a unique identifier in the database - this is also how it renames the images as you upload them. Unfortunately your image may be one of several different file types e.g .jpg, .gif, .png etc so Cactushop has a little bit of code which says I'm displaying product 12345 now go and find what images named 12345.something exist for this.

2. The shipping cost would be dependent on how many items were purchased and how your shipping is calculated - by weight or order value.

Are these fields optional or required. If they're required then you have 2 choices either extract what data you can (I can help with this) or develop a custom extract script purely for Shopzilla.

I do a LOT of custom Cactushop development for my clients so I'm sure we can sort something out.

[ashleycameron]

Thanks for your information..
It has been really very helpful to me.

[collateit]

Hi Jinnett,

I've just managed to get round and have a look at this. Based upon some code I took from the froogle export and modified a bit you could run this query:

SELECT DISTINCT
p.P_ID, CAST(p.P_Desc1 AS nvarchar(4000)) AS P_Desc1, p.P_Name1, MIN(v.V_Price) AS MinPrice, MIN(c.CAT_Name1) AS FirstofCatName1,
v.V_Name1, s.SUP_Name, v.V_CodeNumber, v.V_Weight, v.V_Quantity
FROM tblCactuShop1Categories AS c INNER JOIN
tblCactuShop1ProductCategoryLink AS pcl ON c.CAT_ID = pcl.PCAT_CategoryID INNER JOIN
tblCactuShop1Products AS p ON pcl.PCAT_ProductID = p.P_ID INNER JOIN
tblCactuShop1Versions AS v ON p.P_ID = v.V_ProductID LEFT OUTER JOIN
tblCactuShop1Suppliers AS s ON p.P_SupplierID = s.SUP_ID
WHERE (p.P_Name1 <> '') AND (v.V_Live = 'y') AND (p.P_Live = 'y') AND (c.CAT_Live = 'y') AND (s.SUP_Live <> 'n' OR
s.SUP_Live IS NULL)
GROUP BY p.P_ID, CAST(p.P_Desc1 AS nvarchar(4000)), p.P_Name1, v.V_Name1, s.SUP_Name, v.V_CodeNumber, v.V_Weight, v.V_Quantity
ORDER BY p.P_Name1 (you will need to put this query onto a single line by removing all the line breaks) this is assuming your running Microsoft SQL server database. If your running Access or MySQL then slightly different. This will give you everything apart from image URL and product URL. If you would like further information on this then let me know.


Rest of export would then be:

n,s,s,n,s,s,s,s,n,n
comma
doublequote

[gingham743]

Thanks Stuart, I have just read your reply, I will have a look and see if I can make this work.