Data Import in AX 2012

Data Migration for Microsoft Dynamics AX 2012

Options for importing data into Microsoft Dynamics AX 2012

This section describes the functionality that is available in Microsoft Dynamics AX 2012 for importing data.

Microsoft Dynamics AX import
Microsoft Dynamics AX import (available from System administration > Common > Data export/import > Import) is the primary mechanism for transferring data between different Microsoft Dynamics AX instances.

You cannot use this import method to migrate data from another ERP system or an older version of Microsoft Dynamics AX. To use this functionality and correctly create definition groups, you should have a clear understanding of the underlying data model. No business logic can be invoked to validate or otherwise modify data when you use this functionality. It simply writes the data into the tables exactly as it is in the import file.

Microsoft Excel Add-in for Microsoft Dynamics AX

The Microsoft Dynamics AX Add-in for Microsoft® Excel® is a powerful tool for viewing and editing data in Microsoft Dynamics AX 2012. You can use the Excel Add-in with document services or to directly access tables. However, to perform full create, read, and update operations, the data and metadata that are being imported must be organized in very specific patterns. Because of these restrictions, not all tables or services are supported.

Using document services with the Excel Add-in to import data
Only document services that meet the following requirements can be used with the Excel Add-in to create or update data:

1.       Replacement keys – The root level of the document service (the parent data source) must have unique indexes other than RecId. These may be in the form of a non-RecId primary index or a replacement key.

2.       Related replacement keys – Each field within the service that is a RecId-based foreign key must relate to a table that specifies a replacement key.

3.       Relationship direction – When parent/child relationships exist in the underlying query associated with the service, only relationships originating on the child element and pointing to the parent may be used.

4.       Query and service consistency – Document services are based on an underlying query that defines the data contract used in the service at the time that the service is generated. To be used with the Excel Add-in, the query and service contract must be synchronized. The Excel Add-in uses this query definition to perform read operations when refreshing data into the workbook. Because of this, any overrides to the read method, or any extension of the schema beyond what is in the underlying query, will not be reflected in the service.

5.       View support – Views may be used within document services to provide an easier-to-use data model for end users. However, any service that uses a view must have the PrepareForXXXExtended methods implemented to define the correct order of operations on save.

Using the Excel Add-in to import tables
Only tables that meet the following requirements can be imported directly by using the Excel Add-in:

Visible identity – There must be a unique index on the table that does not contain RecId as a component. This may be either the replacement key or the primary index.
Valid references – All relations in the relations collection for the table that refer to other tables via RecId must be related to tables that have a replacement key specified.

AIF, document services, and custom code

AIF provides the capability to integrate Microsoft Dynamics AX with other systems inside and outside the enterprise. AIF provides this capability by enabling the exchange of data through formatted XML. This formatted XML is referred to as a document, and each document contains data and business logic. Documents are based on a document class and defined by using Microsoft Dynamics AX.
Microsoft Dynamics AX is shipped with many standard document services that support common business processes. By using the capabilities that AIF provides, you can also customize existing document services or create your own document services. For more information about standard document services and how to create your own document services, see Services and AIF development.

We recommend that you use document services to populate more complex data portions of the data model to ensure data integrity.

Examples of using AIF to import global address book–related data

In this section, we provide examples that demonstrate how to import customers, vendors, and HCMWorkers, all of which are related to the global address book. The global address book refers to the structures that contain parties and roles, such as customers, vendors, contact people, prospects, leads, and business relations. The global address book uses several core concepts to model real-world relationships. The following figure illustrates where these concepts are used in the data model.

Customer import

In this example, we demonstrate how to import customer data by using the CustomerService service.

Expose an inbound port for the customer service
To call the service, you need to expose the service on a new inbound port in Microsoft Dynamics AX.
1. Click System administration > Setup > Services and Application Integration Framework > Inbound ports, and then click New.
2. Give the new port a name, such as Customer Services.
3. Enter a description.
4. Under Service Contract Customizations, click Service Operations. The Select service operations form opens.
5. Select the CustCustomerService operations, move them to the Selected operations pane, and then click Close.

