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.
Updating the information of an existing business partner
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)
Note! In the import file used to update the information of existing business partners, all the fields that you want to retain in Procountor's business partner register must be filled in (i.e., also any non-mandatory information). If there are empty fields for a business partner in the import file, those fields will also be emptied in Procountor's business partner register when the information is matched from the file to the existing business partner register.
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
|
|
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.
|
|
|
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
|
|
25
|
Y
|
Homepage
|
|
Text, 255 characters
|
|
|
|
26
|
Z
|
Invoice channel
|
|
Integer
|
1 = Email
|
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.
or
|
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.
Name\Specifier\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.
|