DATA IMPORT FROM CSV FILES

(1)

GKK, Armii Ludowej 21, 51-214 Wroclaw

DATA

IMPORT

FROM

CSV

FILES

SPECIFICATION OF THE FILE

Version2.0 of 2001-02-01

Document’s no

2006/IT-P/??

Document’s category

Project

Document’s status External elaboration Key words

(2)

2006/IT-P/?? Document’s attributes Attribute Value A B 1 _Number _2005/IT-P/02 2 Project Yonick

3 Title Data import from CSV files 4 _Subtitle _{Specification of the file}

5 Version 1.1

6 Dated 2006.04.20

7 _Category _Project

8 File Yonick 1 1.doc

9 Location http://www.krd.pl/exchange/public/Dział Informatyczny/Dokumenty/Archiwum/Projekty 10 Number of pages 12

11 _Template _Normal.dot 12 Instruction <NONE>

13 Authors Radek Soska, Sebastian T. Tkocz, Maciej Kasierski 14 Supervision Sebastian Tkocz

15 Department IT Department 16 _{Contact via email} _{[email protected]} 17 Contact via telephone +48(71)7850213

Document’s history

Attribute Value Date

A B C

1 _Version _1.0 _2005-04-10

2 _Author _{Sebastian Tkocz}

3 _{Content checked by} _{Radosław Soska} _2005-04-25

4 _{Form checked by} 5 _{Approved by}

6 Description First version

Attribute Value Date

Version 1.1 2006-04-20

Author Maciej Kasierski

Content checked by Radosław Soska 2006-04-20

Form checked by Approved by

Description Second version

Atrybut Value Date

Version 1.2 2006-08-28

Content checked by Rafał Stramski 2006-08-28

Description Adding “suspension” and “notification” to the secondo version

Atrybut Value Date

Version 2.0 2011-02-15

Content checked by Rafał Stramski 2011-02-15

(3)

2006/IT-P/??

Table of content

TABLE OF CONTENT ... BŁĄD! NIE ZDEFINIOWANO ZAKŁADKI. INTRODUCTION ... BŁĄD! NIE ZDEFINIOWANO ZAKŁADKI. 1. DATA PASSED TO THE BUREAU ... BŁĄD! NIE ZDEFINIOWANO ZAKŁADKI.

1.1. STEERING DATA ... BŁĄD!NIE ZDEFINIOWANO ZAKŁADKI.

1.2. BUSINESS INFORMATION ... BŁĄD!NIE ZDEFINIOWANO ZAKŁADKI.

1.2.1. Debtor’s data ... Błąd! Nie zdefiniowano zakładki. 1.2.2. Data on obligation ... Błąd! Nie zdefiniowano zakładki. 1.2.3. Suspension and notification data ... 8 1.2.4. Data of obligations with execution clause ... 9

2. TYPES OF ORDERS ... BŁĄD! NIE ZDEFINIOWANO ZAKŁADKI.

2.1. ORDERS REGARDING BUSINESS INFORMATION ...10 2.2. OPTIONS OF ORDERS ... BŁĄD!NIE ZDEFINIOWANO ZAKŁADKI.

2.3. CONFIRMATIONS ... BŁĄD!NIE ZDEFINIOWANO ZAKŁADKI. 3. OUTPUT FILE ... BŁĄD! NIE ZDEFINIOWANO ZAKŁADKI.

(4)

2006/IT-P/??

Introduction

The NICCI protocol allows for defining orders for KRD system in a form of XML files. The orders include among others adding, update and removal of different types of business information, monitoring management etc. However, due to diversity of operations and data, on chich these operations are executed, the structure of NICCI file is quite complicated.

Yonick is the answer to the needs of those clients, who do not take advantage of a full functionality of the NICCI protocol, but who look for an easy way of automating the process of passing debtors’ data to the KRD base. That is why Yonick choses for a base a CSV text file format instead of XML format used in the NICCI protocol. It also limits the quantity of operations (adding, update and removal, suspension and unspuspension of cases) and types of data (only business information on unpaid obligations) that it is able to operate.