6. In the Inbound ports form, click Activate.
You can now access the service externally by using the WSDL URI.

Set up the customer service reference in Microsoft Visual Studio
1. Open Microsoft Visual Studio®, and create a new project.
2. Add a new service reference by using the WSDL URI for the customer service from the Inbound ports form.
3. Add a Using statement for the service reference.

You are now ready to start coding against the customer service.

Sample code to import customers from a .csv file by using a Visual Studio service reference
This section provides a sample that shows the correct order of operations when using the customer service to create a new customer in Microsoft Dynamics AX 2012.

//Index locations of the data columns in the .csv file.

//How you choose to manage the index locations may vary depending on your implementation and structures used

internal static int NameIndex = 0;

internal static int KnownAsIndex = 1;

internal static int PhoneticNameIndex = 2;
internal static int OrganizationNameIndex = 3;
internal static int NameAliasIndex = 4;
internal static int CustGroupIndex = 5;
internal static int SalesGroupIndex = 6;
internal static int DlvModeIndex = 7;
internal static int InvoiceAddressTypeIndex = 8;
internal static int PostalLocationNameIndex = 9;
internal static int PostalstreetIndex = 10;
internal static int PostalBuildingComplimentIndex = 11;
internal static int PostalPOBoxIndex = 12;
internal static int PostalCityIndex = 13;
internal static int PostalStateIndex = 14;
internal static int PostalZipIndex = 15;
internal static int PostalCountryIndex = 16;
internal static int PostalRolesIndex = 17;
internal static int IsPostalPrimaryIndex = 18;
internal static int ContactLocationNameIndex = 19;
internal static int LocatorIndex = 20;
internal static int ContactTypeIndex = 21;
internal static int ContactRolesIndex = 22;
internal static int IsContactPrimaryIndex = 23;

//Create a new Instance of the Customer Service client

CustomerServiceClient custSvcClient = new CustomerServiceClient();

//Read in your .csv file however you want to

//This is just provided as a reference
List<string[]> CustData = ParseCSVFile("C:\\CustomerImport.csv", true);

//Create a customer for each row in the .csv file

