Orders: Sheet Orders

Before you begin

This section describes the XLSX stucture for importing orders.

Follow these guidelines to properly structure your order file. To follow along, feel free to download an example file.

Important
  • This sheet is required for Orders import.
  • All columns must be present in the sequence specified below.
  • Do not change/translate the names of the columns.
  • Allowed names for this sheet: Orders
  • Maximum number of rows: 800. More info here

Structure of the sheet

Here is a list of every column in the sheet Orders.

To follow along, feel free to download an example file.

Column Letter Column Name Extra Info Allowed Values Required
A Order ID If you have an existing order in your store with this Order ID, it will be overwritten. If left empty, the order will be imported as a new order, and it will be linked with any entries from the rest of the sheets (Products, Options, Downloads, etc.). Integer number
Minimum: -2147483647
Maximum: 2147483647		 (Conditional)*
B Invoice Number Integer number
Minimum: -2147483647
Maximum: 2147483647		 No
C Invoice Prefix Text No
D Store ID Do not leave this column empty. Use 0 to associate your order with your default store. See Meta sheet, column A for allowed values. Yes
E Customer ID Use 0 if this is a guest customer. A valid Customer ID. You may do a Customer export to see all available Customer IDs.

Integer number
Minimum: -2147483647
Maximum: 2147483647		 No
F Customer Group ID A valid Customer Group ID. See Meta sheet, column C for allowed values. No
G Customer First Name Text No
H Customer Last Name Text No
I Customer E-mail A valid e-mail address. No
J Customer Telephone Text, Numbers, Symbols No
K Customer Fax Text, Numbers, Symbols No
L Payment First Name Text No
M Payment Last Name Text No
N Payment Company Text No
O Payment Company ID Text No
P Payment Tax ID Text No
Q Payment Address 1 Text No
R Payment Address 2 Text No
S Payment City Text No
T Payment Postcode Text No
U Payment Country A valid OpenCart country name. See Meta sheet, column E for allowed values. No
V Payment Zone A valid OpenCart zone name. For a full list of your zones, visit Admin > System > Localisation > Zones No
W Payment Address Format This is a string with placeholders.

The following ones are supported: {firstname}, {lastname}, {company}, {address_1}, {address_2}, {city} {postcode}, {zone}, {zone_code}, {country}		 An OpenCart address format. If you leave this empty, OpenCart will default to:

{firstname} {lastname}
{company}
{address_1}
{address_2}
{city} {postcode}
{zone}
{country}		 No
X Payment Method A valid Payment Method Name. Some of the possible values are listed in the Meta sheet, column P. No
Y Payment Code A valid Payment Code. Some of the possible values are listed in the Meta sheet, column Q. No
Z Shipping First Name Text No
AA Shipping Last Name Text No
AB Shipping Company Text No
AC Shipping Address 1 Text No
AD Shipping Address 2 Text No
AE Shipping City Text No
AF Shipping Postcode Text No
AG Shipping Country A valid OpenCart country name. See Meta sheet, column E for allowed values. No
AH Shipping Zone A valid OpenCart zone name. For a full list of your zones, visit Admin > System > Localisation > Zones No
AI Shipping Address Format This is a string with placeholders.

The following ones are supported: {firstname}, {lastname}, {company}, {address_1}, {address_2}, {city} {postcode}, {zone}, {zone_code}, {country}		 An OpenCart address format. If you leave this empty, OpenCart will default to:

{firstname} {lastname}
{company}
{address_1}
{address_2}
{city} {postcode}
{zone}
{country}		 No
AJ Shipping Method A valid Shipping Method Name. Some of the possible values are listed in the Meta sheet, column N. No
AK Shipping Code A valid Shipping Code. Some of the possible values are listed in the Meta sheet, column O. No
AL Comment Text No
AM Total Decimal point must be a dot, not a comma. Example: 10.99 Floating point number No
AN Order Status ID A valid Order Status ID. See Meta sheet, column F for allowed values. No
AO Affiliate ID A valid Affiliate ID. See Meta sheet, column H for allowed values. No
AP Commission Decimal point must be a dot, not a comma. Example: 10.99 Floating point number No
AQ Language ID A valid Language ID. See Meta sheet, column J for allowed values. No
AR Currency ID A valid Currency ID. See Meta sheet, column L for allowed values. No
AS Currency Code A valid Currency Code. See Meta sheet, column M for allowed values. No
AT Currency Value Decimal point must be a dot, not a comma. Example: 10.99 Floating point number No
AU IP A valid IPv4 address: xxx.xxx.xxx.xxx No
AV Forwarded IP A valid IPv4 address: xxx.xxx.xxx.xxx No
AW User Agent This field holds the User Agent from which the order was made. Text No
AX Accept Language This field holds the Accepted Language of the User Agent from which the order was made. Text No
AY Date Added Example: 2017-01-31 15:36:53 Date
Format: yyyy-mm-dd hh:ii:ss		 No
AZ Date Modified Example: 2017-01-31 15:36:53 Date
Format: yyyy-mm-dd hh:ii:ss		 No
BA Reward This is used only in OpenCart 1.5.1.3 Integer number
Minimum: -2147483647
Maximum: 2147483647		 No
BB Custom Field Not used in OpenCart 1.5.x JSON-encoded custom field array. No
BC Payment Custom Field Not used in OpenCart 1.5.x JSON-encoded custom field array. No
BD Shipping Custom Field Not used in OpenCart 1.5.x JSON-encoded custom field array. No
BE Marketing ID A valid Marketing Campaign ID. No
BF Tracking Text No
Note* (Conditional) fields accept empty values for the purposes of an ExcelPort import. There are cases when you might need a value for this field. More information in column Extra Info.