Files in the Yonick format shall be converted into NICCI tranche files in client’s IT systems and in such form sent to the KRD. This task shall be performer by e.g. the SABAR program SABAR – the client of SIDDIN protocol, chich sends NICCI tranches to the KRD.

This document describes a structure of CSV file, that is compatible with the Yonick protocol.

(5)

2006/IT-P/??

1. Data passed to the Bureau

Data that can be passed to the business information bureau is regulated by an act. It determines a minimal and maximal range of data, as well as conditions to be met so that the bureau was empowered to accept this data.

One of the types of business information defined by the Yonick protocol is the information on unpaid obligations of business entities. A single line of a CSV file defines one unpaid obligation.

Obligations towards one debtor can be grouped into a case, provided that they are also grouped in the CSV file, that is that they are put in the sequential lines of the CSV file and that a case identifier and debtor’s data is identical in every line. If the grouping does not take place, then cases containing single obligations will be passed to the KRD system. It is significant due to the fact that certain conditions required for acceptance of data by KRD (e.g. a total, minimal sum of obligations) are being checked at the case level. A case is visible only to a client entering this case. For third parties, to whom business information on a debtor shall be disclosed, obligations of one case shall be seen as decoupled (except from creditor’s data, unless he reserved its disclosure) business information regarding unpaid obligations.

1.1. Steering data

Data transferred with the Yonick protocol to the KRD system constitutes orders of executing special operations: e.g. adding new business information to the KRD base. A type of operation commissioned to be executed by the KRD system, its possibile options and a type of business information, on chich the given business operations shall be performed, are icluded in the Yonick protocol in the first two columns.

Table 1 : Steering data

Types of operations and their options are described in the second chapter. Case’s identifier (meaning of a case is explained in the subsection 1.2) is necessary for later modification/removal of cases with the Yonick protocol. An identifier may be any string of maximal length 128 characters. It has to be unique within a client (no need to worry about superposition of identifiers with other bureau’s customers).

An identifier should be placed in the third column of a file.

1.2. Business information

The act defines four types of business information that can be stored in business information bureaus. This is information on unpaid obligations of consumers and etrepreneurs, information on repa id obligations and information on use of a false or a stolen document. The Yonick protocol allows for operating only business information on unpaid obligations. In the event of such information the KRD BIG S.A. creates a case.

A case is a group of business information regarding unpaid obligations of a single debtor towards the client reporting this information. The KRD operates a case in order to allow for adding within a single transaction more than one Number(s) of

column(s) Column’s name Description Notes Required

A B C C D

1 1 OPERATIONTYPE Type of comissioned operations

together with possibile options operation codes Three-letter YES 2 2 INFORMATIONTYPE Type of business information Two-letter code of

CM or LP YES

3 3 CASEID Case’s identifier Max. 128

(6)

2006/IT-P/??

obligation of a debtor and to facilitate management over this obligations, e.g. allow for update of debtor’s data in one place. A debtor may be a consumer or a legal person.

A case consists of an identifier, debtor’s data and data of obligations (at east one obligation).

1.2.1. Debtor’s data

A debtor defined in the Yonick file can be a natural or legal person. However, only the entities determined in the act can provide business information regarding consumers (natural persons). A distinction, whether the reported debt belongs to an entrepreneur (even a single person) or to a natural person, takes place on the basis of data’s range – if file’s tuple contains NIP number, then a debt is considered as entrepreneur’s debt (no matter if PESEL number is also provided)

In order to determine whether a debtor is a natural or a legal person, enter the CM code (for a consumer) or LP code (for a legal person) in the second column.

1.2.1.1. Business entity

The required data of an enterprise (“a subject being a legal person or an organization entity without legal personality”) is : designation, seat and address and tax identification number (NIP). Optionally we can give REGON number, number under which the subject is entered in appropriate register (together with determination of a district court) and a main subject of business activity.

A tax identification number can be given as a string of 10 digits without dashes or as separated by dashes: 3, 3, 2 and 2 digits (e.g. 112-123-09-01) or 3, 2, 2 and 3 digits (e.g. 112-21-30-901). REGON Number consists of 9 digits.

