Import your Cafepress Shop Data into your own SQL Database

Step 2 Extract your cpFeeder .zip and Clean up Using Microsoft Excel

In this part you will extract your cpFeeder .zip file then copy and paste its contents into an Excel spreadsheet for cleanup. The cpFeeder file will contain all of your shop data such as product name, description, price, size and more. I used Microsoft Excel but you should be able to perform this on other equivalents such as OpenOffice Calc.

The reason you need to ‘clean up’ the file is because it will contain characters (i.e.- $, // etc) and other data that may be interpreted differently by the SQL database. I chose to use a spreadsheet to perform this cleanup because it displays data from the text file in table format. I also chose to edit with a spreadsheet because you can perform  Find and Replace to speed up the cleanup process. The last reason I chose to use a spreadsheet is because I can then export it as a .csv comma separated value). This file type also make importing into your database table less hassle.

• Extract your cpFeeder .zip file
  • It should have a .txt extension
• Open your blank Excel workbook (or equivalent)
• Ensure you only have one worksheet.
  • By default if you open a new Excel workbook it will consist of three worksheets.
  • Delete the other two worksheets by right-clicking on the worksheet tab and select delete.
• Copy and paste the .txt file contents into your Spreadsheet.
  • The contents should display nicely in table format.

Clean up Using Excel:

This subsection will contain the items you need to look for in your spreadsheet that requires clean up. You need to remove and/or replace certain characters that can be interpreted as a function or ignored by the SQL database. You use the Find and Replace feature in your spreadsheet to perform this task quicker. I will make suggestions based off of my experience, however you may have a different criteria for your data.

• In Microsoft Excel: Edit > Find (CTRL-F)
• Click the Options button to display a full dialog box of options.
• Under the Find tab, enter your search text
• Select the check box ‘Match entire cell contents’
• Under the Replace tab, enter your text in the Replace with field. ( The Find what: field should auto populate using the text you entered from the Find tab.)
• If you have numerous results then you will want to select Replace ALL rather than Replace.

Suggested data elements you need to search for in your spreadsheet to replace or remove.

• http:// -You need to search for hyper link prefixes and replace with www
• ” -You need to search for quotes after numbers indicating inches in size and replace them with the abbreviation in. , inch or the word inches.
• Replace quotes in your descriptions with the HTML ASCII code "
• Search for commas and replace with a space.
  • For example- Medium, Large, X-Large, 2X-Large
  • Replace with- Medium Large  X-Large 2X-Large
  • Remove or replace commas with space in your product descriptions
  • This needs to be done because you will export your spreadsheet as a .csv and the commas will be interpreted as the end a line.
• Locate any blank cells under columns and type the word NULL.
  • Unfortunately there is no short way that I can find to perform this.
  • All of my NULL values were located under the Size and Material columns
  • Your data may differ based on what products your shop contains
  • A blank cell will be interpreted as no data and will end up shifting your other row data over resulting with cells being under the wrong column. (i.e.- prices for some rows may end up under the size column rather than the price column.)

This will be the most time consuming part depending on the size of your shop and how much product you have. Hopefully this does not have to be done often

Export your spreadsheet

Ensure that you only have one spreadsheet in your workbook consisting of the spreadsheet for your shop.

• In Microsoft Excel, File > Save As
• Save as Type: CSV (Comma delimited)(*.csv)