How to import customers and vendors in Microsoft Dynamics AX 2009

Article translations Article translations
Article ID: 960729 - View products that this article applies to.
  
Expand all | Collapse all

INTRODUCTION

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.

MORE INFORMATION

To import customers in Microsoft Dynamics AX 2009, follow these steps:
  1. In the Navigation Pane, click Administration, expand Periodic, expand Data export/import, expand Excel spreadsheets, and then click Template Wizard.
  2. In the Excel Template Wizard, click Next.
  3. In the File name field, enter the name of the file that you want to use, and then click Next.
  4. On the Select tables page of the wizard, click CustTable in the Available objects area, click the > button to insert the object into the Selected objects area, and then click Next.

    Note For vendors, click VendTable in the Available objects area.
  5. On the Generate fields list page of the wizard, click Next.
  6. On the Select fields page of the wizard, expand Customer, and then click to 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. Click Next.
  8. Follow the steps in the wizard to complete the import.
  9. In the Navigation Pane, click Basic, expand Setup, expand Global Address Book, and then click Parameters.
  10. Click 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, click Administration, expand Periodic, expand Data export/import, and then click 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 click Table setup.
  15. In the Table setup dialog box, click 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. Click to select the Run conversion check box, and then click the Compile button.

    This step determines whether the code is typed correctly.
  17. Click 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 click Import.
  19. In the Excel import dialog box, select the Excel spreadsheet that you created in step 3, and then click 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, click Administration, expand Periodic, expand Data export/import, expand Excel spreadsheets, and then click Template Wizard.
  2. In the Excel Template Wizard, click Next.
  3. In the File name field, enter the name of the file that you want to use, and then click Next.
  4. On the Select tables page of the wizard, click to select the Show all tables option, select Address in the Available objects area, click the > button to insert the object into the Selected objects area, and then click Next.
  5. On the Generate fields list page of the wizard, click Next.
  6. On the Select fields page of the wizard, expand Alt. address, and then click to select the fields that you want to import to Microsoft Dynamics AX.

    NoteRecord-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, click to select the Carrier account number check box.
  8. Click 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 columnTable 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, click Administration, expand Periodic, expand Data export/import, and then click 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 click Table setup.
  18. In the Table setup dialog box, click 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. Click to select the Run conversion check box, and then click the Compile button.

    This step determines whether the code is typed correctly.
  20. Click 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 click Import.
  22. In the Excel import dialog box, select the Excel spreadsheet that you created in step 3, and then click OK.
  23. Verify that the addresses are imported for the customer or for the vendor.

Properties

Article ID: 960729 - Last Review: August 3, 2011 - Revision: 5.0
APPLIES TO
  • Microsoft Dynamics AX 2009
Keywords: 
kbexpertisebeginner kbhowto kbexpertiseinter kbsurveynew kbmbsmigrate KB960729

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com