Dynamics AX Data Migration Importing Numerical Values from Text Files

Michael Oakes

The Microsoft Dynamics AX/D365 Support Team at Avantiico is focused on solving our client’s problems, from daily issues to large and more complex problems.

See All Posts

April 21, 2015
9:09 am
No Comments
Read Time: 20 minutes

During data migration you may have to import data from legacy systems that output data in a text or CSV file. This data may need special formatting so that the data can be properly read by Dynamics AX. This is especially true when importing numerical values from a text file. For Example, the value ’34,203.00’ in the text file may be incorrectly read as the value 34 in Dynamics AX if the data is not properly converted from a text value to a numerical value because of the comma in the value. It is possible to strip out commas so the data can be properly converted, however it’s also possible to use modern .net code design patterns to convert the data.

Properly Converting to a Numerical Value in Dynamics AX

Converting a numerical value in a text or CSV file may not be properly converted to a numerical value in AX. For example, during data migration, if you import customer data with a credit limit value then this may not be properly converted to a number. Figure 1 shows sample code that simulates a value of “25,239.78”. If this text value contains a comma, it may not be converted properly to a number.

static void AXMTestStr2Num(Args _args)
{
str inputStr = "25,239.20";
real realConversion;
;
realConversion = str2num(inputStr);
}

Figure 1. Sample code conversion from a text file.

If we use standard conversion code in AX to convert a string value to a number, we can see this truncating the number. See Figure 2.

Figure 2. Text value not properly converted to a number

Figure 3 and Figure 4 shows .net code that can be called from Dynamics AX to convert the string value to a number.

static void AXMTestStr2Num2(Args _args)
{
str inputStr = "25,239.20";
real realConversion;
;
System.Decimal::TryParse(
inputStr,
System.Globalization.NumberStyles::Number,
System.Globalization.CultureInfo::CreateSpecificCulture(infolog.language()),
byRef realConversion);

}

Figure 3: Sample code & .net formatting

Figure 4: .net conversion results

This approach can be extended to convert string values to other AX data types, including Dynamics AX Date and date/Time fields.

Reading in data from text files to Dynamics AX can lead to issues when converting text values to non-text values, such as numerical values, or date/time values. This code snippet shows how text values containing commas can be converted to numerical values.