Import your Cafepress Shop Data into your own SQL Database

I own a Cafepress Premium shop called HexxWear and I have been working on optimizing my shop for mobile web and smartphones. My mobile site is not yet ready for purchasing product from it but I do have featured products displayed with links to them in the main desktop version of my shop.

As with most web development one of the keys to owning a website is ensuring that there is as little manual maintenance as possible. This is done by developing or integrating dynamic elements on your page by means of scripts or other software.

Previously, on my HexxWear mobile site, I had to manually post my five featured products. This approach is not very convenient. So I decided to add PHP into the mix  to display random featured products. In order to do that I needed a database of my products that the PHP can pull from. Cafepress, as far as I know, will most likely not provide me with a database dump of my shop products (no one has answered my question in the forum yet).  So how can I obtain the necessary data from my shop that I can import into my own SQL database?

The answer to this question is not quick and simple and takes a little time to cleanup prior to importing. However, I have proven that it is possible using the combination of a third party tool called podToolbox, Microsoft Excel (or equivilent spreadsheet application), basic text editor (i.e. notepad, wordpad). There may be quicker procedures for obtaining your cpshop data, but I could not find one aside from the third party tool cpshop. I may look into that later, but from what I saw from the sample sites they are not mobile compliant.

This Instructable will be broken up into four parts. My procedure was derived from a number of different tutorials available on the web. I am combining and sharing what I learned into one spot targeting Cafepress shop owners.

Part 1: Using podToolbox
Part 2: Extract your cpFeeder .zip and Clean up Using Microsoft Excel
Part 3: Create Your SQL Database and Table
Part 4: Use PHP to Randomly Display Products from SQL Database

Step 1 Using podToolbox

I only recently learned about podToolbox but it apparently has been around for a while and was originally called cafeToolbox. In a nutshell, it is designed to extract your premium shop data and create a .txt file for feeds which are compatible with Google Base (now called Google Merchant Center). You would upload this .txt file to Google Base and it would display items from your shop feed (.txt file) upon relevant search results in the Google Market. This tool is free to use but you have to register.

• Register at http://www.podtoolbox.com
  • When registering you will need your Cafepress shop ID.
  • This works for basic and premium shops
  • You will receive a confirmation email with a link to activate
• Log into your account at http://www.podtoolbox.com
• On the left menu under Cafepress Tools, click on Add Shop.
  • You are instructed to enter your Cafepress Shop ID  ( http://cafepress.com/ )
• Under Cafepress Tools, click on Request Feed.
  • You will reach the cpFeeder Request form
• Fill out the cpFeeder Request form and click the Submit Request to Queue button.

At this point your request will be submitted and the process of extracting your shop data will begin.

***NOTE: This process can take quite a while depending on how large your shop is, how many items it contains. You have to exercise patience and once the process is complete you will receive an email with the .zip file of your feed.

Your cpFeeder file will contain all of your shop data such as id, product, description, price, size, etc.

Comments

[admin]

This is a great Instructable, but you need to add a main image of the final project to the intro step. Please do that and leave me a message when you have so that we can publish your work. Thanks!

[hexxamillion]

I added an image as requested. Thanks!