Part 4a – Integromat Workflow – Fetch data from FTPS and filter it out

Pre-requisities for this step

Assuming that the bank (in our case, American Express) pushes the data onto an FTPS server to which you get access, we will need to pull the .CSV file that contains the employees’ expenses and convert it into a spreadsheet file – we will use Google Sheets.

• A bank-provided comma (or other-delimited) CSV file export of transactions.
• A Google Workspace business account (you could replace it with an O365 account, instead, to conver the comma delimited CSV file to a spreadsheet format).
• An Integromat account (a free subscription will work).

Examine the CSV file name structure

In case of AMEX, the bank outputs their .CSV formatted file using this file name convention: R1123296_B000387511_GL9999_001_20220328060907_0021

What is important for us is the ‘GL9999’ part (part of the account number to make sure we fetch the right type of data) and then the date format ‘20220328060907‘- YYYYMMDDHHMMSS (year, month, day, hour, minute, seconds). We want to capture these two and save them into variables and then upload them to Google Sheet to be converted from .CSV into Google Sheets, so that we can work with the data further in the later parts of this tutorial.

Create a new (free) Integromat scenario.

1. FTP module (Watch files) – add the FTPS details provided to you by the bank. In case of AMEX, the new files are stored in the ‘sent’ folder.

2. Set variable – Fetch file name (use a function called split() to divide the file name into individual parts).

3. Create a filter between the first two tiles that will ensure that files that do not contain the required account name are ignored.

4. Add a tile called ‘Set multiple variables’ to determine details for the file name using the substring() function. This function is not smart, it just robotically splits the file name based on the divider that you set.
   • File day: {{substring(2.Split filename to get the date[5]; 6; 8)}}
   • File month: {{substring(2.Split filename to get the date[5]; 4; 6)}}
   • File year: {{substring(2.Split filename to get the date[5]; 0; 4)}}

5. Add a tile for Google Drive called ‘Upload a file’.
   • Add your company Google account (if you have one)
   • Select where to upload it to, i.e. in our case, it is a shared drive called ‘Automated’ into a folder call ‘- RAW AMEX Imports -‘.
   • The file name can be in this format: {{8.File Year}}-{{8.File Month}}-{{8.File Day}}{{2.Split filename to get the date[3]}}{{2.Split filename to get the date[6]}}
   • For Target Google File type, choose application/vnd.google-apps.spreadsheet. This will automatically convert the .CSV file into a spreadsheet format. The assumption is that the file is delimited by commas (some banks provide the file in ASCII format just with spaces, in which case, ask them to provide with a comma delimiter).

6. Just in case the file is rather empty, to avoid processing a file without data later, add a filter in between the last two tiles:

Go back to course overview: Concur Alternative: Import Employees’ Credit Card Expenses to WordPress via an automated bank feed – Gravity Form/Flow & Make.com (Integromat)