Import of Sales Invoices

Import of sales invoices

In the mKsiegowa.pl program, it is possible to import a 'bundle' of sales invoices from text files of CSV and JPK_VAT type. Such files can be prepared, for example, with the use of Excel.

Please contact us in case the import option is not visible in the menu of your program.

Note: Import of invoices does not support inventory management in this version. Invoices with items defined as Purchased or Product are not imported.

Import of invoices from JPK_VAT file

JPK_VAT format is a mandatory format of data exchange between a company and the tax authority. It can be also used to exchange data between systems.

How to configure the import from JPK_VAT?

JPK_VAT files contain only the summary of invoices and not the detailed positions. We have adopted the following method to import them:

for each VAT rate, an item with the appropriate settings should be prepared:
- code 730-1-23% for VAT rates of 23%
- code 730-1-8% for VAT rates of 8%
- code 730-1-5% for VAT rates of 5%
they should be defined in the Item list.
all should have set Type = Service

How import from JPK_VAT work:

custome data is being analysed based on their NIP (GST) number:
- if the customer with a NIP number from JPK_VAT file does not exist in the Customer list, it is added to the Customer list based on JPK_VAT attributes:
  - for K_15 to K_20 the customer is assumed the be domestic
  - for K_12 or K_21 it is assumed to be an EU based company
  - for K_11 or K_22 it is assumed to be a non-EU company
- only letters and numbers are interpreted from the NIP number; other characters are ignored;
- if the NIP number exists in Customer database, the invoice is imported and connected with this Customer;
- new customers are assigned currency PLN;
- for new Customers the Accounts Receivable Account is set according to the System and General GL Setup:
  - for domestic companies: Domestic Receivable Account
  - for EU and non-EU companies: Foreign Receivable Account
the following invoices are imported:
- domestic (attributes K_15 to K_20)
- WDT - export of goods to EU based companies (attributes K_12 or K_21)
- export of goods to non-EU based companies (attributes K_11 or K_22)
documents with negative value are imported as Sales Credit Notes:
- domestic (attributes K_15 to K_20)

Notes:

In the current software version, export Sales Credit Notes are not imported - the documents are ignored;
the JPK_VAT file should not contain more than 3000 documents.

Import from CSV files

Sale Invoices can be imported also from 2 CSV files:

first with document headers
second with document lines

Enter the location of previously prepared CSV files containing invoices on the import screen.

Format of CSV files

The files should be prepared according to the following rules:

all values should have a dot separating the decimal point
dates should be provided in the YYYY-MM-DD format
if the customer is not found in the CSV header file, it will be added to the database based on data from the header file. During data import, the program searches for a contractor according to the NIP number. When comparing the NIP number loaded from the file with NIP numbers in the file, the program eliminates all characters except numbers and letters. For new customers, the NIP tax identification number is entered to the database in the format specified in the CSV file.
Sales Credit Notes are not supported
empty lines are skipped
the document should not contain characters other than 7-bit ASCII characters and Polish characters in the selected format
CSV files should not contain more than 1000 documents

Format of CSV header file

No	Field name	Field type	Comments
1	nr dok	varchar(20)	Document number. Each sales invoice must have a unique number. The field is used to associate the corresponding invoice lines in the line item file with the header.
2	nazwa kontrahenta	varchar(60)	Counterparty name. Used when the customer was not previously in the system - the customer is added with this name.
3	NIP	varchar(25)	Customer's NIP (tax number). Used to identify the customer in the system. If the tax id number does not exist in the system, the contractor will be added with this ID.
4	adres1	varchar(60)	The first line of the counterparty's address. Used when the customer was not previously in the system.
5	adres2	varchar(60)	The second line of the counterparty's address. Used when the customer was not previously in the system.
6	waluta	varchar(3)	If the customer was not previously in the system, the new customer will be added with the currency specified in this field. The currency must exist in the currency table. Note: the field exchange rate must be completed. If the customer already existed in the system and the invoice has a different currency that the customer, the invoice will be ignored.
7	grupa podatkowa	varchar(60)	Customer's tax group identifier. Used when the customer was not previously in the system. Select the appropriate tax group from the list, eg Domestic Contractor, Export / Import, EU Contractor
8	numer faktury dla korekty	varchar(60)	The field is ignored for sales invoices. For Credit Notes - the reference number of the sales invoice to which the correction is loaded. Note: An invoice with this number must exist.
9	komentarz	tinytext(0 – 256 znaków)	Comment - will be put in the invoice comment section
10	data faktury	date	Invoice submission date
11	data sprzedaży		Sale date - this date is used to generate automatic delivery document is stock management is used
12	data płatnosci	date	Payment due date
13	data VAT	date	VAT date
14	kurs waluty	double	Currency rate for invoices in foreign currency; for customers in PLN, provide the value = 1.
15	wartość	double	Total gross invoice value. The value is displayed on the screen but the real value is calculated from the second file.
16	kod magazynu	varchar(20)	Location code. Used only for stock management.
17	Oznaczenie GTU	varchar(20)	Optional filed is used for JPK designation for the delivery and service provision GTU_01 to GTU_13.
18	Oznaczenie PROC	varchar(20)	Optional filed is used for JPK designation for the delivery and service procedure
19	Oznaczenie oddziału klienta	varchar(20)	Optional field. If the program finds a branch with this code, it is included in the document. If the branch is not found or the field is empty, the program assumes the default department

Format of CSV file with document lines

Comments

Lp	Field name	Field type	Comments
1	nr_dok	varchar(20)	Document number. For all lines of a specific invoice, the field must have the same content as the nr_doc field in the header file.
2	nr kartoteki	varchar(20)	Product, goods or service code. The code must exist in the system. In the case of goods, a sufficient amount of goods must be available in the stock. Otherwise, the invoice will not be imported.
3	cena_netto	double	Net unit price. The value must be positive for sales invoices. It can be negative for credit notes. If the invoice is in a foreign currency, the program saves the invoice item with the value in that currency. The invoice will be posted with conversion according to the currency rate provided in the header file
4	kwota_VAT	double	VAT amount. In the current version of the program, the field is ignored - the VAT value on the invoice is calculated based on the subject VAT rate set in the system and the contractor's VAT category (Domestic, non-EU, EU).
5	ilość	double	quantity

Import of CSV files

After clicking Import file, the program first checks the correctness of the file and presents its contents on the screen.

In the following lines, customers (darker lines) and invoices (lighter lines) are presented alternately.

Comments and error messages appear in the right hand side column:

information whether the customer was found in the system
information on the correctness of the invoice data
other messages

After clicking Import data, the invoices will be loaded.

Example of CSV files

Header file:

 FV1/2018;nazwa-firmy;123456789;adres1;adres2;PLN;Kontrahent krajowy;;komentarz;2018-01-31;2018-01-31;2018-01-31;2018-01-31;1;246;DEF
 FV2/2018;firma-import;987654321;adres1;adres2;USD;Export/Import;;komentarz;2018-01-31;2018-01-31;2018-01-31;2018-01-31;3.2;246;DEF

File with invoice lines:

 FV1/2018;d1;200;46;1
 FV2/2018;d2;100;23;1
 FV2/2018;d3;100;23;1

The above files should be imported with the following settings:

Skip headers not checked
Text Delimiter = apostrophe
Field separator = semicolon