Export and import CSV files

When importing and exporting data, you may want to use CSV files. This allows you to list the data in multiple lines with headers.

The first row of the CSV contains the headers. These headers are used to identify the complete path to the property, since the CSV format supports complete hierarchy of the record.

Understand CSV headers

When working with CSV headers, remember the following:

The top-level properties use the property name as the header name. For example, the property and header name would both be stringProperty1.
A child item uses dot separators to refer to its properties. For example, the property of a child item would be complexProperty.stringProperty1.
When working with list properties, the first header holds the sequence number of each item followed by the individual property. For example:
```
listProperty.row#, listProperty.springProperty1, listProperty.numberProperty1
```
Map properties use the first header to hold the keys of each entry, followed by individual properties. For example:
```
mapProperty.key#, mapProperty.stringProperty1, mapProperty.numberProperty1
```
Note that each entry in a map or list will be displayed in separate CSV rows. This may make a single record in a CSV file spread across multiple CSV rows.

The following is an example of a list data CSV file with records spreading across multiple rows:

`ID`	`listProperty.row#`	`listProperty.id`	`listProperty.property1`	`listProperty.property2`
`ID123`	`0`	`childpropId1`	`Property1data1`	`Property2data1`
`ID123`	`1`	`childpropId2`	`Property1data2`	`Property2data2`
`ID123`	`2`	`childpropId3`	`Property1data3`	`Property2data3`

You can export a CSV file with default headers using the csv in the format parameter. For example:

{
  "fileName": "profile.json",
  "mode" : "standalone",
  "id" : "Profiles",
  "format" : "csv"
}

You can export all attributes, including custom attributes by setting the headersList parameter to ALL. The following example shows how to include all headers in a standalone mode:

{
  "fileName": "profile.json",
  "mode" : "standalone",
  "id" : "Profiles",
  "format" : "csv"{
   "fileName": "profile.json",
   "mode" : "standalone",
   "id" : " Profiles",
   "format" : "csv",
   "params": {
          "headersList": "ALL"
    }
 }
}

To export a specific list of attributes, use the following format:

{
   "fileName": "profile.json",
   "mode" : "standalone",
   "id" : " Profiles",
   "format" : "csv",
   "params": {
          "headersList": "firstName,lastName,shippingAddress.postalCode,shippingAddress.country"
    }
 }

You can also use the default list of attributes and include additional attributes by using a '+' sign before the list of additional headers. For example:

{
   "fileName": "profile.json",
   "mode" : "standalone",
   "id" : " Profiles",
   "format" : "csv",
   "params": {
          "headersList": "+addedfield1,addedfield2,addedfield3.subField1"
    }
 }

If you do not specify a set of headers, the export includes the default set of headers for the Oracle Commerce item.

Note: Custom properties for collections are supported for both CSV and JSON formats.

For JSON export, the custom properties are automatically included.
For CSV export, the headersList should include the required custom property names or they can pass "ALL" (as in the previous example) to get all the custom properties.
During import, the custom properties can be passed in both CSV and JSON format.

Understand the JSON format

A data file in the JSON format contains an array of JSON items, with each JSON item considered to be a data record. The following is an example of a JSON format:

{
  "products": [
    {
      "displayName": "Product 1 Name",
      "id": "product1",
      "listPrice": 35,
      "childSKUs": [
        {
          "id": "SKU of Product1"
        }
      ]
    },
    {
      "displayName": "Product 2 Name",
      "id": "product2",
      "listPrice": 45,
      "childSKUs": [
        {
          "id": "SKU of Product2"
        }
      ]
    }
  ]
}

When working with JSON files, remember the following:

A single JSON record can contain the complete hierarchy of data. For example, a profile can have a list of addresses, accounts and create cards, etc.
The record that needs to be updated can include any updated partial data except for maps and lists. If simple properties, such as strings or numbers, are not passed during the update, they will not be reset to null or to 0.
For map and list properties, if an existing entry is not passed during an update, it is deleted. For example, if a profile contains three addresses and only the first two addresses are passed in with the data file, the third address will be deleted.
A simple property value can be set to null so that during an update, it removes the previous value. For example:
```
{
  ...
  "property1" : null
  ...
}
```
A list property can be cleared in JSON format by passing an empty array. For example:
```
{
  ...
  "listProperty1" : []
  ...
}
```

Default headers for CSV export and import

The following section describes the fields that are included in the header by default when exporting or importing data.

Default headers for accounts

The default header list for accounts contains:

