How to import customers and vendors in Microsoft Dynamics AX 2009

This article describes how to import customers in Microsoft Dynamics AX 2009 from a Microsoft Office Excel spreadsheet. These steps describe how to import customers. However, you can also follow these steps to import vendors.

Applies to:   Microsoft Dynamics AX 2009
Original KB number:   960729

To import customers in Microsoft Dynamics AX 2009, follow these steps:

  1. In the Navigation Pane, select Administration, expand Periodic, expand Data export/import, expand Excel spreadsheets, and then select Template Wizard.

  2. In the Excel Template Wizard, select Next.

  3. In the File name field, enter the name of the file that you want to use, and then select Next.

  4. On the Select tables page of the wizard, select CustTable in the Available objects area, select the > button to insert the object into the Selected objects area, and then select Next.

    Note

    For vendors, select VendTable in the Available objects area.

  5. On the Generate fields list page of the wizard, select Next.

  6. On the Select fields page of the wizard, expand Customer, and then select the fields that you want to import to Microsoft Dynamics AX. The required fields for a CustTable import are as follows:

    • Customer account
    • Customer group
    • Currency
    • Language
    • Address book type
    • Address book ID Name
    • Address
    • Terms of payment
    • Country/region
    • ZIP/postal code
    • State
    • County
    • Search name
    • City
    • Street name
    • Country/region
    • State
    • Company
  7. Select Next.

  8. Follow the steps in the wizard to complete the import.

  9. In the Navigation Pane, select Basic, expand Setup, expand Global Address Book, and then select Parameters.

  10. Select the Number sequences tab, and then verify that a number sequence is selected for the address book ID.

    Note

    The number sequence that is selected cannot be set up as continuous.

  11. Find the file that you created in step 3. The Excel spreadsheet that contains the data that you want to import is populated.

  12. Leave the Address book ID field empty. The Address book ID field is populated when you import the Excel spreadsheet by using the number sequence that you set up in step 10.

  13. In the Navigation Pane, select Administration, expand Periodic, expand Data export/import, and then select Definition groups.

  14. In the Data-export/import dialog box, select the definition group that you created by using the Excel Template Wizard, and then select Table setup.

  15. In the Table setup dialog box, select the Conversion tab, and then add the following code for imports to the CustTable table after the default line in the window.

     str dirId;
     ;
    
    // Check that the custtable table is not associated to Party.
     if (!custTable.PartyId || custTable.PartyId == "")
     {
     // Create a Party entry for the customer.
     dirId = DirParty::createPartyFromCommon(custTable).PartyId;
     custTable.PartyId = dirId;
     }
     else
     {
     DirParty::updatePartyFromCommonInsert(custTable.PartyId,custTable);
     }
    

    To import vendors, add the following code.

    str dirId;
     ;
    
    // Check that the vendtable table is not associated to Party.
     if (!vendTable.PartyId || vendTable.PartyId == "")
     {
     // Create a Party entry for the vendor.
     dirId = DirParty::createPartyFromCommon(vendTable).PartyId;
     vendTable.PartyId = dirId;
     }
     else
     {
     DirParty::updatePartyFromCommonInsert(vendTable.PartyId,vendTable);
     }
    
  16. Select the Run conversion check box, and then select the Compile button.

    This step determines whether the code is typed correctly.

  17. Select the Preview tab, verify that the fields match the correct values that are entered in the Excel spreadsheet, and then close the Table setup dialog box.

  18. In the Data export/import dialog box, select the definition group that you created, and then select Import.

  19. In the Excel import dialog box, select the Excel spreadsheet that you created in step 3, and then select OK.

  20. Verify that your customer is imported into the CustTable table and that a corresponding record is created in the DirPartyTable table.

After you import the customer, you can import additional addresses for the customer. To do this, follow these steps:

  1. In the Navigation Pane, select Administration, expand Periodic, expand Data export/import, expand Excel spreadsheets, and then select Template Wizard.

  2. In the Excel Template Wizard, select Next.

  3. In the File name field, enter the name of the file that you want to use, and then select Next.

  4. On the Select tables page of the wizard, select the Show all tables option, select Address in the Available objects area, select the > button to insert the object into the Selected objects area, and then select Next.

  5. On the Generate fields list page of the wizard, select Next.

  6. On the Select fields page of the wizard, expand Alt. address, and then select the fields that you want to import to Microsoft Dynamics AX.

    Note

    Record-ID and Table ID of the main table must be selected

  7. Select an additional field that is a string that you are not using currently. For example, select the Carrier account number check box.

  8. Select Next.

  9. Follow the steps in the wizard to complete the import.

  10. Find the Excel spreadsheet that you created in step 3, and then fill in the data that you want to import.

    Note

    Set the column Table ID of the main table with 77 for the CustTable or 505 for the VendTable.

  11. Set the Reference field to 0.

  12. For the additional Carrier account number field that you added to the spreadsheet, enter the customer account number.

  13. In the Application Object Tree (AOT), find the importData method in the following object:

    Classes\SysDataImportDefBase

  14. In the importData method, change the code as follows.

    Existing code

    _curcommon.doInsert();
    

    Replacement code

    if (SysExpImpGroup::find(groupId).Type == SysExpImpType::Excel
     && _curCommon.TableId == tablenum(Address))
     {
     _curcommon.Insert();
     }
     else
     {
     _curcommon.doInsert();
     }
    
  15. Save and then compile the SysDataImportDefBase class.

  16. In the Navigation Pane, select Administration, expand Periodic, expand Data export/import, and then select Definition groups.

  17. In the Data-export/import dialog box, select the definition group that you created by using the Excel Template Wizard, and then select Table setup.

  18. In the Table setup dialog box, select the Conversion tab, and then add the following code for imports to the CustTable table after the default line in the window.

    str account;
     ;
    
    // If the address is for a customer and the recid for the customer is not specified
    if (address.AddrTableId == tablenum(CustTable) && address.AddrRecId == 0)
    {
     account = address.ShipCarrierAccount; // The additional field is added to the spreadsheet.
    
    // Fetch the recid of the customer.
     address.AddrRecId = CustTable::find(account).RecId;
     address.ShipCarrierAccount = '';
    }
    

    To import a vendor, add the following code.

    str account;
    ;
    
    // If the address is for a vendor and recid for the vendor is not specified
     if (address.AddrTableId == tablenum(VendTable) && address.AddrRecId == 0)
     {
     account = address.ShipCarrierAccount; // The additional field is added to the spreadsheet.
    
    // Fetch the recid of vendor.
     address.AddrRecId = VendTable::find(account).RecId;
     address.ShipCarrierAccount = '';
     }
    
  19. Select the Run conversion check box, and then select the Compile button.

    This step determines whether the code is typed correctly.

  20. Select the Preview tab, verify that the fields match the correct values that are entered in the Excel spreadsheet, and then close the Table setup dialog box.

  21. In the Data export/import dialog box, select the definition group that you created, and then select Import.

  22. In the Excel import dialog box, select the Excel spreadsheet that you created in step 3, and then select OK.

  23. Verify that the addresses are imported for the customer or for the vendor.