Imports - Orders items
This article will show you how to prepare files for import in the "ordersitems" scope. Representing individual items in receipts or shopping carts, orders items are used to fill the contents of Orders and Abandoned carts.
Records in the "ordersitems" scope are not updated. Instead, you need to be prepared to reimport all orders items for each "order_id" you update in the "orders" or "abandonedcarts" scope. Updates to these two scopes always result in Splio purging all associated orders items (it is one of the initial steps of import).
Table of Contents
- Preparation of an Ordersitems file
- Advanced information
- Basic knowledge of the CSV format and UTF-8 encoding.
- The sub-sequence must be defined in the config file under the "ordersitems" scope.
- A UTF-8-enabled text editor.
- A spreadsheet software.
Preparation of an Ordersitems file
Use your favorite spreadsheet software or UTF-8 enabled text editor to prepare and edit the file. Spreadsheet is the recommended tool whenever you need to trim or move columns.
Remember to save your file in the CSV format using UTF-8 encoding without BOM.
Header and Columns
The first line of the file, called the header, is used to determine the contents of the following lines. Therefore, it should be constructed only from the names of columns.
Remember that if Splio encounters a column name it cannot recognize, it will skip the file.
The following columns are available in the "ordersitems" scope:
- order_id -- The external identifier of an order, this column binds "ordersitems" to specific "orders" (or "abandonedcarts"); this column is mandatory and should reference an already existing order, otherwise a blank "orders" entry will be created.
- product_id -- the external id of the product on the receipt or in the shopping cart. This column is mandatory. If the product does not yet exist in the data base, Splio will create a default (empty) record. Updating products does not erase associated orders items.
- unit_price -- The price charged for a single unit of the product.
- quantity -- The quantity of the product.
- discount_amount -- The amount of discount applied to the product.
- tax_amount -- The amount of tax applicable to the product.
- total_line_amount -- The total amount of the line (i.e., "ordersitems" record); normally equals unitprice times quantity minus discountamount plus tax_amount.
- currency -- Three-letter ISO code of the currency in which all prices and amounts are given. If absent the universe's default is used.
- A custom column, if defined for the "ordersitems" scope in your Splio universe. You can include as many custom columns as you need. The names of custom columns are composed of the lowercase letter "c" and the number of the corresponding custom field, e.g., "c15".
Note that all column names are always lowercase.
Please remember that Splio checks import files for mandatory columns. You need to include both "orderid" and "productid" for the import to succeed.
Important: please note that when uploading your product prices, you must enter two decimals after the dot (i.e 10.99).
Example of an Ordersitems file
Examine the following example of a file containing 3 items which belong to the same order:
order_id;product_id;unit_price;quantity;tax_amount;total_line_amount;currency;c3 70x1bMhtt-1531745300;00131300020004;182.49;1;36.5;218.99;EUR;Accessories 70x1bMhtt-1531745300;0027011018374;9.16;5;9.16;54.96;EUR;Food 70x1bMhtt-1531745300;0077890206324;154.5;2;61.8;370.80;Accessories
The fields represent the unique identifier of the receipt, identifier of the product, its net price, quantity, applied tax, sum total of each line, and a custom category field.
Name your file
When saving your file, use a name composed of the universe name, scope ("ordersitems"), sub-sequence, and current date. For example:
This filename belongs to the universe "myuniverse", sub-sequence "pets" defined for orders, and is dated July 12, 2018. Note that this date marks the file, it does not have to correspond to the items.
If you wish to know more, consult the File Naming and Grouping guide.
You can now upload the file to SFTP/FTPS.
This part of the document contains additional information which supplements the guide above and provides more detail.
|Encoding||UTF-8 without BOM.|
|Format||CSV (no multiline)|
|Column separator||; (semicolon)|
|Text qualifier||" (double quote, optional)|
|Escape character||\ (backslash)|
|End of line||\n or \r\n|
|Decimal separator||. (dot)|
The sections below describes columns (fields) available for use when constructing or editing ordersitems files.
/!\ Remember that all column names are case sensitive. For this reason, default names are all lowercase.
|Column||Mandatory||Data Type / Maximum length||Description|
|order_id||Yes||Text (max. 50 characters)||Corresponds to the orderid field in the "orders" scope and should reference the external id of an existing (already imported) order. If you reference an unknown orderid, a default order record will be created in the database.|
|product_id||Yes||Text (max. 50 characters)||References the external id of a product. The referenced product should be imported beforehand, otherwise Splio will create a default product record to preserve the integrity of data.|
|unit_price||Yes||Decimal, "." (dot) as decimal separator||The price for a single unit of the item being sold.|
|quantity||No||Integer||The number of units of the item represented by the product_id.|
|discount_amount||No||Decimal, "." (dot) as decimal separator||The total discount amount.|
|tax_amount||No||Decimal, "." (dot) as decimal separator||The total tax amount.|
|total_line_amount||No||Decimal, "." (dot) as decimal separator||The total amount for this order line.|
|currency||Yes||Text (max. 3 characters)||The 3-letter code of the currency used for this order line. If none, the default currency of the universe is assumed.|
|<custom>||No||Text (max. 255 characters)||A custom field (see below).|
The import feature in Splio allows using custom fields which have been configured in your universe. To include a custom field in the header, use the technical name of the field (lowercase letter "c" followed by its numerical id).
For example, if you have a field called "promo" in your "orders" table, and its id is 5, you should use "c5" as the name of the column. You can include as many custom fields (columns) in your import files as defined for the given scope in your Splio universe. Each column name should be used only once.
Also, bear in mind that if Splio cannot recognize the name of a column, it will not import the file.
The following information is made available by Splio after import.
- Whether the import was aborted or not and, if aborted, the reason (unknown column, missing external id column, undeclared custom field)
- The number of lines treated (imported+skipped)/imported/skipped
- The number of new records
- The reason each line was skipped (productid NULL, too many/not enough columns, orderid or product_id not found in the database)
Normally, a log file will be available in the same repository where the import file was uploaded.