Business partner data import file – Procountor

Business partner data import file can be used to bring new business partners to Procountor’s business partner register or to update existing business partner information. Business partner register include customers, suppliers and persons. This article focuses on forming the CSV import file of the business partner data. Further information about the importing process and things to take into account when importing the file can be found here.

Business partner data

The example file in the picture above has three business partners and one business partner group to be imported. These business partners and business partner groups can be imported to Procountor with the Import data function.

In order to update information of an existing business partner with an import file requires that the information is allocated to an existing business partner. Imported data is allocated to the business partner register in the following order:

Business ID / social security number (field 2, column B)
Bank account for number (only with persons and suppliers) (field 14, column N)
Customer number (field 53, column BA)

An example of the allocation logic

If import data contains only a business ID:

Allocation is done by the business ID and the imported data is allocated to the business partner in business partner register that has the corresponding business ID.
If there are several business partners in business partner register with same business ID, imported data will be allocated to the business partner that has been created earliest.
If there is no corresponding business partner in the register for the imported business ID, imported data will not be allocated to any business partner; instead, the data will create a new business partner.

If import data contains business ID as well as another allocation information (for example, a customer number):

If necessary, allocation is done with both pieces of information: if there are several business partners in the register with same business ID, Procountor will next look for a business partner in the register that has both business ID and customer number that match to the import file.
If there are several business partners in the register with same business ID but with different bank account or customer number, the import data can then be allocated to the right business partner with bank account number or customer number in the import file.
If there are business partners in the register that have a matching business ID to the import file, but that have simultaneously different customer number in the register compared to the import file, any information will not be allocated. In these kinds of situations, new business partners will be created.

Import file

The import file is brought to Procountor in CSV format. The file can be created using this CSV template. The file contains heading rows for the information that can be included in the file. However, these heading rows must be deleted before importing the file; every single row in the file is interpreted as a business partner or business partner group record. It is also important to check that the first cell (A1) of the file contains information; otherwise all the other cells in the file will relocate to a faulty position.

The data import file is a file in which a single row constitutes a single record. Fields in the record are separated by semicolons. For this reason, semicolons may not be used in the data contained in the fields. The file does not have a separate header row, i.e. the records begin immediately from the first row. The CSV extension is used to identify the file, which can otherwise be named freely. If the file is saved in CSV format in Excel (not CSV (Macintosh) or CSV (MS-DOS)), the field separators and extension will automatically be correct. If the file is created using an OpenOffice spreadsheet, it will add quotation marks around the character strings when saving in CSV format. This setting must be cleared in order to make the file correctly readable.

Records

The business partner data import file can contain two types of records:

Business partner record that contains the data of a business partner.
Business partner group record that determines a group or groups of the business partner. Business partner group records can be added without limit to a single Business partner record. However, each Business partner group record applies only to the Business partner record above a Business partner group record. Business partner group record is not obligatory.

The following record descriptions include the following information for each field, organized by record type:

Num: the field’s number
Excel cell: the field’s column in the Excel spreadsheet
Field name / Data item: description of the data
Obligatoriness: X = mandatory, (X) = mandatory depending on the circumstances, empty = optional
Format: text, decimal, integer, date, or other specified form
Permitted values / Verification: states the values permitted for the field in question / will the accuracy of the data be verified
Default: the value assigned to empty fields
Additional information: other relevant information concerning the field

Business partner record

