Invoice data import file – Procountor

The invoice data import file can be used to import invoices and journal receipts into Procountor.

Invoice data

Laskuaineiston siirtotiedosto esimerkki

The previous picture is an example of an Excel worksheet in a CSV format that includes invoice information. The imported invoices can be allocated to existing business partners and products in Procountor with using certain information.

Invoices are allocated to business partners in the following order:

Business ID / social security number / VAT number (field 5, E)
Bank account (not on sales invoices) (field 4, D)
Customer number (field 32, AF)

If a business partner does not have a business ID set in Procountor, the allocation can be done also with bank account or customer number (provided that the bank account or customer number has been saved to the business partner register).

When the aim is to allocate imported invoices to certain business partners, it is recommended to use the first factor, business ID. If the business ID is known and it has a corresponding value in the business partner register, the fields for bank account and customer number can be left empty: Procountor will then automatically fetch the bank account and customer number from the business partner register according to the given business ID.

If a sales invoice data import file includes a business ID and a customer number, from which only the business ID is correct, the sales invoice will be allocated to a certain business partner according to the business ID. However, since the customer number in the import file is wrong, the imported invoices will have a wrong customer number even when the customer is actually the right one (according to the business ID). The customer number in the business partner register, however, will not change as a result of the contradictory import file.

Respectively, the same occurs when importing purchase invoices with contradictory information: if a purchase invoice data import file includes a business ID and a bank account, from which only the business ID is correct, the purchase invoice will be allocated to a certain business partner according to the business ID. However, since the bank account in the import file is wrong, the imported invoice will have a wrong bank account even when the supplier is actually the right one (according to the business ID). The bank account in the business partner register, however, will not change as a result of the contradictory import file.

Using the bank account or customer number as a tool for allocation might be useful in a situation when there are several business partners in the business partner register with the same business ID. Invoices can be imported with different customer numbers and bank accounts to differentiate the desired business partner from the group of business partners that have the same business ID with each other.

The allocation of an invoice row is done by the following criterion:

Product code (field 3, C)

Invoices are posted in the following order:

According to the product’s default account if the invoice will be posted by rows
According to the business partner’s default accounts
According to the company’s default accounts

Invoices are dimensioned in the following order:

According to the dimensions in the import file
According to the product’s default dimensions
According to the business partner’s default dimensions

Import file

The invoice data import file is brought to Procountor in a 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 an invoice record, invoice row record or a dimension 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.

This article focuses on import files that are in CSV format. Import files can also be read in the following to additional formats:

DDE: DDE is a special import file format created by the Docusend software. DDE files include the basic info and images of scanned purchase invoices.
ZIP: ZIP files must always include the CSV file containing the invoice data. The ZIP package can also include other files as file attachments to the invoices. The names of file attachments are stated in field 34 (AH) of the corresponding invoice record. Scandinavian characters (åäö) may not be used in the names of files packed in ZIP archives.

Extended export file with invoice row details

If it is needed to transfer invoice data between two Procountor environments, the Extended export file with invoice row details is a useful tool. This function is not active by default, but it can be activated by contacting the Procountor’s customer service. There are no fees with activating or using the function.

When the Extended export file with invoice row details is activated, the file can be formed in the following way:

Search the invoices to be in the file using Search > Receipt search.
Click the right button of the mouse over the search results.
Choose Extended export file with invoice row details from the list.

The file contains the invoice information in a form that matches to the record’s description. Before transferring the whole material, it is recommended to first test the function with a few invoices that the information is being transferred in the desired way.

Records

The invoice data import file may contain three types of records:

Invoice records, containing the basic invoice info.
Invoice row records, containing the data from the invoice rows of the Invoice record specified above (recommended, not mandatory). Each Invoice record can have an infinite number of Invoice row records.
Dimension record, stating the dimensions and items to which the above-mentioned Invoice record or Invoice row record is allocated (not mandatory). Each Invoice record and Invoice row record can have an infinite number of Dimension records.

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

No.: 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

Invoice record

