Customers: Sheet Customers

Before you begin

This section describes the XLSX stucture for importing customers.

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

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

Structure of the sheet

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

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

Column Letter Column Name Extra Info Allowed Values Required
A Customer ID This field is mandatory. It is used in the Addresses sheet. Integer number
Minimum: -2147483647
Maximum: 2147483647		 Yes
B First Name Text No
C Last Name Text No
D E-Mail A valid e-mail address. Yes
E Telephone Text, Numbers, Symbols No
F Fax Text, Numbers, Symbols No
G Password Do not insert the actual customer passwords here. This field contains hashed passwords. More information on hashing may be found here. Password hash, generated the following way:

For OpenCart 1.5.1.3 - 1.5.3.1:
MD5(<Original Password>)

For OpenCart 1.5.4+:
SHA1(<Salt> + SHA1(<Salt> + SHA1(<Original Password>)))		 (Conditional)*
H Salt This random string is used to generate the password hash.

Used in OpenCart 1.5.4+		 A random alphanumeric string, 9 characters long. (Conditional)*
I Newsletter Any value different than "Enabled" will be regarded as "Disabled". Enabled / Disabled No
J Status Any value different than "Enabled" will be regarded as "Disabled". Enabled / Disabled No
K Approved This field was used in OpenCart 1.x - 2.x. Not used in OpenCart 3.x. Any value different than "Enabled" will be regarded as "Disabled". Enabled / Disabled No
L Customer Group If the customer group does not already exist in your store, ExcelPort will not create it automatically. A valid name of a Customer Group. See Meta sheet, column B for allowed values. No
M Default Address A valid Address ID. See Addresses sheet, column B for allowed values. No
N Cart This field was used in OpenCart 1.5.1.3 - 2.0.3.1. Not used in OpenCart 2.1.0.1+ Serialized cart array. No
O Wishlist This field was used in OpenCart 1.5.1.3 - 2.0.3.1. Not used in OpenCart 2.1.0.1+ Serialized wishlist array. No
P History A JSON-encoded history array, matching the OpenCart customer_history table schema. No
Q Transactions A JSON-encoded history array, matching the OpenCart customer_transaction table schema. No
R Reward Points A JSON-encoded history array, matching the OpenCart customer_reward table schema. No
S IP Addresses A JSON-encoded history array, matching the OpenCart customer_ip table schema. No
T Custom Field Not used in OpenCart 1.5.x JSON-encoded custom field array. No
U Safe Any value different than "Enabled" will be regarded as "Disabled". Enabled / Disabled No
V Store ID Note that the default store has Store ID = 0. See Meta sheet, column C for allowed values. No
W IP A valid IPv4 address: xxx.xxx.xxx.xxx No
X Date Added Example: 2017-01-31 15:36:53 Date
Format: yyyy-mm-dd hh:ii:ss		 No
Y Customer Approval Date of when customer requested approval. Not used in OpenCart 1.x - 2.x. Used since OpenCart 3.x. Example: 2017-01-31 15:36:53 Date
Format: yyyy-mm-dd hh:ii:ss		 No
Z Customer Affiliate Not used in OpenCart 1.x - 2.x. Used since OpenCart 3.x. JSON-encoded affiliate information. 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.