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.
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|
* 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.
" " (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:
|Type||Entry type||Length limitation (by default)||Comments|
|M4Code||Capital letters||Yes (Mentioned in import file)|
|M4Str||Text, numbers||Yes (Mentioned in import file)|
|M4Ustr||Text, numbers||Yes (Mentioned in import file)|
|M4UVal||Numbers||No||With 3 decimals|
|M4UUID||Always empty if database is not updated|
|M4Mark||0 or 1||No|
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
- Contacts / customers / suppliers
- Fixed assets
- Sales invoices
- Purchase invoices
- Webshop products
- Webshop product categories
- Item groups
- Item classifications
- Classification types
- Customer categories
- Contact classifications
- Objects / tags
- Object types
- Sales orders
- Purchase orders
- Item status support register