Dec

Make a Print Catalog from Scratch Using Your Own Product Data — Part 4

Tim

Excel, Export, Import, Your Product Data

Excel is useful for bulk editing of product data for print catalogs

This article is the fourth in a series that explains how to enter product data for making print catalogs. Catalog-on-Demand provides a tool for this purpose. It is called Private Data Editor (PDE).

Please read Part 1, Part 2, and Part 3 first.

This article is about using Excel for bulk editing of your product data. It is very convenient to use Excel in conjunction with PDE for managing hundreds or thousands of rows of product data. You can make minor edits in PDE. When you have a lot of products to edit, then it is easiest to: (a) use PDE to export the products to Excel; (b) edit them in Excel; and (c) use PDE to import back from Excel. We have customers managing thousands of products in this way. It is also a fast way to add new products. This is especially true when you are copying data over from other spreadsheets or other sources.

Using PDE to export to Excel and to import from Excel is straightforward. You should have Private Data Editor (PDE) open.

How to Export to Excel from Private Data Editor (PDE)

Click on the Import/Export tab
Click on the Data File Export tab (it is selected by default)
For Catalog project, select General.
For Output format, select MS Excel workbook/sheet (.xls)
For Attributes, select Separate attribute columns
Do NOT check Include Project Files (this will be covered in an advanced topic)
Click Execute
After a moment, a dialog will appear

Click on the Export report link
Confirm there are no error messages
Click on the Exported DataFile link
Save the file with any file name – be sure to append the .xls extension

You can now open the file in Excel

Tips for Editing Your Product Data in Excel

There is one row for each item
Columns A through I relate to item-level information
The remaining columns are product-level information
Product information must be identical for each item row that belongs to the product
We strongly recommend that you use Excel to work on one set of data at a time, as follows:
a) Export your data to Excel
b) Identify the rows you will work on (typically all the products in one category)
c) Delete all other rows
d) Edit as needed
e) Import the edited rows
Attributes are exported into columns as label1, value1, etc. for both items (prefix ia) and products (prefix pa). You can rearrange the order of the attributes. For ease of editing, you might want to make the sequence the same for all products within a category.
The category column uses the #$# delimiter between each level. For example Apparel#$#Jackets#$#Wool

How to Import from Excel to Private Data Editor (PDE)

Save your spreadsheet in Excel 97-2003 Workbook format (only the first tab will be used for import)
Open up PDE
Click on the Import/Export tab
Click on the Data File Import tab
For Existing catalog project, select General
For New catalog project name, leave blank
Browse to select your Excel file for import
For Mode, select Update data (default setting)
Click Execute, then OK
A progress bar will appear, then a dialog with a link to an Import report
Review the Import report for error messages
Spot check your data via PDE, to make sure the import worked properly