This record consists the basic information about invoices. Each invoice has one record (one row). The Default column includes the information that is fetched from the business partner register if the corresponding information is not brought to Procountor within the import file. The fields that have been filled with information from the register are highlighted with yellow color in the import preview.

Payment terms: there is no field for this data item in the file. If the file’s due date (field 15 (O)) is blank, the due date will be calculated based on the information found in the business partner register (by default, this is the same as the general setting in Usage settings). If payment terms have not been defined in the Usage settings, the payment terms will be the Procountor’s default, 10 days. If the due date is specified in the file, payment terms will be calculated based on the due date and invoice date (field 13 (M)).

No.	Excel cell	Field name / Data item	Obligatoriness	Format	Permitted values / Verification	Default	Additional information
1	A	Invoice type	X	Text, 1 character	O=Purchase invoice M=Sales invoice T=Travel invoice K=Bill of charges N=Journal receipt		Mandatory in invoice records. If empty, the row will be processed as an invoice row or dimension record.
2	B	Currency code		Text, 3 characters	The code must exist in Procountor’s currency register.	EUR	The currency rate is specified field 23 (W).
3	C	Bank reference code		Integer, 2–20 digits	The format will be verified if it is brought in the file.	For sales and travel invoices and bills of charges, this is generated from the invoice number. If the invoice or bill is allocated to the business partner register, the partner’s standard reference number will be used.	This field may be empty for purchase invoices, even if the payment method, field 6 (F), is bank transfer.
4	D	Bank account		nnnnnn-nnnnnnnn or IBAN	The format will be verified.	If the invoice is allocated to the business partner register, this will be retrieved from the register. If a sales invoice is not allocated to the register, "Default account for invoices" will be retrieved from the company’s basic info.	This information will then be used to allocate the purchase invoice to the business partner register. Invalid bank account numbers will be cleared.
5	E	Business ID/SSN/VAT number		Text, 40 characters, with or without Business ID type (country code).		Retrieved from the register if the invoice is allocated to the business partner register.	This information will be used to allocate the invoice to the business partner register. If the data item is a VAT number in an acceptable format, this will be entered in the invoice’s VAT number field.
6	F	Payment method		Text	Bank transfer, direct debit, direct payment, clearing, credit card charge, foreign payment, other or empty	Bank transfer	If the payment method is written in a form that differs from the listed methods, the import fill be faulty.
7	G	Name of business partner	(X)	Text, 80 characters		Retrieved from the register if the invoice is allocated to the business partner register.	May be empty on a purchase invoice. May be empty on other invoices if the invoice is allocated to the business partner register.
8	H	Method of delivery		Text	Mailing, online, freight, courier service, VR cargo, bus, pick-up or empty		Only informative; the method of delivery is visible in the upper section of an invoice. If written in a different form compared to ones on the list, the field will be emptied.
9	I	Invoice discount %		Decimal	0–100		Rounded to the closest two decimals
10	J	Incl. VAT number		Text, 1 character	t or f	Default = f. If the invoice contains no rows, the sum always includes VAT, i.e. this field has a value of ”t”. This data item is always processed as t on travel invoices and bills of charges.	The code indicates whether or not the invoice rows include VAT. t = includes VAT, and f = does not include VAT.
11	K	Credit invoice code		Text, 1 character	t or f	t	This code indicates whether the invoice is a debit or credit invoice. This data item is for information only and does not influence values such as the invoice sum. The sums of credit invoices must be entered as negative figures. t = debit invoice and f = credit invoice.
12	L	Penal interest %		Decimal	0–100	8.00
13	M	Invoice date		Date	dd.mm.yyyy	The current date	Invoices cannot be entered for closed financial periods. If this is attempted, the next possible open date will be assigned as the invoice date.
14	N	Delivery date		Date	dd.mm.yyyy
15	O	Due date		Date	dd.mm.yyyy	Invoice date + payment terms	Must be greater than the invoice date, field 13 (M).
16	P	Address of business partner		Text, 255 characters Format: Specifier\Address\Postal code\Town\Country code or Address\Postal code\Town\Country code	Procountor checks the contents of the field to verify that 3 or 4 "\" signs have been entered in the field. If there are 4 such signs, the first data item is the specifier. If there are 3, the first data item is the address.	Retrieved from the register if the invoice is allocated to the business partner register.	The country code must be given in the format determined by ISO 3166-1 alpha-2 standard, i.e. consisting of two letters.
17	Q	Billing address	(X)	Text, 255 characters Format: Name\Specifier\Address\Postal code\Town\Country code or Name\Address\Postal code\Town\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.	This is primarily retrieved from the register if the invoice is allocated to the business partner register. Secondarily, a blank address is generated from the business partner’s address (field 7 (G) + field 16 (P)).	Billing address information is only used for sales invoices. Mandatory if invoice channel, field 26 (Z), is 2 (Post).
18	R	Delivery address		Text, 255 characters. Format: Name\Specifier\Address\Postal code\Town\Country code or Name\Address\Postal code\Town\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.	Retrieved from the register if the invoice is allocated to the business partner register.	Delivery address information is used for sales and purchase invoices.
19	S	Additional invoice info		Text, 500 characters		The customer’s default additional info is retrieved from the register if the sales invoice is allocated to the business partner register. If the business partner does not have default additional info or the sales invoice is not allocated to the business partner register, the company’s default additional info will be retrieved.	The additional info is visible on the invoice sent to the customer.
20	T	Notes		Text, 500 characters			Notes are not visible on the invoice sent to the customer.
21	U	Email address		Text, 80 characters	The format will be verified.	Retrieved from the register if the invoice is allocated to the business partner register.	If the field is empty and the invoice channel, field 26 (Z), is 1 (Email), the invoice channel will be changed to 2 (Post).
22	V	Payment date		Date	dd.mm.yyyy		Does not affect the invoice’s status.
23	W	Currency rate		Decimal		Retrieved from Procountor’s currency rates according to the invoice date.
24	X	Invoice sum total	(X)	Decimal	Mandatory if the invoice contains no rows.		The sum is indicated in the invoice’s currency. If the invoice contains no invoice rows, a single invoice row with this data item as its sum will be generated. If the invoice contains invoice rows, the program checks that this sum matches with the total sum of the rows.
25	Y	VAT %	(X)	Integer	0, 8, 9, 10, 12, 13, 14, 17, 22, 23 or 24		Used if the invoice contains no invoice rows. If the invoice does contain rows, this data item is unnecessary.
26	Z	Invoice channel		Integer	1=Email 2=Post 3=E-invoice 6=No sending	Retrieved from the register if the invoice is allocated to the business partner register. If the invoice is not allocated, the value of this field is 2 (Post).	If the value is 1 (Email), field 21 (U) must be correct. If the value is 2 (Post), field 17 (Q) must be correct. If the value is 3 (E-invoice), field 27 (AA) or 38 (AL) must be correct. If fields 27 (AA), 37 (AK) and 38 (AL) are empty, the invoice channel will be changed to 2 (Post)
27	AA	Electronic invoice reference	(X)	Text	The format will be verified.	Retrieved from the register if the invoice is allocated to the business partner register.	Mandatory if the invoice channel (field 26 (Z)) is e-invoice (3).
28	AB	Order reference		Text, 70 characters			The text written on this field will be shown as a message in payment transaction.
29	AC	Accounting by rows code		Text, 1 character	t or f	Retrieved from the company’s basic info according to the invoice type.	This code indicates whether accounting will be performed by row or by entry. t = by row, and f = by entry.
30	AD	Finvoice address 1 (no longer in use)		Not in use, must be left empty.			The separate Finvoice channel is not in use anymore; therefore this field must be left empty.
31	AE	Finvoice address 2 (no longer in use)		Not in use, must be left empty.			The separate Finvoice channel is not in use anymore; therefore this field must be left empty.
32	AF	Customer number		Text, 40 characters		Retrieved from the register if the invoice is allocated to the business partner register.	The information is used to allocate the invoice to the business partner register.
33	AG	Automatic sending or marked paid data items		Text, 1 character	X or M		X means that the invoice will be marked for sending (sales invoice) or to approval circulation (purchase invoice). M means that a payment transaction will be created for the invoice and the invoice’s status will be set to ”marked paid”. If the value of this field is X, Sales invoices will be sent to the customer if the invoice channel is not No sending. If the invoice channel of a sales invoice is No sending, the invoice’s status will be set to Not sent. If the value of this field is X in a Purchase invoice, the verifier and approver data items will be added to the invoice (they are fetched from business partner register or from Usage settings). The invoice then goes to approval circulation if it is in use.
34	AH	Name of file attachment included in ZIP archive		Text (the name of the file with the filename extension)			A file in the ZIP archive with a name corresponding to the contents of this field will be attached to the invoice in question. Attachments in .txt format will be sent with sales invoices. Attachments in other formats will not be sent. A single invoice can have one file attachment, and each file can only be attached to a single invoice. Scandinavian characters (åäö) may not be used in the file names.
35	AI	Contact person		Text, 255 characters		Retrieved from the register if the invoice is allocated to the business partner register.	Not taken into account for travel invoices and bills of charges.
36	AJ	SWIFT code of business partner’s bank	(X)	Text, 8–11 characters	The format will be verified.	Retrieved from the register if a purchase invoice’s payment method (field 6 (F)) is foreign payment, and the invoice is allocated to the business partner register.	Mandatory if the payment method (field 6 (F)) is foreign payment.
37	AK	Electronic invoice operator	(X)	Text	EDI or SWIFT code. The format will be verified.	Retrieved from the register if the invoice is allocated to the business partner register.	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 27 (AA)) or EDI (field 38 (AL)).
38	AL	EDI of business partner	(X)	Integer, 12–17 digits	The format will be verified.	Retrieved from the register if the invoice is allocated to the business partner register.	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 27 (AA)).
39	AM	Invoice sender’s invoice number		Text			Invoice sender's (on a purchase invoice) or third-party system’s invoice number (on a sales invoice). Using the third-party system's invoice number must be agreed with Procountor separately.
40	AN	Number of factoring agreement		Text	Must exist in financing agreements.		This data item is used to allocate the invoice to a third-party financing agreement. Only used on sales invoices.
41	AO	Country code for VAT processing		Text, 2 characters	Must be selected in the VAT processing rules.		This data item is only available by specific agreement with Procountor. The value in this field affects the permitted values for VAT % in the invoice row record.
42	AP	Language code		Integer, 1 digit	1=English 2=Finnish 3=Swedish 4=Estonian 5=Norwegian 6=Danish	Retrieved from the register if the invoice is allocated to the business partner register, otherwise the default is 2 (Finnish).	If the value is not acceptable, the default value will be used.
43	AQ	Number of cash discount days		Integer
44	AR	Cash discount percentage		Decimal	0–100
45	AS	VAT discount percentage		Integer	0–100	100	NOTE! This field is only used when N=Journal receipt has been defined as the invoice type in column A of the file.
46	AT	VAT type		Text, 1 character	P=Purchase S=Sales	P=Purchase	NOTE! This field is only used when N=Journal receipt has been defined as the invoice type in column A of the file.
47	AU	VAT status		vat_XX (in which XX must be replaced with the correct number according to the listing)	The listing of the status codes is found behind the link below: VAT codes	vat_12=No VAT processing	NOTE! This field is only used when N=Journal receipt has been defined as the invoice type in column A of the file. Example: the content of the field is vat_22 -> the status of the journal is EU, community sales (service). Please verify that the VAT status in question matches the VAT type.