foreach (string[] customer in CustData)
{
AxdCustomer axdCustomer = new AxdCustomer();
AxdEntity_DirParty_DirPartyTable dirPartyTable = new AxdEntity_DirParty_DirPartyTable();
AxdEntity_CustTable custTable = new AxdEntity_CustTable();
AxdEntity_CustTable[] customerList = new AxdEntity_CustTable[1];
EntityKey[] entityKey;

//Create the base organization that the customer will belong

AxdEntity_DirParty_DirOrganization org = new AxdEntity_DirParty_DirOrganization()
{
KnownAs = customer[KnownAsIndex],
PhoneticName = customer[PhoneticNameIndex],
OrganizationName = new AxdEntity_OrganizationName[1],
NameAlias = customer[NameAliasIndex],
Name = customer[NameIndex]
};

// Create the Organization name for the Customer

AxdEntity_OrganizationName orgName = new AxdEntity_OrganizationName()
{
Name = customer[OrganizationNameIndex]
};

//Assign organization name to the base organization.

org.OrganizationName[0] = orgName;

//Add an address to the org. You can add a loop and add as many address and types you want to here

AxdEntity_DirPartyPostalAddressView[] postalAddView = new AxdEntity_DirPartyPostalAddressView[1];
postalAddView[0] = new AxdEntity_DirPartyPostalAddressView()
{
LocationName = customer[PostalLocationNameIndex],
BuildingCompliment = customer[PostalBuildingComplimentIndex],
City = customer[PostalCityIndex],
State = customer.[PostalStateIndex],
ZipCode = customer[PostalZipIndex],
Street = customer[PostalstreetIndex],
CountryRegionId = customer[PostalCountryIndex],

//Valid Values come from the Typed column logisticsLocationRole where //IsPostalAddress = true

Roles = customer[PostalRolesIndex],
IsPrimary =
(AxdExtType_LogisticsIsPrimaryAddress)Enum.Parse(typeof(AxdExtType_LogisticsIsPrimaryAddress),customer[IsPostalPrimaryIndex])
};

//Add the party Address to the Organization

org.DirPartyPostalAddressView = postalAddView;

//Add a contact to the organization. You can add a loop and add as many address //and types you want to here

AxdEntity_DirPartyContactInfoView[] contactList = new AxdEntity_DirPartyContactInfoView[1];
contactList[0] = new AxdEntity_DirPartyContactInfoView()
{
LocationName = customer[ContactLocationNameIndex],

//Value of locator will vary by type.

Locator = customer[LocatorIndex],
IsPrimarySpecified = true,
IsPrimary = (AxdEnum_NoYes)Enum.Parse(typeof(AxdEnum_NoYes),
customer[IsContactPrimaryIndex]),
TypeSpecified = true,
Type = (AxdEnum_LogisticsElectronicAddressMethodType)Enum.Parse(typeof(AxdEnum_LogisticsElectronicAddressMethodType), customer[ContactTypeIndex]),
//Valid Values come from the Typed column logisticsLocationRole where //IsContactInfo = true
Roles = customer[ContactRolesIndex]
};

//Add the contacts to the organization

org.DirPartyContactInfoView = contactList;

//Set the customer specific information on the CustTable

custTable.CustGroup = customer[CustGroupIndex];
custTable.InvoiceAddress = (AxdExtType_CustInvoiceAddress)Enum.Parse(typeof(AxdExtType_CustInvoiceAddress),customer[InvoiceAddressTypeIndex]);
custTable.InvoiceAddressSpecified = true;
custTable.DlvMode = customer[DlvModeIndex];
custTable.SalesGroup = customer[SalesGroupIndex];

//Assign the organization to the customer

custTable.DirParty = new AxdEntity_DirParty_DirPartyTable[1];
custTable.DirParty[0] = org;

//Add the specific customer to the list

customerList[0] = custTable;
axdCustomer.CustTable = customerList;

//Set the header fields
axdCustomer.DocPurpose = AxdEnum_XMLDocPurpose.Original;
axdCustomer.DocPurposeSpecified = true;

//Create the customer
entityKey = custSvcClient.create(null, axdCustomer);
}
}

//Simple .csv parser without any robust error handling. Please implement your

//own logic to parse your csv file or use existing parsing libraries.
//This code is provided only as a sample.

public static List<string[]> ParseCSVFile(string path, Boolean hasHeaders)

{
List<string[]> CSVRows = new List<string[]>();
int rowNumber = 0;
using (StreamReader inputFile = new StreamReader(path))
{
string line;
string[] columns;
while ((line = inputFile.ReadLine()) != null)
{
columns = line.Split(',');
if (hasHeaders != true || rowNumber != 0)
{
CSVRows.Add(columns);
}
rowNumber++;
}
}
return CSVRows;
}

Sample .csv file to use for import
The preceding sample assumes that a .csv file with the following headers exists. The CustGroup, SalesGroup, and other reference values come from a system where the reference data was already set up.

Header field	Values row 1	Values row 2
Name	Customer One	Customer Two
KnownAs	cust1	cust2
PhoneticName	Customer1	Customer2
OrganizationName	Customer One	Customer Two
NameAlias	cust1	cust2
CustGroup	10	10
SalesGroup	10	20
DlvMode	1	10
InvoiceAddressType	InvoiceAccount	InvoiceAccount
PostalLocationName	Primary	Primary
Postalstreet	11111 Sample Lane	22222 Sample Lane
PostalBuildingCompliment		Building 1
PostalPOBox
PostalCity	Redmond	Redmond
PostalState	WA	WA
PostalZip	98052	98052
PostalCountry	USA	USA
PostalRoles	Delivery;Invoice;Business	Delivery;Invoice;Business
IsPostalPrimary	Yes	Yes
ContactLocationName	Primary Phone	Primary Phone
Locator	111-222-333-4444	111-222-333-4444
ContactType	Phone	Phone
ContactRoles	Business	Business
IsContactPrimary	Yes	Yes

Related Posts for your reference...

• Data Import in AX 2012 - Employee
• Data import in ax 2012 - Vendor

If you need Product, Product Master, Route using AIF and C#. you can contact me.