How to: Enable Import/Export of Bank or Payroll Files Using the Data Exchange Framework

The formats of files for exchange of bank or payroll data with ERP systems vary depending on the supplier of the data file and on the country/region. The generic version of Microsoft Dynamics NAV does not support local bank or payroll file formats out-of-the-box. A data exchange framework is therefore provided to make it easy to enable users to import or export bank and payroll files. For more information, see About the Data Exchange Framework.

Tip

As preparation for creating a data exchange setup for a SEPA bank file, you can use the related SEPA XML schema to define which data elements to include in the setup. For more information, see How to: Use SEPA XML Schemas to Prepare Data Exchange.

Note

If the bank or payroll file is in XML format, the term column in this topic should be interpreted as an XML element that contains data.

This topic includes the following procedures:

• To create a data exchange setup

• To export a data exchange setup as an xml file for use by others

• To use an xml file for an existing data exchange setup

• To enable a data exchange setup

To create a data exchange setup

Creating a data exchange setup involves two tasks:

1. In the Posting Exchange Definition window, describe the formatting of lines and columns in the file.

2. In the Posting Exchange Mapping window, map columns in the data file to fields in Microsoft Dynamics NAV.

   This is described in the following procedures.

To describe the formatting of lines and columns in the file

1. In the Search box, enter Posting Exchange Definitions, and then choose the related link.

2. On the Home tab, in the New group, choose New.

3. On the General FastTab, describe the data exchange setup and the data file type by filling the fields as described in the following table.

   Field	Description
   Code	Enter a code to identify the data exchange setup.
   Name	Enter a name for the data exchange setup.
   File Type	Specify what type of file that the data exchange setup is used for. You can select between three file types: XML: Layered strings of content and markup surrounded by tags indicating function. Variable Text: Records have variable length and are separated by a character, such as comma or semi-colon. Also known as delimited file. Fixed Text: Records have the same length, using pad characters, and each record is on a separate line. Also known as fixed-width file.
   Type	Specify what type of exchange the data exchange setup is used for. You can select between three types: Bank Statement Import Payment Export Payroll Import
   Reading/Writing XMLport	Specify the XMLport through which an imported data file or service enters prior to mapping and through which exported data exits when it is written to a data file or service after mapping.
   Ext. Data Handling Codeunit	Specify the codeunit that transfers external data in and out of the Data Exchange Framework.
   Reading/Writing Codeunit	Specify the codeunit that processes imported data prior to mapping and exported data after mapping.
   File Encoding	Specify the encoding of the file. Note This field is only relevant for import.
   Column Separator	Specify how columns in the data file are separated, if the file is of type Variable Text.
   Header Lines	Specify how many header lines exist in the file. This makes sure that the header data is not imported. Note This field is only relevant for import.
   Header Tag	If a header line exists in several positions in the file, enter the text of the first column on the header line. This makes sure that the header data is not imported. Note This field is only relevant for import.
   Footer Tag	If a footer line exists in several positions in the file, enter the text of the first column on the footer line. This makes sure that the footer data is not imported. Note This field is only relevant for import.

4. On the Posting Line Definitions FastTab, describe the formatting of lines in the data file by filling the fields as described in the following table.

   Note

   For import, you only create one line for the single format of the bank statement file that you want to import. For export, you can create a line for each payment type that you want to export. In that case, the Posting Column Definitions FastTab shows different columns for each payment type.

   Field	Description
   Code	Enter a code to identify the line in the file.
   Name	Enter a name that describes the line in the file.
   Column Count	Specify how many columns the line in the bank statement file has. Note This field is only relevant for import.
   Data Line Tag	Specify the position in the related XML schema of the element that represents the main entry of the data file. Note This field is only relevant for import.
   Namespace	Specify the namespace that is expected in the file, to enable namespace validation. You can leave this field blank if you do not want to enable namespace validation.