Invoice row record

If the invoice contains row data, this data will be entered on the row below the basic invoice info with the first field (A) left empty. The rest of the fields will be filled in according to the table below. Each invoice record can include an unlimited amount of invoice row records. If the invoices do not contain invoice row records, the invoice record’s total sum (field 24 (X)) and VAT (field 25 (Y)) are used to generate a single invoice row. If the total invoice sum is specified in the invoice record and it does not match the total sum of the rows, the invoice will become invalid and it is displayed in red on the import preview screen.

No.	Excel cell	Field name / Data item	Obligatoriness	Format	Permitted values / Verification	Default	Additional information
1	A	EMPTY			Empty
2	B	Product description		Text, 80 characters	Something other than DIMENSION.	Retrieved from the product register if the product is allocated to the product register, otherwise –.	If – in the data, retrieved from the product register if the product is allocated to it.
3	C	Product code		Text, 80 characters			Used to allocate the product to the product register.
4	D	Quantity		Decimal		1
5	E	Unit		Text	All values that are listed in Invoice settings, for example pcs, kg, g, km, etc.		The unit is not retrieved from the product register. If units are not specified in the data to be imported or the value is in a faulty form, this field will be left empty.
6	F	Unit price		Decimal		0	Price in the invoice currency. The unit price is not retrieved from the product register, but must be specified in the import file. If this is not the case, the unit price will be set to 0.
7	G	Row’s discount percentage		Decimal	0–100 or empty	0	Rounded to the closest two decimals.
8	H	Row’s VAT %		Integer	0, 8, 9, 10, 12, 13, 14, 17, 22, 23, 24 or empty. If VAT processing country codes are used, this field will contain the percentages of the country in question.	0	The row’s VAT % is not retrieved from the product register. If the percentage is not specified in the data, the value will be 0.
9	I	Comment row		Text, 255 characters
10	J	(not in use)					NOTE! This column is not used and should be left empty.
11	K	(not in use)					NOTE! This column is not used and should be left empty.
12	L	(not in use)					NOTE! This column is not used and should be left empty.
13	M	(not in use)					NOTE! This column is not used and should be left empty.
14	N	Account		Text, 4 characters	Must exist in the chart of accounts	Retrieved from the register.	Column N, or account, is retrieved from the product register. If accounting by row will be applied to the invoice, the four-digit account number corresponding to the invoice row can be entered in this field.
15	O	VAT deduction percentage		Integer	0–100	100	NOTE! This field is only used when N=Journal receipt has been defined as the invoice type in column A of the file.
16	P	VAT type		Text, 1 character	P=Purchase S=Sales	P=Purchase	NOTE! This field is only used when N=Journal receipt has been defined as the invoice type in column A of the file.
17	Q	VAT status		vat_XX (in which XX must be replaced with the correct number according to the listing)	The listing of the status codes is found behind the link below: VAT codes	vat_12=No VAT processing	NOTE! This field is only used when N=Journal receipt has been defined as the invoice type in column A of the file. Example: the content of the field is vat_22 -> the status of the journal is EU, community sales (service). Please verify that the VAT status in question matches the VAT type.

