Import a DE Import Error Results file to a Data Extension

If a record/row is not processed due to an error during an Import Definition it is logged as a validation error. When validation errors are logged, a Results.txt file is generated and attached to the Import notification that is received.

The results file is generated from validation errors. If rows were only Skipped and no validation errors were otherwise logged, then no Results file will be generated.

The Results.txt file will output to the ExactTarget FTP “Import” folder and will contain the rows of data and the reason why the row failed the import. Depending on the size of the Results file it may be included as an email attachment.

File name example:
d37b0f3c-7993–7101–9002–3e9468fd5d66 Results 202111080002.txt

Below I outline how to take this extract and import it back into an SFMC DE.

Create the DE_Import_Error_Template

Firstly, create the data extension to house the results. Configure the DE as per the setup below. I like to save this DE in to a “DE Templates” folder, so it is easily accessible to duplicate from each time you wish to create a new DE.

Data Extension Setup

Import the file from FTP

In Email Studio, or Contact Builder, click Records > Import. For the File Location, change the radio button to the FTP option and paste in the file name of the Results file you wish to import from the FTP.

Import

Configure Mapping

Finally check your email, to confirm all the file’s contents were imported.

Import Email Confirmation

From here, if you wanted to split out the Row Data field back out into separate columns you can prepare a query activity similar to the one shown below. The script will convert the Row Data string into XML, and then pluck the corresponding node value and plug it in to the sql select alias we provide.

SELECT
    XmlData.value('/x[1]','nvarchar(max)') AS Node1,
    XmlData.value('/x[2]','nvarchar(max)') AS Node2,
    XmlData.value('/x[3]','nvarchar(max)') AS Node3,
    XmlData.value('/x[4]','nvarchar(max)') AS Node4,
    XmlData.value('/x[5]','nvarchar(max)') AS Node5,
    XmlData.value('/x[6]','nvarchar(max)') AS Node6
FROM
(
    SELECT
        CONVERT(XML, Replace(Replace([Row Data], '[', '<x>'), ']', '</x>')) AS XmlData
    FROM 
        [mobile_connect_import_error_2021-11-07]
) AS X

I would recommend trying to extract only an id field such as the primary key field so you can go back to your source data and easily locate and resolve any import errors.

With my example use case, adjusting the query to add in the Error details, will nicely output the phone numbers, subscriberkey and the exact error I received while trying to import a csv file to Mobile Connect.

SELECT
    XmlData.value('/x[2]','nvarchar(max)') AS SubscriberKey,
    XmlData.value('/x[5]','nvarchar(max)') AS Locale,
    XmlData.value('/x[6]','nvarchar(max)') AS PhoneNumber,
    [Error Code],
    [Error Details]
FROM
(
    SELECT
        CONVERT(XML, Replace(Replace([Row Data], '[', '<x>'), ']', '</x>')) AS XmlData,
        [Error Code],
        [Error Details]
    FROM 
        [mobile_connect_import_error_2021-11-07]
) AS X

See Also: Retrieve the Aggregate Results of an Import Via ImportResultsSummary

Salesforce Developers

<script runat="server">
Platform.Load("core","1.1.1");
try {
    var prox = new Script.Util.WSProxy();
    var cols = [
        "ImportDefinitionCustomerKey",
        "ImportType",
        "ImportStatus",
        "ID",
        "ObjectID",
        "NumberDuplicated",
        "NumberErrors",
        "NumberSuccessful",
        "DestinationID",
        "TaskResultID",
        "TotalRows",
        "StartDate",
        "EndDate"
    ];
    var filter = {
        Property: "ImportDefinitionCustomerKey",
        SimpleOperator: "equals",
        Value: "X6473F2C-51CX-412A-8791-ED20F131C2X2"
    };
var result = prox.retrieve("ImportResultsSummary", cols, filter);
Write(Stringify(result));
} catch(ex) {
     Write("An error has occurred: " + Stringify(ex));
}
</script>