The main purpose of the import definition file is to set the field mapping between the source and the XML file to be created for Exact. These specific field mappings can be found on the XConnector start page for each import topic. The table below shows the general settings that can used in the import definition file. Setting Description Example ADM Company number used in functions and lookups. adm;"001" ANGELSAXIONYN Use angelsaxion notation for currency rates. ANGELSAXIONYN;"Y" BATCHINDICATOR A source file can be split into batches. This is useful when one single source file has records for multiple Exact companies. The XConnector makes separate XML files and imports them into the correct company. BATCHINDICATOR;4 DATEFORMAT Only used in CSV source files. Set the date format for all the date fields in the source, will be overruled by a field specific date format setting. DATEFORMAT;"dd-mm-yyyy" DECIMALSEPARATORXML Request freefield amounts should be used with comma. This is a bug in the synergy import module. For this this setting can be used. Default for XML is a period . Accounts;DECIMALSEPARATORXML;"," DEFAULTCURRENCY Sets the default currency used in the XML file. The default currency is also important in the Financial entry when using foreign currenries. DEFAULTCURRENCY;"EUR" DELIMITER Only used in CSV source files. Specify a delimiter. Semicolon ";" and comma "," are recognized automatically. Other delimiters should be set with this setting. A textqualifier can also be set. See further on this page. DELIMITER;"TAB" or DELIMITER;"|" FILES Set the number of import files. This used when the header and line records of a source or in separated files. FILES;"2" HEADERINDICATOR When the header of entry (order, invoice etc) is 'triggered' by a specific character (ie. "0") this setting can be used. Use "NONE" to indicate that all records in the source belong to the same entry. HEADERINDICATOR;"0" FREECOLUMN1..5FREEVAR1..5 Adds a vitual column to the source data. This column can filled with the result of a function (eg. fnif or fngetstring). The freecolumns are determined prior to processing the source data records, so its value can be used as company field (adm) or header indicator. Order;freecolumn1;fnleft({7},4) Order;HeaderIndicator; Order;HeaderIndicatorColumn;freecolumn1 Freecolumn 1 is filled first four characters of source data column 7. After this freecolumn1 is used as Headerindicator for the Sales orders.Order;Reference;fnGetString("select <field_X> from <tableX> where <fieldY> = '{Freevar1}'") The value of Freevar1 is used in the fnGetstring function. HEADERINDICATORCOLUMN Sets the column which is monitored to trigger a new header. This column has the header indicator (see Headerindicator setting) or a field that changes and triggers a header, ie. Debtor number, entry number, invoice number. HEADERINDICATORCOLUMN;"1" Column 1 in this example has the header indicator. POPUPSTRING Enables the manual entry of a string value during generation of the XML file. This is useful when a text needs to be flexible. Ie. The description of a financial entry. Entry;Description;popupstring("Geef de boekingsomschrijving") SHEETNAME Specify a specific sheet in the Excel source file. By default the first sheet in the Excel workbook is taken. SHEETNAME;"Sheet3" SKIPHEADER With this setting a record in the source file can be skipped. Most used with conditional function fnif or fngetstring. Please note that the result of the if-then-else formula is a "1" (for skipping a line) or "0" (for not skipping) between double quotation marks. skipheader;fnif({3}="x","1","0") when the value in column 3 is an "X" then the line should be skipped. SKIPLINE With this setting a record in the source file can be skipped. Most used with conditional function fnif or fngetstring. Please note that the result of the if-then-else formula is a "1" (for skipping a line) or "0" (for not skipping) between double quotation marks. skipline;fnif({3}="x","1","0") SKIPZEROAMOUNTLINES Obsolete, replace by SKIPZEROLINES SKIPZEROLINES Used to make the XConnector skiplines in the source file 0 - do not skip any line (default) 1 - skip when amount = 0 2 - skip when quantity = 0 3 - skip when amount = 0 OR quantity = 0 4 - skip when amount = 0 AND quantity = 0 SKIPZEROLINES;"2" this will skip records where the quantity is null, empty or zero. SORTFIELD Field in CSV, Excel or SQL source table/view that is used for the sorting of the records. SORTFIELD;"2" SOURCESQLSERVER Used when source data should be read from a different SQL server. SOURCESQLSERVER;"SQLSERVERNAME" SOURCETYPE Set the type of source data Possible values are: "XLS", "XLSX" - Excel "CSV" - Comma delimited "TAB" - Tab delimited "TXT", "FIX", "FIXED" - Fixed length "SQL" - SQL table of view "SP" - SQL stored procedure SOURCETYPE;"XLS" SP Used to defined the SQL stored procedure that delivers the source records to the XConnector. adm;"001" SOURCETYPE;"SP" SP;"sp_orders" SQLDATABASE The database having the source data (view, table or sp). SQLDATEBASE;"DATABASENAME" STARTLINE Indicate at which line in the source data the actual data starts. This is to prevent that header data such as column titles etc. is used in the XML file. Startline;"3" the actual data starts on the 3rd line in the source file. Line 1 and 2 are neglected. STREAMPACKETSIZE Split the XML result in multiple files. After a number of source records a new file is started. STREAMPACKETSIZE;"1000" TABLE Used to define the SQL source table. adm;"001" TABLE;"tb_orders" TARGETSQLSERVER Used when the target XML should be directed to a different SQL server. This is the SQL server where the target Exact company is located or the fngetstring function should be used at. TEXTQUALIFIER Sets the qualifier for textfields in the source file. Only used in CSV source files. TEXTQUALIFIER;""" Textindicator is set to a double quote. TIMESTAMPTARGETFILE Adds a date and time timestamp to the XML file name. ie. Account_20140225_1230.xml. TIMESTAMPTARGETFILE;"1" COMPANYTARGETFILE Adds a company nunber to the XML file name. ie. Account_001.xml. COMPANYTARGETFILE;"1"
The main purpose of the import definition file is to set the field mapping between the source and the XML file to be created for Exact. These specific field mappings can be found on the XConnector start page for each import topic. The table below shows the general settings that can used in the import definition file.
Order;freecolumn1;fnleft({7},4) Order;HeaderIndicator; Order;HeaderIndicatorColumn;freecolumn1
Freecolumn 1 is filled first four characters of source data column 7. After this freecolumn1 is used as Headerindicator for the Sales orders.
Order;Reference;fnGetString("select <field_X> from <tableX> where <fieldY> = '{Freevar1}'")
The value of Freevar1 is used in the fnGetstring function.
Column 1 in this example has the header indicator.
when the value in column 3 is an "X" then the line should be skipped.
Possible values are:
the actual data starts on the 3rd line in the source file. Line 1 and 2 are neglected.
Doc id: 00.005.493