Dimension record

If the invoice or invoice row contains dimension data, this data is entered on the row below the basic invoice info or row info, with the first field (A) left empty. The remaining fields will be filled according to the table below. Each invoice record or invoice row record can include an unlimited amount of dimension records.

No.	Excel cell	Field name / Data item	Obligatoriness	Format	Permitted values / Verification	Default	Additional information
1	A	EMPTY			Empty
2	B	DIMENSION	X	Text, 8 characters	DIMENSION		The word must be written in capitals.
3	C	Dimension row type		Text, 1 character	R or L	L	R = Invoice-row-specific dimension – the invoice row above the dimension row will be dimensioned. L = Invoice-specific dimension – the invoice above the dimension row will be dimensioned. If the invoice includes both R and L rows, the data on the L row will be copied to all other invoice rows apart from R rows. If any dimension rows have been provided for the invoice, accounting will automatically be performed by row.
4	D	Dimension	X	Text, 255 characters	Must exist in Procountor with the exactly same form		If the dimension cannot be found, the invoice will not be saved in the system, but will be displayed in red as an invalid row on the import preview page, provided that the dimension type (field 3(C)) is invoice-specific dimension (L). If the dimension is invoice-row-specific (R), the invoice will be saved in the system without being dimensioned.
5	E	Item	X	Text, 255 characters			If the item cannot be found, it will be created.
6	F	Per cent	X	Decimal, 2 decimal places	0–100		The total sum of the percentages within an invoice/invoice row and one dimension must be 100. If the sum is not 100, the invoice will not be saved in the system, but will be displayed in red as an invalid row on the import preview page.

Articles in this section