An address in the Yonick protocol is defined as a string of 2 to 4 fields, chich should contain all data necessary for delivery of a dispatch, that is at least postal code and city, and property’s details (e.g. street address) .

A postal code and city’s name have to be entered in one field (the system identifies city’s name by a postal code standing before it).

The system recognizes the following articles in property’s name: st.- street, ave.- avenue, pl.- place

Other articles shall be treated as name’s components.

If a debtor is a private enterprise, we may give data of a natural person constituting this enterprise. This data does not have to meet statutory requirements regarding the range of required data of a natural person.

Table 2 : Enterprise’s data

The Yonick protocol is not capable of giving names and surnames of people who make up management bodies, proxies or attorneys of this entity. There is no possibility of entering information on cooperators or shareholders of a debtor either.

Number(s) of

column(s) Column’s name Description Notes REquired

A B C C D

1 4 NAME Enterprise’s name (designation) Nonempty text YES

2 ₅ _NIP _{Tax Identification Number} _YES

3 6 REGON REGON number of enterprise NO

7 EKD EKD Number (European

Classification of Activities) NO

4 8 REGISTRATIONNUMBER Number in a register _{Have to appear}

together NO 5 9 REGISTRYNAME Name of a register, in which the

above mentioned number is kept

(7)

2006/IT-P/??

1.2.1.2. Natural person

The required data of a natural person is: name and surname, natinality and PESEL number (for citizens of Poland) or series and number of ID card (for foreigners). Other, optional data is : address of residence (for permanent or temporary residence) and date of birth. Polish citizens may also give series and number of ID card or other document proving identity.

If a debtor is a natural person running business activity, we can give data of his enterprise, then however this data is not subject to statutory requirements of demadability of legal persons’ data.

Table 3 : Data of natural person

Number(s) of

column(s) Column’s name Description Notes Required

A B C D E

1 14 FIRSTNAME Name YES

2 ₁₅ _SECONDNAME _{Second name} _NO

3 16 SURNAME Surname YES

4 17 CITIZENSHIP Nationality YES

5 18-21 ADDRESS Address of residence (permanent or

temporary residence) Four lines YES

6 ₂₂ _BIRTHDAY _{Date of birth} _NO

7 23 PESEL PESEL number (required for Polish

citizens) One or

another 8 24-26 IDENTITYCARD Description of a document proving identity Type, series

and number

1.2.2. Data on obligation

Obligation is described with at least four elements: identifier, legal title of obligation and date of arrears’ occurrence. The Yonick does not allow for passing information on obligations in other currencies than PLN.

Additionally, we can give a sum of obligation (these sums shall be different , e.g. if the obligation has been partially repaid).

Data on active debts may also be supplemented with description of a stage of proceedings regarding the obligation (e.g. of ongoing court or enforcement proceedings ) and information on debtor’s questioning of a whole or a part of obligation. These descriptions are in a form of a loose text up to 1024 characters long.

While passing data on obligation to a bureau, the client has to provide a date of sending a request for payment to the debtor. This request for payment has to contain warning upon intention of passing the data to a business information bureau, together with name and address of this bureau.

The Bureau shall accept information on obligation only if the transferred data meets three conditions(apart from providing minimal data):

 an obligation is demandable since at east 60 days (that is 60 days have passed since the “PaymentDate”)

 At least one month has passed since a request for payment was sent. (that is since the “CallSent” date)

 A total sum of debtor’s obligations towards the client has exceeded 200zloty, if a debtor is a consumer, and 500zloty if a debtor is a business entity.

Naturally, it is necessary to meet also all the remaining requirements determined by the act, that are not specified in the data (e.g. possessing authorization for providing business information on consumers).

The obligation data transferred to the bureau are collected in the table 4.

Table 4 : Data on obligation

(8)

2006/IT-P/??

of

column name

A B C D E

1

27 ID Identifier of obligation. Any string

established by the client. An identifier has to be unique for all client’s

obligations. YES 2 28 TITLE Legal title of obligation, e.g. „Invoice

145/A/02” Nonempty element YES

3 29 DEBT Sum of obligation NO

4 ₃₀ _ARREARS _{Sum of arrears} _YES