Num	Excel cell	Field name / Data item	Obligatoriness	Format	Permitted values / Verification	Default	Additional information
1	A	Business ID type for companies and social security number type for persons.		Text, max. 4 characters, e.g.: FI (Finnish companies), SSN (Persons), ASSN (Associations) or empty (the user does not wish to fill in this field).			If the value is FI or SSN, the input in column 2 (B) will be verified. If the business partner is a person, column 2 (B) will be verified even if column A is empty.
2	B	Business ID / SSN	(X)	Text, 40 characters	The format will be verified		Business ID for companies and social security number or date of birth for persons. This data item is used to allocate the business partner to the business partner register.
3	C	Business partner type	X	Text, 1 character	C = Customer S = Supplier P = Person		Persons for whom salaries, travel invoices and bills of charges are created are stored in the Person register. Customers should be added to the customer register even if they are persons, since persons normally do not have an associated billing address in the system, and this may cause problems when creating sales invoices for them.
4	D	Name of business partner	X	Text, 80 characters			Last name only if business partner type is Person (column 3 (C) = P).
5	E	First name (only for business partner type Person)		Text, 80 characters			Only taken into account for business partner type Person (column 3 (C) = P).
6	F	Street address		Text, 255 characters			The data item specifying the name related to the address information is entered into field 54 (BB).
7	G	City		Text, 80 characters
8	H	Postal code		Text, 20 characters			After the data import the postal code is shown without the first zeros. The code is shown correctly in the register.
9	I	Country code		Text, 2 characters	The code must exist in Procountor’s country register or this field will be left empty.		Country codes
10	J	Telephone number		Text
11	K	Mobile number		Text
12	L	Telefax		Text
13	M	Department		Text, 80 characters			Only taken into account for business partner type Person (column 3 (C) = P).
14	N	Bank account number		nnnnnn-nnnnnnnn or IBAN	The format will be verified		Only for persons and suppliers. Customers do not require a bank account number. This data item is used to allocate the business partner to the business partner register.
15	O	Term of payment		Integer, 3 digits	The format will be verified	If the business partner is a customer and default terms of payment for sales invoices in Usage settings have been defined, Procountor will use that information and a zero as the percentage. Otherwise the payment terms will be 10 days (Procountor default) and percentage will be 0.	The first two digits indicate the number of days (10 = 10 days, 14 = 2 weeks and 30 = 1 month). The third digit is the payment percentage. We recommend the figures 0, 1, 2, 3, 4 and 5. For example: 104 = 10 days payment term with a 4% cash discount. 000 = Payable immediately, no discount.
16	P	Payment method		Text	bank transfer, direct debit, direct payment, clearing, credit card charge, foreign payment or other	bank transfer	If the payment method is written in a faulty form, the payment method will be set to the default value (bank transfer).
17	Q	Currency code		Text, 3 characters	The code must exist in Procountor’s currency register.	EUR
18	R	Email address	(X)	Text, 80 characters	The format will be verified		If the invoice channel, field 26 (Z), is email (1), this data item is mandatory.
19	S	Contact person		Text, 80 characters
20	T	Home municipality		Text, 40 characters			Only taken into account for persons (field 3 (C) = P).
21	U	Tax municipality		Text, 40 characters			Only taken into account for persons (field 3 (C) = P).
22	V	Method of delivery		Text	mailing, online, freight, courier service, VR cargo, bus, pick-up or empty		If the method of delivery is written in a faulty form, this field will be emptied.
23	W	Penal interest rate		Decimal	0-100	8,00
24	X	Default invoice language		Integer, 1 digit	1 = English 2 = Finnish 3 = Swedish 4 = Estonian	2
25	Y	Homepage		Text, 255 characters
26	Z	Invoice channel		Integer	1 = Email 2 = Post 3 = E-invoice 6 = No sending	2	If the value is 1 (Email), field 18 (R) must be correct. If the value is 2 (Post), field 48 (AV) must be correct. If the value is 3 (E-invoice), field 50 (AX) or 57 (BE) must be correct. If fields 50 (AX), 56 (BD) and 57 (BE) are empty, the invoice channel will be changed to 2 (Post).
27	AA	Passivity code		Text, 1 character	I = Inactive
28	AB	Default item in dimension class 1		Text, 255 characters	Must exist in the first dimension’s items		The first dimension in Dimensions view will be interpreted as the first dimension class.
29	AC	Default item for dimension class 2		Text, 255 characters	Must exist in the second dimension’s items
30	AD	Default item in dimension class 3		Text, 255 characters	Must exist in the third dimension’s items
31	AE	Default item for dimension class 4		Text, 255 characters	Must exist in the fourth dimension’s items
32	AF	Default item for dimension class 5		Text, 255 characters	Must exist in the fifth dimension’s items
33	AG	Default item for dimension class 6		Text, 255 characters	Must exist in the sixth dimension’s items
34	AH	Default debit account for purchases		Integer, 4 digits	Must exist in the chart of accounts	8450	If the account is not found in the chart of accounts, the default will be used.
35	AI	Default credit account for purchases		Integer, 4 digits	Must exist in the chart of accounts	2880	If the account is not found in the chart of accounts, the default will be used.
36	AJ	Default account for purchase balancing		Integer, 4 digits	Must exist in the chart of accounts	8890	If the account is not found in the chart of accounts, the default will be used.
37	AK	Default debit account for sales		Integer, 4 digits	Must exist in the chart of accounts	1700	If the account is not found in the chart of accounts, the default will be used.
38	AL	Default credit account for sales		Integer, 4 digits	Must exist in the chart of accounts	3000	If the account is not found in the chart of accounts, the default will be used.
39	AM	Default debit account for bills of charges		Integer, 4 digits	Must exist in the chart of accounts	8450	If the account is not found in the chart of accounts, the default will be used.
40	AN	Default credit account for bills of charges		Integer, 4 digits	Must exist in the chart of accounts	2880	If the account is not found in the chart of accounts, the default will be used.
41	AO	Default account for balancing bills of charges		Integer, 4 digits	Must exist in the chart of accounts	8890	If the account is not found in the chart of accounts, the default will be used.
42	AP	Default debit account for travel invoices		Integer, 4 digits	Must exist in the chart of accounts	7800	If the account is not found in the chart of accounts, the default will be used.
43	AQ	Default credit account for travel invoices		Integer, 4 digits	Must exist in the chart of accounts	2880	If the account is not found in the chart of accounts, the default will be used.
44	AR	Default account for balancing travel invoices		Integer, 4 digits	Must exist in the chart of accounts	8890	If the account is not found in the chart of accounts, the default will be used.
45	AS	Default credit account for journals		Integer, 4 digits	Must exist in the chart of accounts	8450	If the account is not found in the chart of accounts, the default will be used.
46	AT	Default debit account for journals		Integer, 4 digits	Must exist in the chart of accounts	8450	If the account is not found in the chart of accounts, the default will be used.
47	AU	Default account for balancing journals		Integer, 4 digits	Must exist in the chart of accounts	8890	If the account is not found in the chart of accounts, the default will be used.
48	AV	Billing address		Text, 255 characters. Format: Name\Specifier\Street address\Postal code\City\Country code or Name\Street address\Postal code\City\Country code	Procountor checks the contents of the field to verify that 4 or 5 ”\” signs have been entered in the field. If there are 5 such signs, the second data item is the specifier. If there are 4, the second data item is the address.	A blank address will be generated from the business partner’s address (fields 4-9 (D-I) + field 54 (BB)).
49	AW	Delivery address		Text, 255 characters. Format: Name\Specifier\Street address\Postal code\City\Country code or Name\Street address\Postal code\City\Country code	Procountor checks the contents of the field to verify that 4 or 5 ”\” signs have been entered in the field. If there are 5 such signs, the second data item is the specifier. If there are 4, the second data item is the address.
50	AX	Electronic invoice reference	(X)	Text	The format will be verified		Mandatory if the invoice channel (field 26 (Z)) is e-invoice (3). Can be left empty if the business partner’s information is found in the e-invoice address database using the EDI (field 57 (BE)).
51	AY	Finvoice address 1 (no longer in use)		Not in use, must be left empty.			Separate Finvoice-channel is no longer in use. Therefore, this field must be left empty.
52	AZ	Finvoice address 2 (no longer in use)		Not in use, must be left empty.			Separate Finvoice-channel is no longer in use. Therefore, this field must be left empty.
53	BA	Customer number		Text, 40 characters	Customer numbers must be unique		This information is used to allocate the business partner to the business partner register.
54	BB	Specifier		Text, 80 characters			A specifier for the business partner’s address name. Not taken into account for persons (field 3 (C) = P).
55	BC	Bank’s SWIFT code	(X)	Text, 8-11 characters	The format will be verified		Mandatory if the payment method (field 16 (P)) is foreign payment.
56	BD	Electronic invoice operator		Text, electronic invoice operator’s EDI or SWIFT code	The format will be verified		Mandatory if the invoice channel (field 26 (Z)) is e-invoice (3). Can be left empty if the business partner’s information is found in the e-invoice address database using the Electronic invoice reference (field 50 (AX)) or EDI (field 57 (BE)).
57	BE	EDI of business partner		Integer, 12-17 digits	The format will be verified		Mandatory if the invoice channel (field 26 (Z)) is e-invoice (3). Can be left empty if the business partner’s information is found in the e-invoice address database using the Electronic invoice reference (field 50 (AX)).
58	BF	Number of factoring agreement		Text	The number must exist in Financing agreements view.		The business partner will receive the information of a factoring agreement from the Financing agreements view by the factoring agreement number that is in the import file.
59	BG	Number of cash discount days		Integer
60	BH	Cash discount percentage		Decimal	0-100

Business partner group record

Business partner group record is added below the business partner record that is going to be added to the business partner group in question. If all imported business partners are going to be added to business partner groups, there must then be at least one business partner group record below every business partner record. The amount of business partner groups for a single business partner is not limited; therefore, a single business partner can be added to multiple groups.

Num	Excel cell	Field name / Data item	Obligatoriness	Format	Permitted values / Verification	Default	Additional information
1	A	GROUP		Text, 5 characters	GROUP		If even a single GROUP row is found for a business partner, all of the partner’s group data will be deleted and the newly imported data will replace the existing data.
2	B	Group name		Text	If the group’s name cannot be found, a new group will be created in Procountor.
3	C	Name of main group		Text	If the main group’s name cannot be found, a new main group will be created in Procountor.
4	D	Report group		Text, 1 character	X		An X is entered in this field if the group is a report group. Each business partner can only have one report group. If more than one of the business partner’s rows has been marked with an X, the topmost will be selected as the report group.

Articles in this section