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

  1. 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

  2. Billing code description – MANDATORY; String – e.g.: Primary code

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

  1. Billing code – MANDATORY; String – e.g.: 100, 200, 1.1.1

  2. Billing code description – MANDATORY; String – e.g.: Primary code

  3. Parent billing code (first-level billing code) – OPTIONAL; String – e.g.: 100, can be empty

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

images/download/attachments/160483647/billing-codes-import-prefix-format.png

Result in YSoft SafeQ management interface:

images/download/attachments/160483647/billing-codes-import-prefix-format-result.png

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:

images/download/attachments/160483647/billing-codes-remove-and-insert-result.png

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:

images/download/attachments/160483647/billing-codes-import-parent-format.png

Result in YSoft SafeQ management interface:

images/download/attachments/160483647/billing-codes-import-parent-format-result.png

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:

images/download/attachments/160483647/billing-codes-import-special-characters-utf-8-excel.jpg

Result in YSoft SafeQ management interface:

images/download/attachments/160483647/billing-codes-import-special-characters-utf-8-result.PNG

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 '\\'