5. Repeat step 4 to create a line for every payment type that you want to export.

6. On the Posting Column Definitions FastTab, choose Insert Default Definitions to prefill the lines with values for columns that are required by minimum to import a bank statement file.

   Note

   You cannot use the Insert Default Definitions function for a data exchange setup of type Export.

7. Describe the formatting of columns in the data file by filling or editing the fields as described in the following table.

   Field	Description
   Column No.	Specify the number that reflects the column’s position on the line in the file. For XML files, specify the number that reflects the type of element in the file that contains the data.
   Name	Specify the name of the column. For XML files, specify the markup that marks the data to be exchanged.
   Data Type	Specify if the data to be exchanged is of type Text, Date, or Decimal.
   Data Format	Specify the format of the data, if any. For example, MM-dd-yyyy if the data type is Date. Note For export, specify the data format according to Microsoft Dynamics NAV. For more information, see Identifiers, Data Types, and Data Formats. For import, specify the data format according to .Net. For more information, see Standard Date and Time Format Strings.
   Data Formatting Culture	Specify the culture of the data format, if any. For example, en-US if the data type is Decimal to make sure that comma is used as the .000 separator, according to the US format. For more information, see Standard Date and Time Format Strings. Note This field is only relevant for import.
   Length	Specify the length of the fixed-width line that holds the column if the data file is of type Fixed Text.
   Description	Enter a description of the column, for information.
   Path	Specify the position of the element in the related XML schema.
   Negative-Sign Identifier	Enter the value that is used in the data file to identify negative amounts, in data files that cannot contain negative signs. This identifier is then used to reverse the identified amounts to negative signs during import. Note This field is only relevant for import.
   Constant	Specify any data that you want to export in this column, such as extra information about the payment type. Note This field is only relevant for export.

8. Repeat step 7 for every column or xml element in the data file that has data that you want to exchange with Microsoft Dynamics NAV.

The next step in creating a data exchange setup is to decide which columns or xml elements in the data file map to which fields in Microsoft Dynamics NAV.

The specific mapping depends on the business purpose of the data file to be exchanged and on local variations. Even the SEPA bank standard has local variations. Microsoft Dynamics NAV supports import of SEPA CAMT bank statement files out-of-the-box. This is represented by the SEPA CAMT data exchange setup record code in the Post Exch Def List window. For information about the specific field mapping of this SEPA CAMT support, see Field Mapping When Importing SEPA CAMT Files.

To map columns in the data file to fields in Microsoft Dynamics NAV

1. On the Posting Line Definitions FastTab, select the line for which you want to map columns to fields, and then choose Field Mapping. The Posting Exchange Mapping window opens.

2. On the General FastTab, specify the mapping setup by filling the fields as described in the following table.

   Field	Description
   Table ID	Specify the table that holds the fields to or from which data is exchanged according to the mapping.
   Name	Enter a name for the mapping setup.
   Pre-Mapping Codeunit	Specify the codeunit that prepares the mapping between fields in Microsoft Dynamics NAV and external data.
   Mapping Codeunit	Specify the codeunit that is used to map the specified columns or XML data elements to fields in Microsoft Dynamics NAV.
   Post-Mapping Codeunit	Specify the codeunit that completes the mapping between fields in Microsoft Dynamics NAV and external data. Note When using the Bank Data Conversion Service feature, the codeunit converts exported data from Microsoft Dynamics NAV to a generic format that is ready for export. For import, the codeunit converts external data to a format that is ready for import into Microsoft Dynamics NAV.

3. On the Field Mapping FastTab, specify which columns map to which fields in Microsoft Dynamics NAV by filling the fields as described in the following table.

   Field	Description
   Column No.	Specify which column in the data file that you want to define a map for. You can only select columns that are represented by lines on the Posting Column Definitions FastTab in the Posting Exchange Definition window.
   Field ID	Specify which field the column in the Column No. field maps to. You can only select from fields that exist in the table that you specified in the Table ID field on the General FastTab.
   Optional	Specify that the map will be skipped if the field is empty. Note If you do not select this check box, an export error will occur if the field is empty. Note This field is only relevant for export.

