/
How to setup a sFTP export in 5.8+ and format the Metadata search results.

How to setup a sFTP export in 5.8+ and format the Metadata search results.

Owned by Dave Clark
Last updated: Apr 22, 2025

Please note, this is a long and complicated process, usually 20 hours is a good estimate.

  1. Test the clients sFTP.
    a. They will need to give you the Host address, username, password and port for the sFTP they want the assets sent to.
    b. Open up Filezilla or WinSCP and make sure you can connect to the sFTP.

 

  1. Setup the sFTP automation.
    a. Use this automation to create your sFTP asset export automation, filling in the appropriate info for your specific sFTP -

trigger "ManualTrigger" { type = "ManualTrigger" resolves = "Upload asset to FTP" } action "Get Asset Information" { type = "Get Asset Information" asset_id = "@sourceAssetId" member_id = "@sourceMemberId" member_username = "@a" upload_computer = "@b" is_soft_deleted = "@c" source_file_name = "@sourceFilename" source_file_extension = "@d" asset_code = "@e" derived_from = "@f" replaced_with = "@g" height = "@h" width = "@i" source_file_size = "@j" upload_date = "@k" image_resolution_dpi_xres = "@l" image_resolution_yers = "@m" hash_md5 = "@n" asset_size = "@o" video_duration = "@p" video_aspect_ration = "@q" file_type = "@r" hash_sha1 = "@s" video_resolution = "@t" original_file_name = "@u" } action "Upload asset to FTP" { type = "Upload asset to FTP" asset_id = "@sourceAssetId" format_id = "-1" upload_path = "@sourceFilename" ftp_host = "sftp://127.0.0.1" ftp_port = "22" ftp_username = "FTPUser" ftp_password = "" overwrite_existing = "true" ignore_certificate_errors = "true" needs = "Get Asset Information" }

 

  1. Test your automation.
    a. Upload a test asset or find a test asset on the client MM and pass its assetId in the manual trigger on your sFTP upload automation.
    b. Get confirmation of asset received from the client

 

  1. Pull the list of assetIds from the DB to run through the automation. Use the defining characteristics of the assets to narrow down your list.

***THE METADATA SECTION HAS BEEN MADE OBSOLETE BY THE NEW METADATA EXTRACT APP (see doc link below)***

https://keyshot.atlassian.net/wiki/x/B4B5AwE

  1. Create your metadata search.
    a. In the DAM Center, scroll to config manager → DAM Center, select searches and click on the Add new search button -

image-20250310-170604.png

b. You may need to beak the search into two or more different searches depending on how many metafields you are returning, the API will throw a critical exception when the search is called if there is too much metadata returned.
c. These are general settings for the Input (request) side of the search, what parameters you send to trigger the search. These settings will return all assets in the client DB that are not soft deleted. Adjust per your search needs -

image-20250310-182400.png


d. The Output tab is what the search will return for each asset’s metadata. Click Add to begin adding asset metafields. Each field must be added individually -

image-20250310-182609.png

e. Select the Metadata field tab and choose metafield group button to add a specific metafield -

image-20250310-182906.png
image-20250310-182925.png
image-20250310-182947.png

If you want to add data directly from a DB table, select the Table and column tab and select the table, then the column name that the value you want is in -

image-20250310-183056.png

f. When you have added all the metafields from all of the metadata groups you want returned in the search, click the save button by the Add button.

 

  1. Test your search. The best way to do this is to call your search in Postman. Here is the base url for the new API search: https://admin-media.mdrt.org/dmm3bwsv3/SearchService.js?
    a. Then add your input parameters. The minimum you will need are an accesskey (to the DC) and the search name. It is a good idea to add a limit -

    image-20250311-201255.png

    b. If you get a critical exception response, then you have too much metadata coming back in the response. You can add a page param with the limit or you can break the search into multiple searches returning different metafields(recommended).

 

  1. Run your search in Excel.
    a. copy your search from postman and open an excel doc. Click on the data tab, then the From Web button -

    image-20250311-202049.png

b. The first time you run the search Excel will prompt you for credentials. Enter the SuperAdmin user and password for the environment you accessing.

  1. Formatting the search results (part 1).
    a. Click the into Table button when excel transitions to Power Query editor -

    image-20250321-125454.png

Then hit To Table -

image-20250321-125548.png

b. Click on the little expand icon in your condensed column -

image-20250321-125650.png

Keep the delimitator as None and click OK on the modal -

image-20250321-125736.png

Uncheck the Use original column name as prefix option on the next modal (this will save you pain later on) and then click OK -

image-20250321-125848.png

  1. Formatting the search results (part 2). Expanding list.
    a. Now that your columns have expanded out, we need to further expand the columns with lists (multiple values in one cell), unfortunately this is not as easy as it should be. First, copy all of the column names. Note which columns have a List value -

    image-20250321-130149.png

b. Now click on the Advanced Editor button -

image-20250321-130256.png

c. You will need to modify this code with your searches details (please note the sections you need to enter your own data) -

let // Retrieve data from the JSON source Source = Json.Document(Web.Contents("https:// YOUR ENTIRE SEARCH HERE INCLUDING THE ACCESSKEY ")), items = Source[items], // Convert the list to a table #"Converted to Table" = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error), // Expand the main record columns - This should be your column names - can be copied from original Advanced Editor search - #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"itemId", "assetAssetid", "Keywords", "Language", "Note", "Source_Publication", "Target_Audience", "Target_Region", "Title", "Topic", "Fullpath_UNC", "Published_to", "Status", "Unlocked"}, {"itemId", "assetAssetid", "Keywords", "Language", "Note", "Source_Publication", "Target_Audience", "Target_Region", "Title", "Topic", "Fullpath_UNC", "Published_to", "Status", "Unlocked"}), // Ensure 'Keywords' and 'Target_Audience' columns are consistently lists -- YOU NEED TO ENTER ALL OF THE COLUMNS THAT HAVE LISTS IN THEM HERE - #"Ensure Lists" = Table.TransformColumns(#"Expanded Column1", { {"Keywords", each if _ = null then {} else if Type.Is(Value.Type(_), List.Type) then _ else {_}}, {"Target_Region", each if _ = null then {} else if Type.Is(Value.Type(_), List.Type) then _ else {_}}, {"Topic", each if _ = null then {} else if Type.Is(Value.Type(_), List.Type) then _ else {_}}, {"Source_Publication", each if _ = null then {} else if Type.Is(Value.Type(_), List.Type) then _ else {_}}, {"Target_Audience", each if _ = null then {} else if Type.Is(Value.Type(_), List.Type) then _ else {_}} }) in #"Ensure Lists"
image-20250321-131425.png

d. When you have clicked done the Advanced Editor will now allow you to expand all the columns that contained Lists-

image-20250321-131532.png

Select “Extract Values…” From the dropdown -

image-20250321-131610.png

And select Comma as your value delimiter (comma works best) -

image-20250321-131647.png

  1. Turning it back into an Excel doc.
    a. Once you have checked that all of the List have been expanded and look correct, you can click the Close & Load button and Power Query will turn this into a Excel file and you should be good to edit as an Excel doc from here-

    image-20250321-131845.png

Congratulation, if you have made it this far you are a champ and have completed a big project. Good job!