5 31 CURRENCY Currency of obligation and arrears e.g. „PLN” YES 6 32 PAYMENTDATE Date of arrears’ occurrence yyyy-mm-dd format YES 7 33 OBJECTIONS Information on debtor’s questioning

of a whole or a part of obligation. Required if such exist NO 8 34 PROCEEDINGS Description of a stage of proceedings

regarding this obligation NO

9 35 CALLSENT Date of sending a request for

payment to the debtor yyyy-mm-dd format YES

ATTENTION! The field “OBJECTIONS” has to be, in accordance with requirements of the Act, filled etery time when a debtor questions active debts entered in the Register. Objections can be entered into the base together with obligations (operation of adding a case), and if debtor reports it at the time when the case is already put in KRD, then the data has to be modified.

Electivity of the field „OBJECTIONS” should be understood in technical context. Substantially, leaving the field “OBJECTIONS” empty indicates lack of objections, a completed field indicates entering of objections’ content into the base. All objections of a debtor have to be added to a case by using protocols or through the website service.

1.2.3. Suspension and notification data

The act allows to suspend publishing of business information for definite period of time. A suspended business information shall be unavailable for third parties within frames of search, whereas such information remains in the KRD database and shall be disclosed within frames of inquiring upon oneself. In order to suspend publishing, apart from chosing appropriate operation code, we have to provide an ending date of suspension in the field no 36. In order to resume publishing we have to chose appropriate operation code and leave the field no 36 empty. In both cases it is required to provide identifier of obligation (column 27), to which the operation refers.

The KRD systems allows for notifying debtor upon adding him to the KRD base. Such notification can be sent by regular or registered mail. It can also be sent to a forwarding address other than the one provided in a business information.

A type of a letter to be sent to a debtor is being determined by a code put in the field 37. If we do not want to send a notification, the field should remain empty. The “P” means a request for sending a regular mail, and the „R” code – a request for sending a registered mail. If the fields 38-41 are filled, then a letter shall be sent to debtor’s forwarding address therein. Otherwise, the letter shall be sent to debtor’s residence address or abode given within frames of a business information.

The listing of data is put in the table no 5. The formats of suspension date and forwarding address are analogous to other dates and addresses in the Yoncik protocol.

(9)

2006/IT-P/??

Table 5 : Suspension and notification data

Number

of column Column’s name Description Notes Required

A B C D E

1

36 SUSPENDCAS EOBLIGATION DATE

Ending date of business data suspension. Required when we order suspension of information’s publishing

yyyy-mm-dd format NO

2

37 NOTIFYDEBTO

R Optional order of notifying Debor via regular or registered mail. regular mail, r or R Entering P or p for

for registered mail NO 3 38-41 NOTIFYADDRE

SS Address, to which the notification shall be sent Four lines NO

1.2.4. Data of obligations with execution clause

For obligations with execution clause the Act requires to provide KRD with additional data. It is necessary when choosing ^E option at operation codes from the 1st column.

Table 6 : Data of obligations with execution clause

Number

of column kolumny Nazwa Opis Notes Required

A B C D E

1 42 SIGNATURE Files’ signature NO

2

43 EXECUTIVEOB LIGATIONDAT E

Data of issuing an execution title

yyyy-mm-dd format NO

3 44 DECIDINGAUT

(10)

2006/IT-P/??

2. Types of orders

2.1. Orders regarding business information

We may provide the business information bureau with orders of rendering services. These services regard managing business information: adding, modification, removing, suspension and unsuspension of business information’s publishing, orders of notifying debtor via regular or registered mail.

At present, the Yonick protocol serves the following types of orders:  adding business information

 updating business information  removing business information

 suspension of business information’s publishing  resume of business information’s publishing

While ordering add of a business information, we can also order a notification for debtor upon adding him to KRD base. Such notification shall be send with regular or registered mail.

There are no plans of serving other types of business information (except from information on unpaid obligations)

A type of order, that is a type of commissioned operation shall be put in the first column of CSV file for the Yonick protocol. The currently served codes are:

- ‘ADD’, denoting add of business information, - ‘UPD’, denoting update of business information,

