ImportDefinition File Name

Question

Is it possible to get the exact filename from an import definition? My Code

<script runat=“server”>
    Platform.Load(“Core”,“1”);
    var api = new Script.Util.WSProxy();
    try {
        var name = “ExKey”;
        var request = api.retrieve(“ImportDefinition”, [“FileSpec”,“UpdateType”], {
            Property: “CustomerKey”,
            SimpleOperator: “equals”,
            Value: name
        });
        Write(Stringify(request.Results[0].FileSpec));
    } catch(err) {
        Write(Stringify(err));
    }
</script>

FileSpec does not replace the substitution fields (%%year%% etc).

In the email notification post import, we do get the file name (with substitution fields replaced), if the SFMC SFTP is used. For external SFTP, the email notification contains the file name with the substitution fields.

Is there a good way to get the exact file name?

PS: I have seen the question at Accessing Import Filename, but that was 5 years ago, hence asking again

Answer 1

So I did some digging and found what may be a good solution for your request via an undocumented REST API call.

Basically what the call does is pull a history of your imports for that import activity.

GET /automation/v1/imports/{{importActivityID}}/history
Host: {{subDomain}}.rest.marketingcloudapis.com
Authorization: Bearer {{auth_token}}
Content-Type: application/json

It defaults to 50 records per page, but via the $page and $pageSize parameters you can increase this. something like appending ?$page=1&$pageSize=200 to the endpoint.

The return on this call is something like:

{
    "page": 1,
    "pageSize": 200,
    "count": 62,
    "items": [
        {
            "importFileTransferHistoryId": 0,
            "filename": "myFile_20210714.csv",
            "importSucceeded": true,
            "importStatusMessage": "Completed",
            "importStatusDescription": "OK",
            "transferType": 0,
            "activityType": 1,
            "attempts": 1,
            "transferSucceeded": true,
            "importFileStatus": "1",
            "startDate": "2021-07-14T01:35:04.813",
            "totalRecordsCount": 29200058,
            "totalRecordsImported": 29200058,
            "totalRecordsSkipped": 0,
            "totalRecordsDuplicates": 0
        },
        {
            "importFileTransferHistoryId": 0,
            "filename": "myFile_20210711.csv",
            "importSucceeded": true,
            "importStatusMessage": "Completed",
            "importStatusDescription": "OK",
            "transferType": 0,
            "activityType": 1,
            "attempts": 1,
            "transferSucceeded": true,
            "importFileStatus": "1",
            "startDate": "2021-07-11T01:30:06.197",
            "totalRecordsCount": 29122249,
            "totalRecordsImported": 29122249,
            "totalRecordsSkipped": 0,
            "totalRecordsDuplicates": 0
        },
        {
            "importFileTransferHistoryId": 0,
            "filename": "myFile_20210710.csv",
            "importSucceeded": true,
            "importStatusMessage": "Completed",
            "importStatusDescription": "OK",
            "transferType": 0,
            "activityType": 1,
            "attempts": 1,
            "transferSucceeded": true,
            "importFileStatus": "1",
            "startDate": "2021-07-10T01:49:00.963",
            "totalRecordsCount": 29077130,
            "totalRecordsImported": 29077130,
            "totalRecordsSkipped": 0,
            "totalRecordsDuplicates": 0
        }
}

Which should give you the fully rendered file name that you are looking for.

A caveat to this though is that it only returns the past 60 days of records - so anything beyond that would be lost.

This also brings the note of it being an undocumented endpoint so its use at your own risk as it is not officially supported by SF. This means at any moment it could be turned off, changed or otherwise adjusted. It also may not be designed for heavy frequency or volume of calls, which can cause the endpoint to slow down or fail.

Answer 2

For anyone else who might face a similar issue -

From the answer posted by @Gortonington it is not possible to get the actual values in of the wildcard values if you are importing the file from a external API. Quick solution to this (and it works for my use case) is:

1. Use a File Transfer activity with the file action 'Manage File'.
2. Do not select either Unzip or Decrypt option.
3. The file will get transferred to SFMC SFTP with the actual file name.
4. Import file From SFMC FTP
5. Then use the API end point provided by @Gortonington.
6. Get the actual file names. Be Happy!

Answer 3

For people checking this question in 2024, you might need to consider the "slightly" new data view Automation Instance. It contains a field named FilenameFromTrigger:

file drop and trigger automations, the file that started the automation.

With a little tuning, you can get the file name (maybe check the automation run correctly by filtering on AutomationInstanceStatus field,...)