Billing Code Import CSV Format Specifications
YSoft SafeQ management interface supports the batch importing of billing codes from a CSV file. The file must fulfill the format requirements described on this page.
It is recommended to use a maximum of 1,000 billing codes per one level (without technical limitations on the number of nested levels). If you have more billing codes, use (or request) an import script that can import data into the logical tree structure.
CSV Format
Billing codes are stored in a comma-separated values file.
Delimiter: semicolon ;
Quote character (if needed): double quote "
Importer configuration
You can modify the behavior of the importer by configuring the first row.
Format selection
Several formats are supported for importing. The format MUST be specified in the first line in the first column.
Available formats: prefix, parent
Format specification string: format:parent
Level delimiter
When you use the prefix format, you can change the default delimiter from '.' to another single character.
Delimiter specification string: levelDelimiter:/
The default level delimiter is '.'
The default prefix importer will read 1.2.3.. When you change levelDelimiter to / then it will read data in the format 1/2/3.
Supported Formats
Format prefix
Billing code in the tree format – MANDATORY; String – e.g.: 1.2.14 – the parent is in this case 1.2 and the billing code for this item is 14
Billing code description – MANDATORY; String – e.g.: Primary code
Extension string. From the third position, you can specify extension strings. Each column contains one and only one extension. Extensions are applied from the first left record to the right.
Format parent
Record contains the following columns:
Billing code – MANDATORY; String – e.g.: 100, 200, 1.1.1
Billing code description – MANDATORY; String – e.g.: Primary code
Parent billing code (first-level billing code) – OPTIONAL; String – e.g.: 100, can be empty
Extension string. From the fourth position, you can specify extension strings. Each column contains ONE and only one extension. Extensions are applied from the first left record to the right.
Parent billing code (first-level billing code) is optional. When it is not specified, the billing code is considered to be directly under the root element.
The uniqueness of a billing code is defined by its path. The same billing codes can appear under different parents.
Extension string
Format: extension_name:value
Allowed extensions: user, center, role, action
The extension string is case insensitive.
Extension user
This extension contains a user login name. The billing code with its entire subtree will be assigned to the specified user.
If a user account with the specified name does not exist, the system instead tries to find a role with the name.
Example: user:georgik
Extension center
This extension contains a cost center number. The billing code with its entire subtree will be assigned to the specified cost center.
Example: center:118999881
Extension role
This extension contains a role name. The billing code with its entire subtree will be assigned to the specified role.
Example: role:everyone
Extension action
Available actions:
remove – This deletes the billing code and its entire subtree
resetACL – This deletes the user, cost center and role assignments (i.e., the Access Control List) of the billing code
Example: action:remove
Removing assignments
To remove a billing code assignment from a specific user, cost center or role, prefix the username, cost center number or role name with a minus sign.
Example: user:-georgik;center:-1234;role:-everyone;user:newuser
Alternatively, you can remove all existing assignments and add new ones:
Example: action:resetACL;user:newuser
Note that in the unusual case when a username, cost center number or role name starts with a minus sign or a plus sign and you need to assign a billing code to it, you need to prefix it with a plus sign.
An example of adding assignments to user "-minususer", cost center "-1" and role "-minusrole": user:+-minususer;center:+-1;role:+-minusrole
An example of removing assignments from user "-minususer", cost center "-1" and role "-minusrole": user:--minususer;center:--1;role:--minusrole
Guidelines for Working with a Large Number of Billing Codes
It is possible to handle a large volume of billing codes, but you should follow certain rules.
Some useful guidelines:
1. If you need to assign a large number of billing codes, group them under one parent and only assign this parent.
2. If you need to assign the same set of billing codes to a large number of users, create a role and assign billing codes to the role according to guideline 1.
3. Make as few deletions as possible. Billing codes are still in the database even when they are deleted.
3.1. If you already deleted a large number of billing codes from the database, ask a database expert to clean up and VACUUM the database. Billing codes are necessary for statistics; incorrect deletions can harm statistics.
4. Do not create more than 1,000 children at the first level of a billing code tree. Divide billing codes into groups.
Sample CSV Data
Format prefix
Contents of the CSV file:
format:prefix;
1;Czech republic;user:barbora;user:richard;
1.1;Brno;
1.2;Lomna;
1.2.1;Dolni Lomna;
1.2.3;Horni Lomna;
1.3;Milikov;
2;Slovakia;center:118999881;
2.1;Kosice;
2.2;Povazska Bystrica;
2.2.1;Vrtizer;
2.2.2;Milochov;
1.9;Trencin;
2.2.3;Marikova;
Sample in Excel:
Result in YSoft SafeQ management interface:
Format prefix with level delimiter /
Contents of the CSV file:
format:prefix;levelDelimiter:/;
1;Czech republic;user:barbora;user:richard
1/1;Brno;
1/2;Lomna;
1/2/1;Dolni Lomna;
1/2/3;Horni Lomna;
1/3;Milikov;
Remove and insert new
Contents of the CSV file:
format:prefix
1;Large forest;;action:remove
1;Desert;;
1.1;Sahara;;user:georgik;118999881;user:arnost
1.1.1;Sand;
1.1.2;Dust;
Result in YSoft SafeQ management interface:
Format parent
Contents of the CSV file:
format:parent
100;Large forest;;center:118999881
10;Giant Sequoia;100;user:mary;user:james
11;Coast Redwood;100
12;Western Redcedar;100
13;Australian Oak;100
14;Inheritance;100;center:118999881
200;Old forest;
8;Bristlecone Pine;200
9;Alerce;200
10;Giant Sequoia;200
11;Sugi;200
12;Huon-pine;200
Sample in Excel:
Result in YSoft SafeQ management interface:
Special characters
You can use LibreOffice Calc to generate a proper CSV file in UTF-8 from an Excel table. Use a semicolon as the field delimiter and no character as the text delimiter.
Contents of the CSV file:
format:prefix;levelDelimiter:*
1;Tiskárna
1*1;Принтер
1*1*1;プリンター
2;打印机
2*1;პრინტერი
2*2;tölvufræði
Sample in Excel:
Result in YSoft SafeQ management interface:
Recommendation
max 1,000 sub-levels per one first-level
max 100,000 lines per CSV file
Limitation
max 1,000 sub-levels per one first-level for a one import procedure
max 3 MB CSV file size
the following characters are restricted: ?&’”<>
if you need to enter the character backslash '\' you must escape it, i.e. type '\\'