In Microsoft Dynamics NAV, you can set dataports to import data from an external file with a fixed format or a variable format. This topic describes how to create a dataport for importing data from an external file.

Creating a dataport that imports data is not very different from creating one that exports data. However, you must consider how the imported records are inserted in the database table on which the data item is based. This is especially relevant if the table already contains records that have the same primary key as some of the records that will be imported.

To create a dataport

  1. On the Tools menu, click Object Designer.

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

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

    Property Value

    Import

    Yes

    FileFormat

    Fixed

  4. In Dataport Designer, in the first DataItem field, click the AssistButton, and then select the table that you want to add to the dataport. This is the table that the data will be imported into. For example, to import data into the Item table, select table 27.

    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.

NoteNote

You must know the number of fields per record in the external file, and you must know the position of each field.

To add fields to a data item

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

  2. On the View menu, click Dataport Fields.

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

    This can be a field in the table to which the data item corresponds, 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, then 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, then the value of the SourceExpr field can be "Tariff Number".Description, where Tariff Number is a global variable for the Tariff Number table.

    You can also use the field menu to add multiple fields from the current table.

    1. When Field Designer is open, on the View menu, click Field Menu.

    2. In the Field Menu window, select the fields to which you want to import.

      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 Field Designer, and then 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.

    NoteNote

    Ensure that the values match the external file. If the fields have a different order in your table then they do in the file that you are importing, you can add the fields manually and ensure that the design of the data item reflects the order that you need. You must then set the StartPos and Width properties manually.

    If one of the fields is the primary key of the table, then the order in which the records are displayed is determined by the primary key, which may not be the same order as they appeared in the original import file.

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, save the dataport before running it. For more information, see How to: Save a Dataport.

  3. On the File menu, click Run to run the dataport.

    When you run the dataport, the request form appears.

  4. In the request form, on the Options tab, in the File Name field, enter the location and the name of the file from which the data will be imported.

  5. Optionally, on the tab for the data item, set filters to specify which data to import.

  6. To run the dataport, click OK.

When the dataport run is finished, the records in the Microsoft Dynamics NAV database are updated.

NoteImportant

The records are not imported if you run the dataport from inside Dataport Designer. You must run it from somewhere else, such as from Object Designer.

Importing with Variable Format

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

To import 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

    Yes

    FileFormat

    Variable

    FieldSeparator

    ,

    FieldStartDelimiter

    <None>

    FieldEndDelimiter

    <None>

    FieldSeparator is set to use a 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 depend upon the target application for the external file and the formats that the application supports when it imports text files.

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

    NoteNote

    The values of the StartPos and Width properties are not used in a dataport with a variable format. If the order of the fields in the import file is different from the order of the fields in the data item, then you must change the order of the fields in the data item by adding them manually in Field Designer.

  3. When you have completed defining the dataport, save and run it.

When you run the dataport, the result is the same as running the dataport with a fixed format.

Common Errors

It is easy to make errors when deciding how to parse the lines in the file that you are importing. In some cases, this will result in a run-time error when the dataport is run, such as if you make an error in setting up the Field No. field and assign it a width that is one character too wide. For most of the import file, this would make no difference because the resulting trailing space would be ignored. But in a line that starts with 112Oven, you would get a run-time error when C/SIDE reads 112O instead of 112. The letter O cannot be inserted into an integer field.

In other cases, the error might not be detected by C/SIDE, such as if the parse between two text fields is placed incorrectly. Always test your imports carefully before using them for production.

In some cases, the fields have a different order in your table than they do in the file that you are importing. You can add the fields manually and ensure that the design of the data item reflects the order that you need. You must set the StartPos and Width properties manually.

See Also