Automated File Upload Via SFTP

TimelinePI will configure the SFTP destination and shares the location and credentials with the client for the scheduled file push.  The data upload from the SFTP server is then an automated process where the files are automatically picked up and processed into a project or one or more To-Do lists in ETL which will result in that data being loaded into a project.

The user can either create To Do lists within the ETL, which are essentially a list of data transformation functions which end in loading that data into a project, or if the file does not require any transformation, they can specify that it be loaded directly into a project using the pre-defined project table mapping.

Zipped along with each source file, the client should provide a small manifest file, in JSON format (examples below), which includes the instructions as to how the file should be processed. 
Manifest file:

Manifest file contains the instructions to TimelinePI server what to do with the data files from the ZIP. The file is in JSON format. It could be created in any simple text editor or using variety of online JSON editors.

The manifest file has the nodes:

  • repositoryToken – the encrypted strings identifying the repository into which you load the data. You obtain this string from the Data Sources section of your Repository, once you check Scheduled:
  • projectToken – the encrypted strings identifying the project into which you load the data. You obtain this string from the Data Sources section of your Project, once you check Scheduled:
  • email – one or multiple email addresses, separated by semi-column. The reports about successful or failed uploads will be sent to these emails. We also always send the email to the project or repository owner.
  • commands – the ordered list of varios commands which will be executed once the upload package is received and unzipped. A command has an action and the optional arguments.
    • Action “upload”, if the repositoryToken is defined above. Parses the file and puts the data into the table. Arguments:
      • “file” – name of the file from the zip
      • “table” – name of repository table into which the data from the file will be placed. If the table doesn’t exist, it will be created.
    • Action “clone”. Copies a repository table and all data in it. Arguments:
      • “src” – name of the table to be copied
      • “dst” – name of the table into which the data from the file will be copied. If the table doesn’t exist, it will be created.
    • Action “todo-list”. Executes ToDo list from the repository. Arguments:
      • “table” – name of the table on which the list will be executed
      • “list” – name of ToDo list.
    • Action “upload”, if the projectToken is defined above. Parses the file and puts the data into the existing project using the mapping defined for this project. Arguments:
      • “file” – name of the file from the zip
      • “clearProject” – if true, old data from the project will be deleted before new data is loaded.

Examples of manifest

The following manifest file takes file myfile.csv from the ZIP, parses it into the repository table mytable, then copies the table into yourtable, the applies ToDo list mylist to it. Report will be sent to repository owner and to myname@company.com and to yourname@company.com.

{

“repositoryToken”: “k2QZiJkZuH … 8v6f8BpQEdekqjgqNxBw-E0AZUz2kdVA”,

“email”: [“myname@company.com“, “yourname@company.com“],

“commands”: [

{“action”: “upload”, “file”: “myfile.csv”, “table”: “mytable”},

{“action”: “clone”, “src”: “mytable”, “dst”: “yourtable”},

{“action”: “todo-list”, “table”: “yourtable”, “list”: “mylist”}

]

}

Next example parses the file myfile.csv and loads it into a project identified by the token. Old data from the project is deleted.

{

“projectToken”: “XdX3Cc8bW03Yvp … P7nsPmx_eD9MOSBfA”,
“commands”: [

{“action”: “upload”, “file”: “myfile.csv”, “clearProject”: true}

]

}

Setup

In order to use SFTP channel, you need:

  • Contact TimelinePI support at support@timelinepi.com and obtain the credentials for SFTP server.
  • Create manifest.json file as described above
  • Zip together the manifest and your data files
  • Upload the zip into SFTP server.

You should receive the email report once the upload and data processing is completed. You may also see the progress in History section of your repository or project, depending on what token is specified in manifest.

Example Video Demonstration