I like to validate the data as much as possible throughout the process of migrating data into AX.  Usually this means reviewing the input file, loading the file, and clicking “Validate all”  before actually running the data into the target table.

During the “Validate All” you may receive a plethora of messages.  Sorting through thousands of messages can be a major drag.  I like to bring it into Excel so I can categorize each item and make a list of the values which are not in the main table / setup table.  For example, an imported vendor record may have a payment term of ‘Net 45’ yet that term of payment has not been set up in AX.

1. Click “Validate All” on the Staging Data view (from the Execution History screen)

2. Right click the top node in the infolog and choose “Copy as list to clipboard”

3. Paste into a new Excel file

4. At this point I like to delete the Warning/Info/Error column and add a Category column, where I key in some categories to group the messages together.

5. Add a reference to “Microsoft VBScript Regular Expressions 5.5” using Tools > References

6. Create a new module

7. Copy and paste the following code

Public Function extractValueUsingRegex(Myrange As Range) As String
    Dim r As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim strReplace As String
    Dim strOutput As String
 
    strPattern = “^The value ‘(.*?)’.*”
    strInput = Myrange.Value
    strReplace = “$1”
 
    With r
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = strPattern
    End With
 
    If r.test(strInput) Then
        extractValueUsingRegex = r.Replace(strInput, strReplace)
    Else
        extractValueUsingRegex = “”
    End If

 

End Function

Your file should look similar to this:

8. Change the filter if needed (Optional, Advanced)

9. Save the file as a Macro-Enabled Workbook (.xlsm) to ensure that your code and work are not lost.

10. Add a column using your newly-created custom function 🙂  It will extract the missing value for you.

The function could be duplicated to create a related table column, which would parse the error message for the table name.

Reference: https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops