Using CSV Files to Quickly Create Custom Catalogs

h3. Why Should I Use a CSV File?

The CSV Catalog Import is a feature in the Catalog Management system that enables you to import and manage Catalog Entries (such as Products, Variation/SKUs, Packages) in bulk using a CSV file. It is an alternative to manually creating Catalog Entries directly in the ECF 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 formatted spreadsheets and create new catalog entries in ECF
\\
** *Note*: This feature is not all-or-nothing. You can either use the CSV import feature to do everything from scratch, or you can work with preexisting categories, and entries to update and/or add additional items.
* Set your Catalog items into a desired Category structure of your choosing
\\
* Create entry relations to associate one entry with others (e.g., Variation/SKUs will appear within a Product or a Package)
\\
* Edit or delete existing Catalog items
\\

The CSV Catalog Import feature can be used for your entire Catalog data, or just portions of them.
\\


h3. Import Overview


{color:#333333}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.{color}



* {color:#333333} Prerequisite: Manually create a new catalog in Catalog Management.{color}

# Create CSV data files (using Excel Spreadsheet, Notepad, etc.) with information about your Categories, 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 ECF.
# 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/SKU, etc.).


h3. Format for CSV Files

CSV files you create and import into the ECF Commerce Manager need to have a particular format.
* The first row should have headers that correspond to different attributes of the data, or a.k.a. _meta fields,_ you want to import (e.g., Product name, Price, Description, mage file location, etc.).
* There are a few attributes that are +required+ 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 *not required*, 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 exists 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:
** *SEO URL*: 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.


h3. Mapping Types

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 Associations (Advertising 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).




h1. Example: Importing and Setting up a New Catalog

The example below demonstrates how to use CSV import using the guidelines provided above.

For example,  you want to set up a "Baby Stuff" catalog with Categories and entries as shown here in Figure 1.
\\

!babycatalogstructure.jpg! Figure 1
\\


h3. *Prerequisite: Create a new catalog*

First, create a new catalog called "Baby Stuff" in the Catalog Management system.




h3. *Step 1: Create CSV files containing data for your new entries*

Here you can download the five sample CSV files that go with this example [Sample Catalog CSV Files|^BabyStuffSamplCSVCatalogFiles.zip].


The downloadable zip file above contains the following CSV files:


* BabyStuffCategories.csv
* BabyProducts.csv 
* BabyPackages.csv 
* BabyVariations.csv 
* BabyVariationInventoryPricing.csv
* BabyEntryRelations.csv
* BabyAssociations.csv
* BabySalePrice.csv

*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 "root," 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.


!csvcategories.png! Figure 2
\\
\\

*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.

!csvbabyproducts.png! Figure 3
\\
\\

*CSV File Type 3 -- Entry Relations File*

*CSV File Type 4 - Associations file*

*CSV File Type 5 - Pricing/Inventory file*

\\

*CSV File Type 6 - Sale Price file*

h3. *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. If you want to add additional columns in your CSV spreadsheet that are not included in the sample files, you may do so in step 1. In addition to that, however, you must also create the meta field in Commerce Manager. 

** 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.

h3. *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.
\\

!csvimporttab1.png!
Figure 10
\\
\\

h3. 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.



!csvbabymappingfiletab.png! Figure 11


*Edit existing mapping file section*
* *Load mapping file*: This 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 File and CSV adjustment section*
* *Data File*: The drop down menu will list the 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 displayed under the "Fields and Attributes" column reflects the meta fields of the "Meta Class" selected in the "MetaClass, Language" section above (which were either previously imported or created within Commerce Manager).
The values that appear in the drop-down menus under the "Column headers in the data file" column reflects the available columns in your CSV file specified in the "Data file" section 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.
* You may also choose the "<custom value>" option in the drop-down menu and use the new drop-down menu that appears on the left most column to override the values saved (or missing) in your CSV files. For example, if you choose this option for "Actions" attribute, then the new drop down menu will give you the options of "Insert," "Update," or "Delete."
!CSVCustomValueOption.png|border=1!


*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.
\\

!csvbabyimporttab2.png!&nbsp;Figure 12&nbsp;

h3. 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.


h3. 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.