Data can be easily imported from Microsoft Office Excel by using the import feature of Microsoft Dynamics AX. To reduce the potential of error, the import feature include a wizard that generates Microsoft Office Excel workbook templates and a dialog box that controls the import of data into Microsoft Dynamics AX after the data has been entered into the workbook.

Note Note

Microsoft Windows Regional options date format and the date format in Microsoft Office Excel on the computer that is performing the import must be the same. If the formats are not the same, the date field will be empty.

Import data from Microsoft Excel

  1. Click > > > > .

    The helps you create one or more templates in Microsoft Office Excel where you can enter data, and then import the data into Microsoft Dynamics AX.

    The wizard creates a worksheet in the workbook for each table that you select.

    Complete the wizard, as described in Create a template in Microsoft Office Excel. While completing the wizard, select to create a definition group.

  2. Open the Excel workbook that contains the templates and enter necessary data, as described in Enter data in Microsoft Office Excel.

    Note Note

    Protect data by adding password protection to the worksheet or workbook using Excel's password protection feature.

  3. Click > > > > and select the definition group that was created by the Template wizard. Click .

    The dialog box appears.

  4. Select the Excel workbook to import data from and then click OK.

    Based on the worksheet name, the import allocates the contents of each worksheet into the corresponding table in Microsoft Dynamics AX.

Tips for importing data from Excel

  • After you create a Microsoft Office Excel template using the Template wizard, you can import data from third-party systems into Excel and from Excel into the Microsoft Dynamics AX template. The easiest way to do this is:

    • Put the data from your third-party system into a different Excel spreadsheet.

    • Map the contents of the Excel spreadsheet to the Microsoft Dynamics AX template. The mapped contents appear in the template spreadsheet.

  • The Microsoft Office Excel template lists each field to be imported into Microsoft Dynamics AX. When populating the Excel spreadsheet, many fields are associated with another Microsoft Dynamics AX table. Therefore, make sure that you use the correct codes.

    For example, Customer groups may be defined as DOM = Domestic and FOR = Foreign; DOM or FOR should be entered on the spreadsheet. The values for these types of user-defined fields are not available from Excel. However, lists of the setup values can be printed from Microsoft Dynamics AX.

  • Data import from Microsoft Office Excel into Microsoft Dynamics AX is optimal when the data types in the Excel worksheet match the ones in the Microsoft Dynamics AX fields. When a template based on Microsoft Dynamics AX is created, the cells in the worksheet have an Excel format called 'Text'. The contents of cells with text format are treated as text even when a number is in the cell. This format is useful because numbers frequently contain spaces, parentheses, or dashes. This can produce poor data if they are saved in the 'Number' format.

    When Excel data is imported, Microsoft Dynamics AX converts the data to the required format only if the entered data type is compatible with the required type. For example, a string type entered in an Excel cell that should be imported into a Microsoft Dynamics AX field of integer type is not imported into Microsoft Dynamics AX. This also means that wherever enum values are expected, the actual string value must be entered instead of the representative integers.

  • Begin entering data starting at row 7.

    Caution note Caution

    Rows 2 through 6 in the workbook are used to map the fields back to Microsoft Dynamics AX and are hidden deliberately. Do not delete them.

    • Verify that all required fields are populated before importing.

    • Complete any additional fields that your old system may not have had available.

    • Edit records as necessary to clean up the database.

      Note Note

      Remember that Microsoft Dynamics AX has already created the mapping of this template to the Microsoft Dynamics AX database. Therefore, do not delete or rearrange columns. If you are no longer using a column, hide it.

  • Before populating the whole spreadsheet, try importing a few records to verify the import is working correctly. Verify the import status before re-importing. The status is used to determine whether the re-import should replace or append the existing records. Check the status by clicking the button in the group.

See Also