Page tree
Skip to end of metadata
Go to start of metadata

1. Description

In Hansaworld SERP data is stored in registers. Every register has its own independent set of data. (E.g., Items are stored in item register INVc, Item Classes are stored in a separate item class register DIVc etc.). Connection between different data is made within Hansaworld software. 

In this page you will find a description how to create Hansaworld import files using Excel spreadsheets and attached templates for more frequently used register imports. 

This is what an import file template looks like:

Template consists of three parts: Header, Column descriptions, Import data. The number of columns and rows for data import is unlimited and for some registers there may be even more than 100 columns, though you don't need to fill all of them, because some of them will be filled automtically after import or they may be used only for internal data processing.

2. Header

Header(1.) is  8x6 cells located in columns from A to H (icluding) and first 6 rows. It is important not to change information in the header unless you need to customise import file. Entries in the header cells indicate how the import will be done in the Hansaworld system. Name of the register (2.) indicates in which register the data will be imported. Cell D2 indicates if the imported data will replace the existing data or will be added to the existing data.


 

Starting from version 7.2. header is placed in first 9 rows (8x9 cells) and it contains also the number of the version.

3. Column descriptions


In the next rows after Header Column descriptions ar placed to make work with data more convenient. The number of rows in the templates will be 2 to 5. They will contain (in case of 5 rows):

  • Column No.;
  • Description in English;
  • Description in Latvian;
  • Column name in database (in register);
  • Type of the entry (Text, number).

These 5 (or four, three, two) colored rows HAVE TO BE DELETED when creating import file.

4. Import data

Next part of the document (Picture - arrow 1; the red frame) is meant for entering data. Number of rows is unlimited, but you may need to divide some very large files. 

Every row stands for one entry (arrow 2.). Respectively you can add existing data (if you have one) for the particular entry in every column. E.g., when importing activities in the Activities register, every row will show one activity with its data, as Start time, Planned time, Persons, Supervisors, etc. More important entries usually will be in the first columns of the file (e.g., number, name, date, etc.)

In cases when there is a matrix available for the register, it is required to fill the next row with matrix entries or empty spaces (if there are no matrix entries) after every entry (row). If there are matrix entries, the empty row must be placed after matrix entry row. (arrow 3.)

4.1. List of registers

In the table below you can see most frequently used registers and their descriptions as well as existence (or non-existence) of the matrix field (when an empty row must be used between the entries).

List of registers  
NameCodeMatrix
ActivitiesActVcYes
Activity classesActTypeGrVcNo
Activity typesActTypeVcNo
ItemsINVc*
Item groupsITVc1Yes
Item classificationsDIVcNo
Reporting periodsPeriodBlockNo
BanksBankVcNo
DepartamentsDepVcNo
TransactionsTRVcYes
Purchase InvoicesViVcYes
Classification TypesCTypeVcNo
Customer CategoriesCCatVcNo
ContactsCUVc**
Contact classificationsCClassVcNo
Expense accountsCLCorspVcNo
 
NameCodeMatrix
UnitsUnitVcYes
Depreciation ModelsDprModVc1Yes
ObjectsObjVcNo
Object typesOtVcNo
AssetsAT2UnitVcNo
PersonsUserVcYes
Supplier CategoriesVgVcNo
Freight CompaniesFreightCompanyVcNo
Asset categoriesAT2VcYes
Assets N/L ClassesAC2BlockNo
InvoicesIVVcYes
Payment ModesPMBlockNo
Goods ReceiptsPUVcYes

Standard problems

StandProblemVcNo
PricesPLVcNo

* Two empty rows are needed between entries if matrix has comments field. There should be an extra empty row between comments field and matrix.

** Register has no matrix row, but it has comments field and an extra empty row between entries is needed..

6. Saving files in text format / File import

Files must be saved in MS Excel through Save as -> "Text (Tab delimited).txt" in order to recognize them in Hansaworld system.

Hansaworld sistēmā imports notiek no Tab delimited teksta failiem jeb teksta failiem, kuru šūnas ierakstus teksta failā atdala Tab veidotas atstarpes.

7. Suggestions and useful information

7.1. Adding empty rows in Excel

There may be cases when data is not saved with empty rows that are necessary for data import, if there is a matrix intended in the register. You can generate empty rows automatically in Excel.:

  • Enter the data that you need to import in Hansaworld in the import file template;
  • In the empty column (if there is no empty column in the template, you should create one) add numeration to the rows;
  • Copy and paste this numeration (from 1 to the last number) at the bottom of the same column;
  • After that select the column and use Excel function Data → Sort.
  • After sorting the rows you will get an empty row after each entry. When this is done, you can delete the column with numeration..

7.2. Suggestions when preparing files

In fields that are not meant for text input use of diacritical marks should be avoided. For example in item code fields.

Before import check if the required fields in the registers are not empty.

Code fields are usually made of both - numbers and letters. The preferable length of codes is up to 8 symbols, but this length can differ for every register and every field.

In the Name fields longer text can be used.

UUID, ID and other fields are for numbers only.

There is no need to fill all the fields. It is important to fill code, name and other required fields. Other fields may be more convenient to fill manually in the system entries after import.

7.3. Limitations

 " " (space) is not allowed in code fields.

7.3. Field length and format

Visiem laukiem ir stingri noteikts kāda informācija var būt tajos ierakstīta. 

Row "field type" in import file templates indicates the type and length of entries in the specific fields. Usually it is several capital letters for code fields have short capital letters, numbers and letters for ID fields. Text fields usually have no limitations regarding the input symbols.

Here are the most commonly used field types:

TypeEntry typeLength limitation (by default)Comments
M4CodeCapital lettersYes (Mentioned in import file) 
M4StrText, numbersYes (Mentioned in import file) 
M4UstrText, numbersYes (Mentioned in import file) 
M4IntNumbersNoNo decimals
M4LongNumbersNoNo decimals
M4ValNumbersNoWith decimals
M4UValNumbersNoWith 3 decimals
M4DateDateNo 
M4TimeTimeNo 
M4UUID  Always empty if database is not updated
M4PhoneNoNumbersYes 
M4Mark0 or 1No

Indicates if the field is selected

Length limitations are indicated in the import file cells after field type name (E.g. "M4Code,20" or "M4Str,100" - will mean respectively code field with max length 20 symbols or text field with max length 100 symbols). If there is a 0 after comma, then if there is no other indications, you can set values with no limitations as long as they are allowed for the field type.

8. File templates

 

  • No labels