When you have created the data exchange setup for a specific data file, you can export the data exchange setup as an xml file that can be used to quickly enable import of the data file in question.This is described in the following procedure.

To export a data exchange setup as an xml file for use by others

1. In the Search box, enter Posting Exchange Definitions, and then choose the related link.

2. Select the data exchange setup that you want to export.

3. On the Home tab, in the Import/Export group, choose Export Data Exchange Setup.

4. Save the xml file that represents the data exchange setup in an appropriate location.

If a data exchange setup has already been created, you just have to import the xml file into the Data Exchange Framework. This is described in the following procedure.

To use an xml file for an existing data exchange setup

1. Save the xml file that represents the data exchange setup for a specific bank or payroll file in an appropriate location.

2. In the Search box, enter Posting Exchange Definitions, and then choose the related link.

3. On the Home tab, in the New group, choose New. The Posting Exchange Definition window opens.

4. On the Home tab, in the Import/Export group, choose Import Data Exchange Setup.

5. Select the file that you saved in step 1.

The data exchange setup is now ready to be enabled so that users can begin to import/export the file.

To enable a data exchange setup

For import of a payroll file, the data exchange setup is enabled with no further steps. For import of a bank file, the data exchange setup is now available to be selected in the Bank Export/Import Setup window to enable the setup. For export of a bank payment file, you must connect the setup code that you specified for one or more payment types to the related payment method. This is described in the following procedures.

To select the data exchange setup from the relevant bank account

1. Open the Bank Account Card window of the bank that you want to import bank statement files to.

2. In the Bank Statement Import Format field or the Payment Export Format field, choose the lookup button, and then choose Advanced.

3. In the Bank Export/Import Setup window, create a new line and fill the fields as described in the following table.

   Field	Description
   Posting Exch. Def. Code	Select the code that represents the data xml file for a data exchange setup that you have imported. For more information, see the “To use an xml file for an existing data exchange setup” section.
   Code	Specify a code to identify for the setup. This is the code that users will select in the Bank Statement Import Format field in the Bank Account Card window.
   Name	Specify a name for the setup.
   Direction	Select Export or Import, to specify if this setup will be used to import a bank file or to export a bank file.
   Processing Codeunit ID	Select the codeunit that will import the bank statement data.
   Processing XMLport ID	Select the XMLport through which the bank statement data is imported.

To connect the setup for one or more payment types with the relevant payment method(s)

1. In the Search box, enter Payment Methods, and then choose the related link.

2. In the Payment Methods window, select the payment method that is used to export payments from, and then choose the Pmt. Export Line Definition field.

3. In the Pmt. Export Line Definitions window, select the code that you specified in the Code field on the Posting Line Definitions FastTab in step 4 in the “To describe the formatting of lines and columns in the file” section.

All steps required to enable import/export of a bank or payroll file are now completed.

Users can start to import or export an enabled bank file by selecting the related setup code in the Bank Statement Import Format or Payment Export Format fields. For more information, see How to: Import Bank Statements or How to: Export Payments to a Bank File

Users can start to import an enabled payroll file by selecting the related setup code in the Payroll Trans. Import Format field. For more information, see How to: Import Payroll Transactions.

See Also

Tasks

How to: Use SEPA XML Schemas to Prepare Data Exchange
How to: Set Up Bank Data Conversion Service

Concepts

About the Data Exchange Framework
Field Mapping When Importing SEPA CAMT Files

Other Resources

Posting Exchange Definition
Posting Exchange Mapping
How to: Import Bank Statements
Make Payments with Bank Data Conversion Service or SEPA Credit Transfer
Bank Statement Import Format
How to: Import Payroll Transactions
Process Payments Automatically