The easiest way to format a file for import is to start by exporting a file from Merchandising that contains the assets you want to modify. You can also manually create the import file starting with a blank spreadsheet. Unlike the export feature, which supports only XLS files, the import feature supports both XLS and CSV formatted files, so you can use any editor that supports CSV files to create the spreadsheet to import.
The following figure shows an example of a spreadsheet formatted for import:
Caution: If you leave a cell blank, a property value might be deleted. See Format for Deleting Property Values.
Required Formats for Importing a Spreadsheet
The first row and first column of the spreadsheet are reserved for use by the import process and must contain specific information. The first row contains information about the data that is being imported and the first column (except for row 1) contains the ID property of the assets being imported.
In the first row of the spreadsheet, you must specify the repository, asset type, and any special formats for data types, as follows:
The first column of row one (cell A1) must contain the Nucleus path to the repository component and the asset type of the assets in the spreadsheet, as defined in the repository definition. For example:
/atg/commerce/catalog/ProductCatalog:productUse the additional columns in row one (cells B1 through D1) to specify any special property formatting, such as itemized collections or specific date formats. Each format must be in its own cell, but it does not have to appear in any particular order. The formatting options are:
Description
Format in Spreadsheet
Requirements
Itemized collection property
(see Options for Exporting Collection Properties)FORMAT=itemizedRequired when importing a map, list, or set property in itemized format.
Caution: If an itemized import file does not contain
FORMAT=itemized, then the import task overwrites the value of the collection property each time it encounters the next value. The last item in the itemized list for a collection will be the only item stored for that collection.Format of
dateproperty values, such as 01/31/2009DATEFORMAT=MM/dd/yyyyThe day, month, and year indicators can appear in any order to match your data, for example
dd/MM/yyyyRequired if any imported properties are of type
date.The date value must exactly match the format, for example
03/15/2009instead of3/15/09.Format of
timestampproperty values, such as 31/01/2009 22:45:01TIMEFORMAT=
MM/dd/yyyy HH:mm:ssThe day, month, and year indicators can appear in any order to match your data, for example,
dd/MM/yyyy HH:mm:ssTo use 12-hour time, the format is
MM/dd/yyyy hh:mm:ss aa, whereaacan be either AM or PM, for example03/15/2009 05:30:00 AMRequired if any imported properties are of type
timestamp.The timestamp value must exactly match the format, for example
03/15/2009 05:30:00instead of3/15/09 5:30.
Beginning with the second row, the first column of the spreadsheet is reserved for the ID property of the imported assets. No other property can occupy column one. The second row (cell A2) contains the ID heading, which is always ID regardless of the actual name of the repository item’s ID property. The remaining rows (cells A3 and greater) contain the ID values for each imported asset. Any rows with blank ID values are skipped during import.
Format for Adding New Assets
The import feature allows you to create new assets when you import your spreadsheet. To create an asset, add a row to the spreadsheet and enter property values for each column. If you leave a cell blank, then the value for that property is set to null. If you leave the ID column blank, the asset is skipped and not created.
You assign an ID value for the new asset in one of the following ways:
Enter
NEWin the ID column to let the system create an IDThe new asset is created using an automatically generated ID. The
NEWkeyword must be typed in all capital letters and must be the only value in the cell.Enter a unique ID in the ID column
A new asset is created with the ID you enter.
Note: To create an asset, you must provide values for required properties.
When you create a new asset for import, be sure to add it before any other assets in the spreadsheet that make use of that new asset. This is because the import task processes the spreadsheet from top to bottom and must create an asset before it can be referenced from another asset. For example, to create a new product and add it to the Related Products property of other products in the spreadsheet, insert the new product row before the rows containing the products that refer to it.
Format for Deleting Property Values
You can delete a property value of an asset when you import the asset to Merchandising. To delete a property value, leave the corresponding cell blank.
Tip: If you delete a cell in the spreadsheet but do not want to delete the property value during import, you can mark the cell as ignored. See Marking Data to Ignore.
This delete format does not apply when you are adding items to collection properties. If you format a column to prepend or append items to a collection, blank cells are automatically ignored during import (nothing is added or removed from the collection). See Format for Adding Items to a Collection.
Marking Data to Ignore
When you import a spreadsheet, the file might contain data that you do not want to include in the import, for example, columns with formulas or notes, columns containing a property that you do not want to edit, or blank cells that might cause data to be deleted. To remove these items from the import, you can mark columns (properties), rows (individual assets), or cells (individual property values) to be ignored by the import process.
Use the keyword IGNORE to mark data that you want to ignore during import, as follows:
To ignore this | Do this |
|---|---|
Ignore a column of data, such as a single property or extra column | Type the word |
Ignore an asset (a single row) | Type the word |
Ignore a property for an asset (a single cell) | Type |
For data to be ignored, the cell must contain only the single word IGNORE in all capital letters.
Format for Adding Items to a Collection
You can add items to a collection by typing an ampersand (&) before or after the property name in the column heading of a collection property, as follows:
Insert the ampersand at the beginning of the heading name to prepend items to the beginning of a collection, for example
&fixedChildProducts.Insert the ampersand at the end of the heading name to append products to the end of a collection, for example
fixedChildProducts&.
The prepend and append formats simplify the process of editing collections because you only need to include additional items and not every item in the collection. If you do not use this format, the import process overwrites the collection property with the exact items listed in the spreadsheet.
When you use this format, leave a cell blank to indicate that nothing should be added to the collection. If you have formatted a column to prepend or append items to a collection, blank cells in that column are ignored during import (nothing is added or removed from the collection).
The following figure shows an example of the prepend format used to add products to categories:
You can also use prepend and append to add to collections that are in itemized format. The following graphic shows the previous example in itemized format:
If you already use the ampersand character as part of a collection property name, you can use a different character for appending and prepending items to collections.
Formats Specific to CSV Files
The import feature supports files in CSV (comma-separated values) format in addition to XLS (Microsoft Excel) files. CSV files are text-based files that use commas to separate cells in the spreadsheet. Each new line in the file represents a new row of the spreadsheet. If the value stored in a cell includes commas, such as a collection property, the entire value is enclosed in double-quotation marks to indicate that the commas do not begin a new cell.
If you use Microsoft Excel to create the CSV file, the quotation marks are added automatically when you save the file in CSV format. You cannot see them when you open the file in Excel. You see them only if you edit the file in a text editor, such as Microsoft WordPad.
The following shows an example of a CSV file as it appears in text:
/atg/commerce/catalog/ProductCatalog:product, , , ID,displayNameDefault,parentCategoriesForCatalog,childSKUs xprod1009,Terry Gloves,masterCatalog=cat70014,"xsku1032, xsku1033, xsku1034" xprod1012,Cable knit hat,masterCatalog=cat70014,xsku1040 xprod1010,Shoulder Bag,masterCatalog=cat70014,xsku1038
The following figure shows the same CSV file as it appears in Microsoft Excel:
To avoid errors on import, make sure that the number of commas in rows 3 and greater exactly matches the number of commas in row 2.
