Import

Service Import

Synopsis

Service to import data files from customers.

Service Data structures

Enumeration TImportKind
ikDeliveryOrder Delivery order
ikReceptionOrder Reception order
ikInventory Inventory for comparison
ikArticle Article definitions
ikKit Kit definitions
ikInvoice Commercial invoice
Enumeration TImportFormat
ifXML Jamberry import format
ifXML2 Tommy import format
ifCSV Norwex import format

Service Calls

Function ImportFile(aFormat : TImportFormat; aKind : TImportKind; AFile : String) : Int64;

import a file of given kind and in format. for formats ifXML and ifXML2 only ikDeliveryOrder files can be imported. For ifCSV all kinds of files can be imported.

File format description

CSV commercial invoice

The CSV format contains the details of one or multiple commercial invoices. It uses ; as a field separater, fields are not quoted.

The first field is the order number. As soon as this field changes, a new order is started. The first line of an order contains the delivery address. The second field must be zero. additional lines contain simply additional articles for the same order.

Order line format:

col	Field	Type	description
00	Invoice number	string	When this changes, a new order is started.
01	order line	string	Ignored, must be zero for this line
02	Orders list	string	List of internal order numbers of client, separated by :
03	InvoiceValue	float	total amount of invoice
04	InvoiceCurrency	string	Currency code for invoice
05	InvoiceDate	Date	Date in YYYYMMDD format

Article line format:

col	Field	Type	description
00	Invoice number	string	This must be the same for all lines of the order.
01	order line	string	Starts at 1
02	?	string	Ignored
03	?	string	Ignored
04	InvoiceCurrency	string	Currency code for invoice
05	?	string	Ignored
06	ArticleCode	Article code	Article as it is known in the database
07	HSCode	Article code	?
08	ArticleDescription	Article description	Article description as it is known in the database
09	RowValue	Float	Value of this row
10	Quantity	Integer	Quantity
11	Country	string	Country code

CSV Article import format 1

Historical format. This format has 17 fields or more. Most fields in this format are ignored.

col	Field	Type	description
00	?	string	Ignored
01	?	string	Ignored
02	?	string	Ignored
03	Code	string	Article code
04	Description	string	Article code
...	Ignored	String	items 5 to 15 are ignored
16	language	string	if different from EN, the line is considered a translation

if the language is not empty, and different from 'EN' then the record will be considered a translation of an existing article.

CSV Article import format 2

This format has 3 or 4 fields.

col	Field	Type	description
00	code	String	Article code
01	description	String	Article description (optional, max)
02	barcode	String	Article barcode (EAN) (optional )
03	language	String	Article language

if the language is not empty, and different from 'EN' then the record will be considered a translation of an existing article.

CSV Kit definition format.

This format has 4 fields. The first line is skipped if it starts with 'KITITEM'. The separator char is , and the fields are quoted. If either master or detail article does not exist, it is created. If the master code differs from the previous line, a new kit is started.

col	Field	Type	description
00	MasterCode	String	Kit Article code
01	description	String	Kit Article description
02	DetailCode	String	Article included in kit
03	Quantity	Integer	Number of times the article is included in the kit

CSV Delivery Order format

The CSV format contains the details of one or multiple orders. It uses ; as a field separater, fields are not quoted.

The first field is the order number. As soon as this field changes, a new order is started. The first line of an order contains the delivery address. The second field must be zero. additional lines contain simply additional articles for the same order.

For the first line of an order, the first 27 fields (0-26) must be present, additional fields can be skipped.

For additional lines, only 10 lines are needed.

It is not possible to change the order of the fields or to have "holes": for example, it is not allowed to have fields 33,36,37 (35 missing)

The import routine will add empty values for fields that are not present. The meaning of the fields (when present) is the following

col	Field	Type	description
*	00	SalesOrderNo	String
*	01	orderLineNo	Integer
*	02	ItemCode	String
*	03	Description	String
*	04	MfgBatchNo	String
*	05	SRN	String
*	06	StockFeature	Integer
*	07	Owner	String
*	08	OrderedQuantity	Integer
*	09	Quantitytodeliver	Integer
*	10	SerialNumber	String
*	11	ShiptoCode	String
*	12	Shiptoname	String
*	13	Address1	String
*	14	Address2	String
*	15	Street	String
*	16	AddtlAddress	String
*	17	ZipCode	String
*	18	City	String
*	19	CountryCode	String
*	20	SalesOrderreference	String
*	21	OrderDate	Date
*	22	DeliveryDate	Date
*	23	Datemandatory	Boolean
*	24	Deliverytype	String
*	25	Principal	String
*	26	Carrier	String
*	27	OutputType	String
*	28	ShipmentMethod	String
*	29	Pickinstruction	String
*	30	Deliveryinstruction	String
*	31	Contact	String
*	32	Contact1	String
*	33	Phone	String
*	34	Size	Integer
*	35	Languagecode	String
*	36	Customeritemcode	String
*	37	CustomerItemdescription	String
*	38	Unitprice	Float
*	39	SpecificField1	String
*	40	SpecificField2	String
*	41	SpecificField3	String
*	42	Cashondelivery	Boolean
*	43	Priceforcashondelivery	String
*	44	ShipmentNumber	String
*	45	ServiceCode	String
*	46	Add_address1	String
*	47	Add_address2	String
*	48	Add_addressZip	String
*	49	Add_addresscity	String
*	50	Add_addressCountry	String
*	51	IC_FLAG	String