id
name
description
type
customerType
taxReferenceNumber
dunsNumber
contract.terms.terms
contract.displayName
contract.description
contract.catalog.id
contract.priceListGroup.id
contract.externalContractReference
members.row#
members.firstName
members.lastName
members.id
members.email
relativeRoles.row#
relativeRoles.function
secondaryAddresses.key#
secondaryAddresses.country
secondaryAddresses.phoneNumber
secondaryAddresses.address2
secondaryAddresses.city
secondaryAddresses.address1
secondaryAddresses.companyName
secondaryAddresses.postalCode
secondaryAddresses.id
secondaryAddresses.state
billingAddress.country
billingAddress.phoneNumber
billingAddress.address2
billingAddress.city
billingAddress.address1
billingAddress.companyName
billingAddress.postalCode
billingAddress.id
billingAddress.state
shippingAddress.country
shippingAddress.phoneNumber
shippingAddress.address2
shippingAddress.city
shippingAddress.address1
shippingAddress.companyName
shippingAddress.postalCode
shippingAddress.id
shippingAddress.state

Default headers for profiles

The default header list for profiles contains:

dateOfBirth
middleName
receiveEmail
lastName
locale
id
lastActivity
registrationDate
email
login
firstName
shippingAddress.middleName
shippingAddress.item-id
shippingAddress.lastName
shippingAddress.state
shippingAddress.address1
shippingAddress.address2
shippingAddress.address3
shippingAddress.companyName
shippingAddress.repositoryId
shippingAddress.suffix
shippingAddress.city
shippingAddress.country
shippingAddress.postalCode
shippingAddress.faxNumber
shippingAddress.phoneNumber
shippingAddress.county
shippingAddress.prefix
shippingAddress.firstName

Default headers for products

The default header list for products contains:

id
displayName
description
longDescription
type
keywords.row#
keywords.keywords
listPrices.key#
listPrices.listPrices
salePrices.key#
salePrices.salePrices
shippingSurcharges.key#
shippingSurcharges.shippingSurcharges
listVolumePrices.key#
listVolumePrices.complexPrice.levels.row#
listVolumePrices.complexPrice.levels.quantity
listVolumePrices.complexPrice.levels.price
listVolumePrices.pricingScheme
saleVolumePrices.key#
saleVolumePrices.complexPrice.levels.row#
saleVolumePrices.complexPrice.levels.quantity
saleVolumePrices.complexPrice.levels.price
saleVolumePrices.pricingScheme
parentCategories.row#
parentCategories.displayName
parentCategories.id
productImages.row#
productImages.description
productImages.url
productImages.id
productImages.path
productImages.type
productImages.name
primaryImageTitle
smallImageURLs
primaryLargeImageURL
fullImageURLs
sourceImageURLs
primarySourceImageURL
mediumImageURLs
largeImageURLs
thumbImageURLs
primaryMediumImageURL
primaryImageAltText
primarySmallImageURL
primaryFullImageURL
primaryThumbImageURL
seoUrlSlugDerived
seoKeywordsDerived
seoDescriptionDerived
seoTitleDerived
brand
defaultProductListingSku.id
childSKUs.row#
childSKUs.id
childSKUs.barcode
childSKUs.active
childSKUs.bundleLinks.row#
childSKUs.bundleLinks.item.id
childSKUs.bundleLinks.quantity
childSKUs.listPrices.key#
childSKUs.listPrices.listPrices
childSKUs.salePrices.key#
childSKUs.salePrices.salePrices
childSKUs.listVolumePrices.key#
childSKUs.listVolumePrices.complexPrice.levels.row#
childSKUs.listVolumePrices.complexPrice.levels.quantity
childSKUs.listVolumePrices.complexPrice.levels.price
childSKUs.listVolumePrices.pricingScheme
childSKUs.saleVolumePrices.key#
childSKUs.saleVolumePrices.complexPrice.levels.row#
childSKUs.saleVolumePrices.complexPrice.levels.quantity
childSKUs.saleVolumePrices.complexPrice.levels.price
childSKUs.saleVolumePrices.pricingScheme

Default headers for categories

The default header list for categories contains:

longDescription
categoryImages.row#
categoryImages.description
categoryImages.url
categoryImages.id
categoryImages.path
categoryImages.type
categoryImages.name
displayName
id
fixedChildProducts.row#
fixedChildProducts.id
seoDescriptionDerived
fixedChildCategories.row#
fixedChildCategories.id
fixedChildCategories.displayName

