FTP/SFTP File Pickup | Yext Hitchhikers Platform

With the FTP/SFTP Generic Source you can upload files stored in your FTP/FTPS/SFTP server Excel, CSV, and JSON files within the Connectors framework. This source allows users to take advantage of the data cleansing capabilities of the Connectors workflow for ingesting and updating data.

Connection Settings

FTP

If you are connecting to an FTP server, you’ll need to provide the following information:

• Host: this is the host address of your server. Omit the protocol (ftp://) from your input
• Port: this is the port to connect to your server. The default port for FTP is 21.
• Username
• Password

FTPS

If you are connecting to an FTPS server, you’ll need to provide the following information:

• Host: this is the host address of your server. Omit the protocol (ftps://) from your input
• Port: this is the port to connect to your server. The default port for FTPS is 990.
• Username
• SSH Private Key

SFTP

If you are connecting to an SFTP server, you’ll need to provide the following information:

• Host: this is the host address of your server. Omit the protocol (sftp://) from your input
• Port: this is the port to connect to your server. The default port for SFTP is 22.
• Authentication Method: connect to your server either via basic authentication or SSH Private Key authentication.
• Basic Auth: input a username and password SSH Private Key: input a username and your SSH Private Key

Supported Key Exchange to Connect to Server

diffie-hellman-group-exchange-sha1,diffie-hellman-group1-sha1,diffie-hellman-group14-sha1,diffie-hellman-group-exchange-sha256,ecdh-sha2-nistp256,ecdh-sha2-nistp384,ecdh-sha2-nistp521

File Path Settings

Once connected to the Server, configure how the Connector should find the correct file to upload. This can be done in 1 of 2 ways:

Lookup Strategy = Exact File Path

If you know the exact path and name of the file to pull, this strategy will work best. Enter the path to the file, including the root folder and all nested folders, ending with the file name and extension.

E.g. root/folder1/folder2/myfile.csv

Lookup Strategy = Search by Criteria

If you know that you want to pull a file in a certain folder, but that file name might constantly change, this strategy will work best.

1. Input whether to pull the Newest or Oldest file, if multiple are found that match the given criteria.
2. Input the folder path that contains your file (this might be a root folder, represented only by /, or nested folders, e.g. /folder/nestedfolder).
3. Optional: input a set of rules to filter by file name. Choose to filter down to a file name that starts with, ends with, contains, or matches a regex pattern of a given value.

Example use case: Say you have a folder where you store files that contain your business locations. Each week, you upload an updated version of the file, where the file name contains the date of the upload, i.e. “locations_02-10-2023”. To ensure that the most up-to-date file is always pulled, you might input the following settings:

1. Last Modified = Newest
2. Folder Path = /folder/nestedfolder
3. File name = contains “locations”

NOTE: The file MUST be that of a supported file type, and match that of the file type selected in the next step. If a file is pulled that contains an unsupported extension (such as .doc) will result in an ETL error when the Connector is run.

File Settings

Based on the type of your uploaded file, you’ll need to configure your file settings. The uploaded file type must match that of the chosen file type in the connector.

Excel

Extract Sheets: Specify the sheets you would like to extract from your workbook.

• If you want to extract the entire workbook, you can choose Entire Workbook.
• If you want to extract a specific sheet or set of sheets, click Specific Sheet(s) and enter the individual sheet or range of sheets.
  • Note: the first sheet in your workbook is 0. To extract a range of sheets, you can use a comma to separate individually specified sheets, and a dash to represent an inclusive range. For example, and input of 0,2-5,7-9 will extract sheets 0,2,3,4,5,7,8,9.

Merge Sheets: Designate how to merge your workbook sheets, should you choose to extract multiple sheets.

• NOTE: If you are only extracting a single sheet, choosing to merge horizontally vs. vertically is irrelevant, but it still must be set. You may arbitrarily choose to leave the value as the default, and it will not have any impact on your upload.

1. Horizontally
   • Each new sheet’s columns will be appended to the right of the previous sheet. This works well when the same entities’ data is represented in each sheet, just with additional data added per entity (i.e. row 2 in Sheet1 represents the same entity as row 2 in Sheet2).
     
2. Vertically
   • Each new sheet’s columns will be appended below the previous sheet. This is intended for sheets containing the data for different sets of entities (i.e each row in each sheet is a new row in the table).

Has Header Row: Specify whether your Sheets have or do not have a header row.

• If the first row of data is your column headers (with cell values like Name, Address, Description, for example) then toggle on “Has Header Row”. This will help properly generate selectors in the next step and ensure that the header row is not considered an actual data point.
• If you do now have a header row, and the first row contains actual data, toggle off “Has Header Row”. This will ensure that the first row of data is included and help properly generate selectors in the next step.

CSV (Or Any Delimited File)

Specify Delimiter: Specify the character used to delimit your columns.

By default this will be set to Comma as it is highly likely that a comma will be your delimiter. Should your file use something different, you can specify that value by choosing “Custom” delimiter. Note that we automatically delimit rows by a new line.

The extension of your file can be .csv, or it can be the extension of any delimited file such as .tsv, .psv, or .txt.

Has Header Row: Specify whether your file has a header row.

• If the first row of data is your column headers (with values like Name, Address, Description, for example) then toggle on “Has Header Row”. This will help properly generate selectors in the next step and ensure that the header row is not considered an actual data point.
• If you do now have a header row, and the first row contains actual data, toggle off “Has Header Row”. This will ensure that the first row of data is included and help properly generate selectors in the next step.

JSON

If uploading a JSON file, you’ll be prompted to continue and select whether your file contains data for a single entity or multiple entity.

When you look at the file you will be able to see this. If the file contains a list of entities, you will need to add the JMESPath Syntax for that entity container so we can identify and select the nested data for each.