In Microsoft Dynamics NAV, dataports can be set to export data to an external file with a fixed format or a variable format. This topic describes how to create a dataport for exporting data to an external file.

To create a dataport

  1. Click Tools, and then click Object Designer.

  2. In Object Designer, click Dataport, and then click New.

  3. Click an empty line in Dataport Designer to select the dataport itself, click View, and then click Properties.

    This opens the Properties window of the dataport. Alternatively, click Edit, click Select Object, and then open the Properties window.

  4. In the Properties window for the new dataport, set the following properties.

    Property Value

    Import

    No

    FileFormat

    Fixed

  5. In Dataport Designer, in the first DataItem field, click the AssistButton, and then select the table that you want to add to the dataport. For example, to export data from the G/L Account table, select table 15.

    The name is set by default to the name of the table. Optionally, you can change this.

You have created a dataport with a single data item and must now specify which fields from the underlying table will be used in the dataport.

To add fields to a data item

  1. In Dataport Designer, select the data item that you want to add fields to.

  2. Click View, and then click Dataport Fields.

  3. In the Field Designer window, in the SourceExpr field, add the first field to the data item.

    This can be a field in the table that the data item corresponds to, or it can be a source expression that maps to a field in another table. For example, if the field is the No. field on the current table, the value of the SourceExpr field is "No.". If the field is on another table, such as the Description field in the Tariff Number table, the value of the SourceExpr field can be "Tariff Number".Description, where Tariff Number is a global variable for the Tariff Number table.

    Alternatively, you can use the field menu to add multiple fields from the current table.

    1. When the Field Designer window is open, click View, and then click Field Menu.

    2. In the Field Menu window, select the fields that you want to export.

      For example, from the field menu for the G/L Account data item, select the No., Name, Balance at Date, and Net Change fields.

    3. Click the Field Designer window, and then, in the dialog box that appears, click Yes to add the fields to the data item.

  4. In Field Designer, set the appropriate values for the StartPos and Width properties. For more information, see Properties for a Field on a Dataport Data Item.

    For fields on the current table that are not FlowFields, the StartPos and Width properties are automatically set for the fields.

  5. If the data item contains calculated fields, you must set the data item to calculate these values.

    1. In Dataport Designer, select the data item, click View, and then click Properties.

    2. In the Properties window for the data item, in the CalcFields property, click the AssistButton to open the Field List window.

    3. In the Field List window, select the fields from the data item that must be calculated when they are exported, that is, the FlowFields, and then click OK.

      For example, for fields in the G/L Account data item, select the Balance at Date and Net Change fields.

When you have specified the fields, you can finalize and test the dataport.

To finalize and run the dataport

  1. Optionally, for each data item, set values for key, sort order, and any table filters to remove the data item from the request form. This is required if the dataport runs without a user interface. For more information, see How to: Change the Request Form for a Dataport.

  2. Optionally, change the format of the external file. For example, you can change the formatting of field values. For more information, see How to: Change the File Format for a Dataport.

  3. Optionally, save the dataport before running it. For more information, see How to: Save a Dataport.

  4. Click File, and then click Run to run the dataport.

    When you run the dataport, the request form appears.

  5. In the request form, on the Options tab, in the File Name field, enter the location and the name of the file that the data will be exported to.

  6. Optionally, on the tab for the data item, set filters to specify which data to export.

  7. To run the dataport, click OK.

When the dataport run is finished, you can open the file in a text editor. For an example of the result of running a dataport, see the Example section.

Exporting with Variable Format

The preceding sections describe how to set a dataport to export data to an external file in a fixed format. However, you can also export data with variable format.

To export data with variable format

  1. Create a dataport as described in the first procedure. However, in the Properties window for the new dataport, set the following properties.

    Property Value

    Import

    No

    FileFormat

    Variable

    FieldSeparator

    ;

    FieldStartDelimiter

    <None>

    FieldEndDelimiter

    <None>

    FieldSeparator is set to use semicolon because the fields include both space characters and commas. Another solution would be to use the delimiters. In that case, FieldSeparator could also be a comma, but what you choose should really depend upon the target application for the exported file and the formats that the application supports when it imports text files.

  2. Optionally, change the default values of the RecordSeparator and the DataItemSeparator properties. If you use the default values, records will be separated by new lines, and data items by two new lines. This can be useful if your dataport includes several data items.

  3. Add the fields from the underlying table as described in the second procedure.

  4. When you have completed defining the dataport, save and run it. The result is an external file where the values of each field are separated by semicolons. The following example illustrates the output.

      CopyCode imageCopy Code
    1000;BALANCE SHEET;0;0
    1002;ASSETS;0;0
    1003;Fixed Assets;0;0
    1005;Tangible Fixed Assets;0;0
    1100;Land and Buildings;0;0
    1110;Land and Buildings;1.479.480,6;1.479.480,6
    1120;Increases during the Year;147,73;147,73
    1130;Decreases during the Year;0;0
    

Example

The following example illustrates the data that is exported if you run a dataport with a fixed file format, which contains a single data item for the G/L Account table.

  CopyCode imageCopy Code
1000				 BALANCE SHEET				0			0		 
1002				 ASSETS						 0			0		 
1003				 Fixed Assets				 0			0		 
1005				 Tangible Fixed Assets		0			0		 
1100				 Land and Buildings			 0			0		 
1110				 Land and Buildings			 1.479.480,6  1479	
1120				 Increases during the Year	147,73	 0		 
1130				 Decreases during the Year	0			0		 
1140				 Accum. Depreciation, Buildings -526.620,38  -527	
1190				 Land and Buildings, Total	953.007,95   953		 
1200				 Operating Equipment			0			0		 
1210				 Operating Equipment			582.872,18   583		 
1220				 Increases during the Year	25.116	 25	
1230				 Decreases during the Year	0			0		 
1240				 Accum. Depr., Oper. Equip.	 -508.176,74  -508	
1290				 Operating Equipment, Total	 99.811,44	100		 
1300				 Vehicles					 0			0		 
1310				 Vehicles					 49.473,91	49	
1320				 Increases during the Year	87.000	 87	
1330				 Decreases during the Year	0			0		 
1340				 Accum. Depreciation, Vehicles  -60.603,78   -61		 
1390				 Vehicles, Total				75.870,13	76	
1395				 Tangible Fixed Assets, Total   1.128.689,52 1129	
1999				 Fixed Assets, Total			1.128.689,52 1129

In the example, the data is exported from the Microsoft Dynamics NAV demo database. A filter has been applied to only export records where the value of the No. field is less than 2000. Also, the source expression for the Net Change field has been changed to FORMAT(ROUND("Net Change "/1000,1,'='),0,1) to export the values of this field as thousands, but the source expression for the Balance at Date field remains unformatted and returns data that is not rounded.

See Also