Default headers for inventory

The default header list for inventory contains:

preOrderLevel
backorderThresdhold
displayName
skuNumber
backorderLevel
availabilityStatus
availableToPromise.quantity
availableToPromise.availableDate
availableToPromise.quantityWithFraction
availableToPromise.inventoryId
preorderThreshold
availabilityDate
locationId
stocklevel
stockThreshold

Default headers for promotions

The default header list for promotions contains:

template
templateName
templatePath
endDate
displayName
sites.row#
sites.id
sites.name
audiences.row#
audiences.id
audiences.displayName
audiences.deleted
audiences.enabled
global
templateValues.no_of_items_to_discount
templateValues.discount_value
templateValues.discount_type_value
templateValues.sort_order
templateValues.no_of_items_to_buy
templateValues.spend_value
templateValues.sort_by
templateValues.discountStructure
templateValues.gwpItem.autoRemove
templateValues.gwpItem.giftType
templateValues.gwpItem.giftId
templateValues.condition_psc_value.includedCategories.row#
templateValues.condition_psc_value.includedCategories.includedCategories
templateValues.condition_psc_value.includedProducts.row#
templateValues.condition_psc_value.includedProducts.includedProducts
templateValues.condition_psc_value.excludedProducts.row#
templateValues.condition_psc_value.excludedProducts.excludedProducts
templateValues.condition_psc_value.excludedCategories.row#
templateValues.condition_psc_value.excludedCategories.excludedCategories
templateValues.condition_psc_value.includedSkus.row#
templateValues.condition_psc_value.includedSkus.includedSkus
templateValues.condition_psc_value.excludedSkus.row#
templateValues.condition_psc_value.excludedSkus.excludedSkus
templateValues.condition_psc_value.sameAsCondition
templateValues.optional_offer_psc_value.includedCategories.row#
templateValues.optional_offer_psc_value.includedCategories.includedCategories
templateValues.optional_offer_psc_value.includedProducts.row#
templateValues.optional_offer_psc_value.includedProducts.includedProducts
templateValues.optional_offer_psc_value.excludedProducts.row#
templateValues.optional_offer_psc_value.excludedProducts.excludedProducts
templateValues.optional_offer_psc_value.excludedCategories.row#
templateValues.optional_offer_psc_value.excludedCategories.excludedCategories
templateValues.optional_offer_psc_value.includedSkus.row#
templateValues.optional_offer_psc_value.includedSkus.includedSkus
templateValues.optional_offer_psc_value.excludedSkus.row#
templateValues.optional_offer_psc_value.excludedSkus.excludedSkus
templateValues.optional_offer_psc_value.sameAsCondition
templateValues.offer_psc_value.includedCategories.row#
templateValues.offer_psc_value.includedCategories.includedCategories
templateValues.offer_psc_value.includedProducts.row#
templateValues.offer_psc_value.includedProducts.includedProducts
templateValues.offer_psc_value.excludedProducts.row#
templateValues.offer_psc_value.excludedProducts.excludedProducts
templateValues.offer_psc_value.excludedCategories.row#
templateValues.offer_psc_value.excludedCategories.excludedCategories
templateValues.offer_psc_value.includedSkus.row#
templateValues.offer_psc_value.includedSkus.includedSkus
templateValues.offer_psc_value.excludedSkus.row#
templateValues.offer_psc_value.excludedSkus.excludedSkus
templateValues.offer_psc_value.sameAsCondition
templateValues.PSC_value.includedCategories.row#
templateValues.PSC_value.includedCategories.includedCategories
templateValues.PSC_value.includedProducts.row#
templateValues.PSC_value.includedProducts.includedProducts
templateValues.PSC_value.excludedProducts.row#
templateValues.PSC_value.excludedProducts.excludedProducts
templateValues.PSC_value.excludedCategories.row#
templateValues.PSC_value.excludedCategories.excludedCategories
templateValues.PSC_value.includedSkus.row#
templateValues.PSC_value.includedSkus.includedSkus
templateValues.PSC_value.excludedSkus.row#
templateValues.PSC_value.excludedSkus.excludedSkus
description
priority
type
excludedPromotions.row#
excludedPromotions.id
priceListGroups.row#
priceListGroups.id
translations.items.row#
translations.items.description
translations.items.displayName
translations.items.lang
id
startDate
shippingMethods.row#
shippingMethods.shippingMethods
stackingRule
parentFolder.row#
parentFolder.id