Intro
In the mKsiegowa.pl program, you can import 'packages' of purchase invoices from text files of CSV type. Such files can be prepared, for example, with the use of Excel. The import of purchase invoices is based on a text file that contains appropriate entries that represent invoices.
How you can get files prepared?
Purchase invoices are always imported on the basis of 2 files, of which:
- the first contains invoice headers
- the second contains invoice positions
Relationship between them is based on an unique invoice number.
The import can be started in the Purchase module. Provide the location of 2 text files containing invoices and select the prefix of the reference number of purchase invoices.
File format
Prepare your files using the following rules:
- all prices in PLN
- date format: YYYY-MM-DD
- if the supplier is not found in the database, it will be added on the basis of data imported from the header file. During data import, the program will search for the supplier using NIP number. When comparing the NIP number loaded from the file with the NIP numbers in the database, the program eliminates all characters except numbers. For new suppliers, the NIP tax identification number is entered as specified in the header file.
- fields separator is ;
- text fields should be separated by single apostrophe
- credit notes are not implemented
- empty lines are ignored
- the accounting templates should be prepared in the item database as follows:
- item code should be created as per the template: account_VATrate_VATcategory (eg. 401-01_23_0)
- the name of the item can be anything you like, although it is recommended to enter the account name and VAT rate
- the VAT rate, VAT category and COGS account code must be as specified respectively (as specified in the item code)
The VAT categories are numbered as follows:
- Other purchase - 0
- Fixet assets - 2
- Other services - 4
- Partial VAT - 6
- Simplified import procedure - 8
As a result of import, the program saves purchase invoices with items from position file.
Header file format
| Lp | Field name | Field type | Comments |
|---|---|---|---|
| 1 | version | varchar(2) | characters '01' |
| 2 | nr_dok | varchar(20) | Document number. You can enter the invoice number given by the supplier or another registration number. The field is used to associate the corresponding invoice lines in the invoice position file with the header. |
| 3 | nazwa_dostawcy | varchar(60) | Supplier Name. The field is used for new suppliers. |
| 4 | NIP | varchar(25) | Supplier NIP number. The number is used to identify the supplier in the database. |
| 5 | adres1 | varchar(60) | The first line is the supplier's addresses. The field is used for new suppliers. |
| 6 | adres2 | varchar(60) | The second line is the supplier's addresses. The field is used for new suppliers. |
| 7 | symbol_dok | varchar(2) | 'FV' characters |
| 8 | id_dok | varchar(60) | Document id assigned by the supplier. |
| 9 | numer_korekty | varchar(60) | Empty field |
| 10 | komentarz | tinytext(0 – 256 znaków) | Comment - will be saved in the comment setion of the invoice |
| 11 | data_faktury | date | Date of invoice submission |
| 12 | data_otrzymania | date | Date of invoice arrival |
| 13 | data_platnosci | date | Date of invoice payment |
| 14 | data_VAT | date | VAT date |
| 15 | wartosc | double | Total value of the invoice with VAT. The value is ignored and maybe supplied just for check. |
Format pliku pozycji faktur
| Lp | Field name | Field type | Comment |
|---|---|---|---|
| 1 | nr_dok | varchar(20) | Document number. The field is used to associate the corresponding invoice lines in the invoice position file with the header. |
| 2 | VAT | varchar(2) | VAT rates accepted: '23', '8', '5' , '0' , 'zw' |
| 3 | wart_netto | double |
Net value of the invoice for VAT specified in the field above. Positive value. |
| 4 | wart_VAT | double | VAT value of the invoice for VAT specified in the field above. Positive value provided for a reference. The program calculates VAT from net value and VAT specified in the database. |
| 5 | kategoria_VAT | int(11) | VAT category |
| 6 | konto | varchar(15) | item code described above |
File Import
After Read file is clicked the program shows the content of files on the screen. At this point it is possible to see any errors.
The following lines present suppliers (darker lines) and invoices (lighter lines).
Right column is used for error messages:
- information if the supplier was found in database
- information on invoice data correctness
Click Import data if all ok - invoices will be saved in the system.
Each invoice will have also generated GRN document (not important for invoices with services or cost invoices).
Example file
Invoice headers:
01;0440/20;POLSKI KONCERN NAFTOWY ORLEN S.A.;7740001454;CHEMIKÓW 7;Stara Biała;FV;99;;;2020-05-29;2020-05-08;2020-05-08;2020-05-08;155.36
Invoice positions:
0440/20;23;100;23;0;402-1