- ‘RMV’, denoting case removal (removal of every business information with the same identifier from the column 2 -“INFORMATIONTYPE”)

- ‘SSP’ denoting suspension of business information - ‘USP’ denoting unsuspension of business information

In order to remove a single business information ascribed to a case consisting of more than one obligation, it should not be put in the update file during update (e.g. if one of three invoices of a given debtor has been paid, then we perform update of the remaining two). Similarly we can add any amount of business information to the existing case. (e.g. enter five obligations in the update file, where a case consists of three invoices).

ATTENTION!

When updating a case it is necessary to save in the file EVERY business information that builds this case. Omission of any obligation shall result in its removal from KRD.

During suspension and unsuspension of debts it is only required to provide an identifier of obligation.

We should also pay attention to the fact that identifiers of a case and obligation have to be unique in the frames of all cases and obligations of KRD’s client.

Moreover, we can order notification to be sent to a debtor, using the column: NotifyDebtor, where after entering symbols P or R an appropriate letter (regular or registered) is being ordered. The notification is being sent to debtor’s main address (in case of an entrepreneur DebtorSeatAddress; in case of a consumer DebtorAddress) unless a forwarding address is given in

(11)

2006/IT-P/??

2.2. Options of orders

In the code field of a commissioned operation we can also put options regarding this order. In order to separate options from operation code, use the character: ^ (spacing circumflex accent, code: ASCII $5E). A name of an option consists of one character.

An option served this way is a request for full confirmation of a performed operation through sending back the data included in KRD base before or after performance of a service. The code for this option is V (capital letter, code: ASCII $56)

More information on confirmations can be found in the subsection 2.3.

Options of orders are also applied during determination of a given debt as sent to KRD after sentence, with an execution clause. In such case we enter a letter E(capital letter, code: ASCII $45) after the character: ^. In this case the option may be added to a record sent to KRD (operation code: ADD) or updated in KRD (operation code: UPD)

2.3. Confirmations

In a standard output file we can only find the information upon order’s status that is wether the order was realized correctly or some error occurred.

On explicite request however, an output file can be filled with a set of data which the operation concerned. This is a state of data after add or update of obligation or before its removal.

Data downloaded from the system are presented in the same way as they have been addend, that is in a form of a line of the Yonick protocol with appropriate columns filled – they should be identical.

Generation of confirmations after performance of an operation is realized in a separate transaction. If an error occurs during download of confirmation data the performed operation is not being cancelled and the ordering party gets back a status of primary operation together with an empty confirmation.

(12)

2006/IT-P/??

3. Output file

In the version 2.0 of the protocol it has become possibile to generate an output report. It contains at least a status of performance of commissioned services, that is an information on successful performance of a given service or information upon error that occurred.

If we ordered full verification of a service, an output file shall additionally contain data connected with this service. It shall be presented in the same form in which they appeared in the order of service’s performance in the output file – in the same columns and in the same format.

An output file differs from the input one in its additional columns and modified demandability of columns.

The two columns added to the line’s end is a status code and error’s description. The operation status code may take „S” values if an operation was successful and „F” if it failed. If performance of order failed, a text message regarding error’s causes shall be displayed in the field of error’s description.

There will also be filled the first three columns of each line, that is type of order, type of business information on chich this order operated and case’s identifier.

The remaining columns containing data of a debtor, obligation, suspension and notifications shall be filled only if full confirmation of operation has been requested (option “V” of the order).

Table 7 : Output file’s columns

Number of column

Column’s name Description Notes Required

A B C D E

1

1 OPERATIONTYPE Type of commissioned operation

together with possibile options. Operation codes consist of three

letters YES

2 2 INFORMATIONTYP

E Type of business information Two-letter code YES

3 ₃ _CASEID _{Identifier of a case} _{Same as in the order} _YES

4 4-41 Data passed to the bureau in an

output file Filled, if confirmation has been requested NO

5 42 STATUSCODE Order’s status „S” or „F” YES

6 43 ERRORDESC Error’s description Loose text NO

Document’s template: Normal.dot v.1.0 2002-06-20; instruction: <NONE>