Connectivity Manual C/S - Data import introduction
Data import introduction
This chapter describes the way files can be imported in Exact Financials C/S.
The import file is a CSV (comma separated values) file, which means that it is a text (ASCII) file, in which each line represents one record. If a field is left empty, a comma should still separate it. So, a CSV file with two empty character fields will look like this:
"","" - for two character fields
or
, - for two integer fields.
The import file can be kept to a minimum because the import program will assume the default values if no field value is found in the import file.
Table description
Example of a table:
| 1
| line-nr
| INT
| N3
| B
| -
| Line number
| Mandatory
| -
| A
|
| 2
| EXTRA-journal-type
| CHAR
| A1
| B
| -
| Type of journal
| -
| -
| A
|
| 3
| journal-nr
| INT
| N3
| B
| journl
| Journal number
| -
| -
| A
|
| 4
| period
| INT
| N3
| H
| -
| Period
| -
| -
| A
|
| 5
| fin-yr
| INT
| N4
| B
| -
| Financial year
| -
| *
| A
|
| 6
| entry-nr
| INT
| N8
| B
| -
| Entry number
| -
| -
| A |
Nr
The sequence number of the field.
Field name
The field name column contains the name of the field that is expected.
Field type
The field type specifies of which data type is field is. The following data types can be used:
| Character
| CHAR
| A character field consists of a number of alphanumeric characters that are enclosed within quotes (the " character).
|
| Integer
| INT
| An integer field contains a number without decimals.
|
| Date
| DATE
| A date field contains a date in the DD/MM/YYYY or DDMMYYYY.
|
| Decimal
| DEC
| A decimal contains a number with the option to have decimals (The decimal separator is always the dot: ".")
|
| Logical
| LOG
| A logical always contains a Yes or No (Y/N, J/N,
True/False). |
Format
The format of the field.
H/S/B
Belongs the field in the header line (H), sub line (S) or to both (B).
Reference
The given field must exist in the table stated in this column.
Description
A short description of the field
Default
When a field is marked with a *, then the default value is described below.
Remarks
This column contains general remarks about a field. This column also contains the possible values that can be entered in the field.
Journal
The journal types by witch the field is used. This column is only used for financial entries.
Standard import guidelines
- If a mandatory field is not filled correctly, the
whole record will be rejected.
- If an optional field value is supplied, this
value will be checked and depending on the type of error (fatal or warning)
the whole record will be rejected (fatal) or the field will be ignored
(warning) during import.
- If an optional field value is left empty, the import
program will try to get a default value from the database. Because it is not
possible to guarantee that the default values are valid in every case
imaginable it will still be possible that the whole record is rejected.
- In both cases the error file created will give a clear
indication why the record was rejected.
- ,?,= Get the default value for this field (If
possible)
- ,"", OR ,, OR ,0,= if empty is not a valid value, then
default will be used.
- ,Other values,= Check this value, if not valid return the whole invoice in
the error file.
Import settings
In [File, Company settings, Financial preferences] in the Finance menu, you can set the default path of where to find the CSV files. This only applies to Exact Finance C/S import.
The 'Exchange rate type' setting is only available after running the euro wizard. This setting enables the user to import files with different rate formats regardless of the rate format used in Exact Financials C/S.
Here you can set the exchange rate type of the import files.
- The 'Old exchange rate format' will handle the import
file rate as a calculation rate (1 DEM = 1,12 NLG).
- The 'Euro exchange rate format' (Anglo-Saxon rate format) will handle the
import file rate as a euro rate (1 EUR = 1,96 DM)
This setting can be overruled by filling the 'Rate format' field in the import file.
This field is optional and can be filled with 0 for Old and 1 for Euro format. If this field is not filled the default will be assumed.
Example
CSV Import file
Given the (non existent) table 'table1' with the following fields:
Field1 (character)
Field2 (integer)
Field3
(decimal)
Field4 (logical)
Field5 (date)
A CSV import file for this table would look like this:
"abcdef",10,2.5,yes,14012000
"ghijkl",20,2.0,no,14012000
Importing the file
The file to be imported is always a file with the extension .csv. After importing the csv-file is renamed to .001 file (or 002,003,004 etc.if a file with such an extension already exists). In case a file contains errors a *.err file is created. In this file, you will find a list of all errors that occurred. If a record contains fatal errors, the record will be rejected. In this case a *.rej file will be created. This rej-file will show the rejected records. The records that are not stated in this file, have been imported correctly.
Connectivity Manual C/S - Contents
> Data import > Introduction
| Main Category: |
Support Product Know How |
Document Type: |
Support - On-line help |
| Category: |
On-line help files |
Security level: |
All - 0 |
| Sub category: |
Details |
Document ID: |
01.642.687 |
| Assortment: |
Exact Financials (v3)
|
Date: |
23-04-2005 |
| Release: |
|
Attachment: |
|
| Disclaimer |