Using CSV Files to Quickly Create Custom Catalogs
Why Should I Use a CSV File?
The CSV import is a feature in the Catalog Management system that enables you to import and manage catalog entries (such as Products, Variations/SKUs, Packages) in bulk using an excel spreadsheet (.csv format). It is an alternative to manually creating catalog entries directly in Commerce Manager, which can be tedious when dealing with a large amount of catalog data.
This feature enables you to accomplish these key tasks:
- Import your .csv spreadsheets and create new catalog entries in Commerce Manager.
- Set your catalog items into a desired structure of your choosing
- Create entry to associate one entry with others (e.g., /SKUs will appear within a or a )
- Edit or delete existing catalog items..
The CSV catalog import feature can be used for your entire catalog data, or just portions of them.
Here are the basic steps you can take to import and set up a new catalog using the CSV import feature. Before you start, choose an existing catalog folder or create a new one (in the Catalog Management system) to import the CSV files to.
- Create CSV data files (using Excel Spreadsheet, Notepad, etc.) with information about your , entries, and entry relations.
- Create necessary meta fields for the new catalog entries (in the Administration system).
- Upload the CSV data files to the CSV Import page.
- Create a mapping file for each CSV file. A mapping file is needed to link each column of data in a CSV file to the correct attributes in Commerce Manager.
- Import the CSV files and their corresponding mapping files into a specific catalog.
- Create entry relations to associate one entry with another (e.g., a Package and a Product, a Product and a Variation/, etc.).
Format for CSV Files
CSV files you create and import into Commerce Manager need to have a particular format.
- The first row should have headers that correspond to different attributes of the data you want to import (e.g., product name, price, description, image file location, etc.).
- There are a few attributes that are in your CSV file to correctly structure your catalog:
- Code: A unique code to identify each category or entry
- Name: A unique name for each row of data
- Entry Type (for entries only): Each row must contain one of the following five words in order for the Commerce Manager to correctly recognize its entry type --"Variation", "Product", "Package", "Bundle" or "Dynamic Package"
- Parent & Child Entry Code (for entry relations only): A CSV file containing these codes can specify relationships between entries (e.g., display different Variations within a Product).
Although , the following attribute is useful in creating a hierarchy in your catalog:
- Action: A command to specify the action that will be taken during the import. Choose one of the three options: Insert/Update/Delete or I/U/D.
Note: Please make sure to specify an appropriate command for each line of the CSV files. Entering "Insert" when that data already exist, or "Update" when no data exist will give an error during import and those line of data will not get successfully imported.
- Category Code (by comma): By assigning a category code to an entry, you can specify the category that you want to place your entry in.
Also please note this rule:
- : By design, this attribute that is present in both categories and entries must be unique for each item in your catalog. Importing files that contain more than one identical url will result in an error.
Different mapping types are used to create the mapping files mentioned in step 4 above.
- Category w/ Meta Data – select this type to create a mapping file for your category files.
- Entry w/ Meta Data – select this type to create a mapping file for all the entry files (i.e., Packages, Products, Variation/SKUs, Bundles, and Dynamic Packages).
- Entry Relation – select this type to create a mapping file for your entry relation file that specifies the relationships between entries such as many SKUs to one Product, or SKUs and Products to a Package.
- Entry Association – select this type when creating a mapping file for entry (Advertizing other items under titles such as "You may also be interested in...
- Variation w/ Inventory – select this type to create a mapping file to import your Pricing/Inventory data located in your Variation/SKU Edit screen.
- Sale Price – Select this type to create a mapping file for Sale Price file, which specifies different currencies for each item.
By selecting these options, the Commerce Manager will display different sets of Fields and Attributes for you to manually assign to each data column of your CSV file. Figure 8 and 9 below shows the display of the mapping files with the Category w/ Meta and Entry Relations Data type respectively. The items under Fields and Attributes with superscripts 1 and/or 2 are headers that are required attributes that must exist in the CSV files you are importing (1- required field for Insert, 2 – required field for Update).
Example: Importing and Setting up a New Catalog
The example below demonstrates how to use CSV import using the guidelines provided above.
Let's say you want to set up a "Baby Stuff" catalog with categories and entries as shown here in Figure 1.
Prerequisite: Create a new catalog
First, create a new catalog called "Baby Stuff" in the Catalog Management system.
Step 1: Create CSV files containing data for your new entries
Here you can download the sample CSV files that go with this example Sample Catalog CSV Files.
These files provide the base for the discussion in the following section.
The sample files contain the following:
CSV File Type 1 - Category File
This file specifies data for categories and/or subcategories within a Catalog.
The top line has column headers that will be matched with the Meta fields in the later steps. The headers can also have different names. As you will see in later steps, Category Code in column 1 is important since it specifies where the entry data will be stored. The code entered in under Parent Code in column 2 can be used to create subcategories. If the Parent Code is set to "null" or "," then that category will be created in the root level of a catalog. In this example, the "Infants" and "Toddlers" category will be created at the root level, while the "Diapering Supplies" category will be located under "Infants" as a subcategory.
CSV File Type 2 – Entry Files
These files specifies data for Catalog Entries. The Products, Variation/SKUS, and Packages CSV files for this example are shown in Figure 3, 4, and 5 respectively.
CSV File Type 3 – Entry Relations File
This file is used to link one entry to others. In this example, Var1 ~ Var5 (different diaper sizes) are associated to Prod1 "Diapers," Var9 ~ Var11 (different quantity for wipes) are associated with Prod2 "Wipes" and so forth. Also some variations are assigned to packages. Please note that the Prod, Var and Package codes MUST BE the codes from the entry CSV files (Figure 3, 4, and 5). If you include codes that are not defined in the entry CSV files, you will get an error while importing them into Commerce Manager in Step 4.
CSV file type 4 - Associations file
This file is used to create associations between different entries. In this example, Var 10 is associated to Var 1 as an Accessory, and Var 2 to Var 1 as a cross-sell item.
CSV file type 5 - Pricing/Inventory file
This file is used to specify the information for the Pricing/Inventory tab of each individual entry.
CSV file type 6 - Sale Price file
This file is used to specify the information for the Pricing section of the Pricing/Inventory page.
Step 2: Create necessary meta fields for the new catalog entries
For this example, no additional meta fields need to be created because the sample spreadsheet uses only the default attributes.
To create new meta fields follow these steps here.
- Go to the Administration system in Commerce Manager.
- Select the "Catalog System" folder and then "Meta Classes" located in the left navigation frame.
- Select Create New, then New Meta Class from its pull-down menu.
- Enter in a Name, Friendly Name (e.g., Baby Catalog), and some description (optional) for the meta class, and choose "Catalog Entry" for Entry Type.
- Click OK to save the new class.
- Next Select Create New, then New Meta Field from its pull-down menu.
- Enter in a Name, Friendly Name (e.g., Baby Diaper Size), and some optional description.
- Also Choose the data type for this meta field and other properties by clicking on the checkboxes.
- Click OK to save the new field.
- Repeat the steps to create all the necessary fields.
- Associate the new meta fields to the meta class you created.
- Select to "Meta Classes" node on the left navigation frame again.
- For "Element" choose "Catalog Entry" in the pull-down menu.
- Under "Type" choose your meta class (e.g., Baby Catalog) you created earlier.
- Select the desired meta fields from the list displayed at the bottom of the screen.
- Select OK to save the new meta class to fields association.
- You are done with setting up your meta fields.
Step 3: Upload the CSV data files to the CSV Import page
Follow these steps to prepare the CSV files for import:
- Select the Catalog Management system.
- In the upper-left detail area, click on Catalogs folder and you will see the Catalog List view in the main area.
- In the action area above the main view, click the Import/Export drop-down button and select CSV Import Catalog.
- You will now see the CSV Import Catalog page in the main screen. The screen contains two tabs 1) CSV Import and 2) Mapping File, which we will be talking about next.
CSV Import Tab
- In the "CSV Import" tab in the main view, click "Add New File."
- Browse for the CSV file you wish to import, then click "Upload File."
- When you get the "Uploaded File:" confirmation, click "Save the file."
- The CSV file you uploaded will be added to the "Files available for import:" list.
- Repeat the previous four steps for each CSV file you wish to upload.
Step 4: Create a mapping file for each CSV file
Now create a mapping file that will specify the data in the CSV file to its corresponding attributes in the Commerce Manager.
Mapping file tab
- Click the "Mapping file" tab in the main view. You will now see the "Mapping file" tab view of the CSV Import Catalog view as shown in Figure 11.
Edit existing mapping file section
- Load mapping file: This section is used to retrieve previously created and saved mapping files. Since there is no existing mapping file in this example, leave this section blank.
Meta Class, Language section
- Mapping Type*: Select "Category w/ Meta Data"
- Meta Class*: Select "Default Catalog Node"
- Language*: English
Data fle and CSV adjustment section
- Data File*: The drop down values presented here are dependent on the different CSV data files you uploaded on the "CSV Import" tab in step 2.
- For this example, first choose "BabyStuffCategory.csv."Leave the rest of the field as default and move on to the next section.
Fields and Attributes, Column headers in the data file, and Selected values section
The values presented under the "Fields and Attributes" column are representative of the "Meta Class" selected in the "MetaClass, Language" section above (which were either previously imported or created within Commerce Manager).
The values presented in the drop-down menus under the "Column headers in the data file" column are representative of the "Data file" selected in the "Data file and CSV adjustment" section above (which were previously imported above).
- Select the appropriate "Column header" drop-down value to "map" with the MetaClass "Fields and Attributes" to the left; the value selected will be reflected in the "Selected values" column to the right.
Save mapping file section
- Enter a file name for the "Enter file name" textbox. For this example, save the mapping files using the same CSV file names.
- Click the "Save" button. The browser returns to the CSV Import tab, and the mapping file in .XML format will be added to the list in the "Choose mapping file" section.
- Repeat Step 3 to create and save a mapping file for each CSV files you wish to import.
- For CSV Entry files, select the "Entry w/ Meta Data" option under "Mapping Type."
- For CSV Entry Relations file, select "Entry Relation".
- For CSV Associations file, select "Entry Association."
- For CSV Pricing/Inventory file, select "Variation w/ Inventory."
- For CSV Sale Pricing file, select "Sale Price."
- When all the mapping files are saved, your CSV Import tab screen should look something like this.
Step 5. Import the CSV files and their corresponding mapping files into a specific catalog
At the "CSV Import" tab view (Figure 12), do the following:
- Select and highlight the CSV file you wish to import in the "Files available for import" section. For this example, select BabyStuffCategories.csv.
- Select and highlight the mapping file that corresponds to the selected CSV file in the "Choose mapping file for import" section. For this example, select BabyStuffCategories.xml.
- Select the catalog in the "Catalog" drop-down into which you would like to import your CSV data. In this example choose "Baby Stuff".
- Click the "Start import" button. You will see the "Importing catalog" modal dialog box which will notify you of the percentage of completion as well as logging messages.
- Click "Close Window" at the bottom of the modal dialog to complete the operation.
- Repeat Step 4 to import all the remaining CSV & Mapping pairs.
Step 6. Verify Your Import
In the Catalog Management system, expand the "Baby Stuff" folder under Catalog and verify that the contents of your CSV file were imported into the appropriate catalog.
Version: EPiServer Commerce 1 R2 SP2| Last updated: 2012-09-20 | Copyright © EPiServer AB | Send feedback to us