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.
This section applies to Open Storefront Framework (OSF) and Storefront Classic.
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
- 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:
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.mapProperty.key#, mapProperty.stringProperty1, mapProperty.numberProperty1
ID |
listProperty.row# |
listProperty.id |
listProperty.property1 |
listProperty.property2 |
---|---|---|---|---|
ID123 |
0 |
childpropId1 |
Property1data1 |
Property2data1 |
ID123 |
1 |
childpropId2 |
Property1data2 |
Property2data2 |
ID123 |
2 |
childpropId3 |
Property1data3 |
Property2data3 |
csv
in the format
parameter. For example:{
"fileName": "profile.json",
"mode" : "standalone",
"id" : "Profiles",
"format" : "csv"
}
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"
}
}
}
{
"fileName": "profile.json",
"mode" : "standalone",
"id" : " Profiles",
"format" : "csv",
"params": {
"headersList": "firstName,lastName,shippingAddress.postalCode,shippingAddress.country"
}
}
{
"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.
- 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
{
"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"
}
]
}
]
}
- 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 to0
. - 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
- 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