Follow

SFTP: Files and folder structure / Creating your input file

 

Files and folders

SFTP server folders

  • config – needs to contain configuration information (more info here)
  • input – this is where you will place your data files
  • results – this is where the log and the output files are placed

File names

The name of the input file determines the type of upload as per the following formats:

  • Contributor (user) import = jostleContributors<>.csv 
  • Contributor (user) import by external id = jostleContributorsById<>.csv 
  • Contributor (user) deletion = deleteContributors<>.csv 
  • Contributor (user) deletion by external id = deleteContributorsById<>.csv 
  • Teams import (main org) = uploadTeams<>.csv
  • Chart import (not main org) = uploadChart_<category>_<chart>_<>.csv
  • Chart import (from backup/Chart edits) = txtChart_<category>_<chart>.txt
  • Mapped chart import = mappedChart_<category>_<chart>.csv
  • Two part chart import = chartPart(1or2)_<category>_<chart>_<>.csv 
  • Contributor (user) photo import = jostlePhotos<>.zip
  • Contributor (user) export = downloadContributors<>places a copy of the current Contributor file in the output folder.
  • Flat team = flatTeam_<category>_<chart>.csv will generate a flat team from a simple list of usernames. 
  • Reports = downloadReport<>.<> will download the report requested in the file. This can be used to export the platform's Engagement metrics report, Login reports, and more (a complete list can be found in the article SFTP: Creating your input file (CSV), under the heading Downloads/Metrics Reports). 

A special utility is in place such that any file whose name contains with "mappings" or is exactly "setup.txt" will be copied from the input folder to the config folder.

NOTES

  • The ‘<>’ portion is optional (except for Chart imports). If this is included, then the suffix will be appended to the results files name. If it is not included then the results files will be appended with the date & time.
  • The actual file contents are identical to those used on the matching import pages in Jostle under the Administration settings (which also provides a quick way to test your files).
  • File names are case sensitive.
  • ById variants require that users in the org have been assign employee id values, but allow referencing users using an external key such as an employee id maintained in an HRIS.

 

Creating your CSV input file

(NOTE—in file names, "Contributor" refers to "user")

 

Types of input files

User import

You will need to create a CSV file containing a list of the users to import. A pre-formatted User Import template as well as information on all the included fields can be found here.

Also, a mappings.csv file may be used. If re-mapping is not required then no file is needed, otherwise:

  • A mappings.csv file containing 2 columns is placed in the config folder.
  • The import will do an exact match between the data in the first column with the headings in your file and will map them to the normal Jostle heading found in the second column.
  • For a teams upload it will attempt to exact match and then it will proceed to match remaining columns to the normal Jostle headings.
  • If you do have a mappings.csv file then there must be an entry in every column in your import that you wish to have processed. Extra rows in the mappings file will have no affect. 
  • If you are using the mappings.csv file template then we recommend that you do not edit any of the entries in the second column.

NOTE—for a user import, if a mappings file is used then only columns defined in the mappings file will be processed (so if you don't map the 4 required columns it will skip the lines in your input file). 

 

User deletion

You will need to have a CSV file containing a list of the users to delete.  It should have 1 column, with Username appearing at the top, and the list of Usernames (in email address format) following in the rows below.

When using the deleteContributorsById file the column should be EmployeeId.

 

Team import

You need to create a CSV file with 4 columns (username, supervisor username, role name, team name). Username and supervisor username are required. Use our pre-formatted reports-to template to get started. The Team import must be used to manage the "Main Org" chart.

 

Flat Team import

This requires a very simple CSV file. In the simplest form it only needs 1 column containing the usernames for the people in the team. That column can have a heading that is any one of "username", "user name", "workemail", "work email", or "member email" (all case insensitive). It is possible to have a second column with a heading value of  "rolename" or "role name", in which case this value is used to populate the roles names for each card in the chart, otherwise all role names are set to "Member". Any other columns in the csv file are ignored.

 

Chart import

Unlike the Team import, the Chart import allows the creation or updating of any Teams chart (It will not work correctly for the one chart designated as "Main Org"). Key points to keep in mind:

  • The file name is used to specify both the Category and the Chart name. The Category must already exist.
  • If a chart with the specified name exists then it will be replaced, otherwise a new Chart will be created in the specified Category.
  • At present in the Teams view, the Chart name is defined as the top most team in a Chart so when creating a new Chart it will appear using the team name specified in the file, not the name provided in the file name, thus it is easiest if these are kept the same. 

NOTE—if you wish to update an existing team then you must have the <chart> section of the file name an exact match to the Chart name in Teams (which means it must also be an exact match to the team name of the topmost team in the file.)

You need to create a CSV file with 4 columns (username, supervisor username, role name, team name). Username and supervisor username are required. Refer to the "Importing a hierarchy into a Chart" section of this article for more details and then use our pre-formatted reports-to template.

Also, a teams_mappings.csv file may be used, which will function similarly to the mappings.csv for user imports:

  • As with the mappings.csv, the teams_mappings.csv must be placed in the config folder.
  • teams_mappings.csv can be used for Chart imports only.
  • On the teams_mappings.csv, use the column on the left to enter your user values (the column on the right is for Jostle’s use).

 

Mapped chart import

The mappedChart variant allows either or both of the Username or Supervisor columns to contain a unique identifier which will be used to obtain the actual username from a separate employee_mappings.csv file that has been placed in the config folder.

The employee_mappings.csv file needs to contain 2 columns titled id and username, with the "id" column containing whatever unique identifier (e.g. employee number) you want to use in the mappedChart CSV file (any additional columns in the file will be ignored).

NOTES

  • You must have one row in the employee_mappings.csv file for each id you use in the mappedChart file.
  • If you are using a mapping for the username, the column in the mappedChart file needs to be titled "userid" instead of username
  • If you are mapping for the supervisor name then the title should be "supervisorId" instead of "supervisorName".

Two part chart import

This allows chart data to be uploaded from 2 separate sources, normally with the part2 being uploaded less frequently. When a file that matches chartPart2_<category>_<chart>_<>.csv is detected, then it is moved from the input folder to the hold folder. When a file matching chartPart1_<category>_<chart>_<>.csv is detected then the code looks for a file with and exact name match, other than the "1" being a "2", in the hold folder. If found then the data in the two files is combined and uploaded to the destigated chart. The order of the columns may be different between the files, but otherwise all rules that apply to Chart Import above apply.

 

User photos import

Assemble a zip file that contains a separate image for each user photo. The images can be in .jpg, .png or .gif format and can be at any reasonable resolution.

To map the images to users, the file name for each image needs to be either of the following:

  • User's Username.  (eg. bob.smith@abc.com.jpg)
  • User's first name and last name separated by space (eg. Bob Smith.jpg)

NOTE—some may have a 500 MB limit on the maximum file size that can be uploaded, so a zip file under 500 MB is recommended.

 

Downloads/Metrics Reports

When a file whose name starts with downloadContributors is detected in the input folder, the current Contributor file will be placed in the output folder and an email will be sent to the designated email address(es). The downloadContributors needs to have at least 1 character in the file. The system doesn't process empty files.

When a file whose name starts with downloadReports is detected, then the contents of the file is used to determine which report is being requested. The file content should be a simple piece of json content following this pattern:

{"type":<type>,"days":"int","encoding":"UTF-8"}

The value for <type> is required to identify the report, and should be one of the following:

  • "Logins" , "NotLoggedIn", "LastLoggedIn", or "InvitedNotLoggedIn" which align to with the similarly named reports available at Admin Settings> Analytics > View/Download Login Reports e.g.: {"type":"LastLoggedIn","days":"30","encoding":"UTF-8"}
  • "EngagementMetrics" which aligns with the report available at Admin Settings > Analytics > Download Platform Metrics
  • "PublishedNews" or "ArchivedNews" which correspond to the reports available in the Manage News tables. e.g.: {"type":"ArchivedNews"}
  • "Biographies" which corresponds to the zip file available at Admin Settings > User Profiles > View User Profile Completion > Download Biographies
  • "ProfileStatus" which corresponds to the csv report available at Admin Settings > User Profiles > View User Profile Completion > Download Report
  • "ActivityMetrics" which corresponds to the report available from the gear in Activity. This supports two optional parameters startDate and endDate (mmm dd yyyy format) that match the date picker in the UI. e.g.: {"type":"ActivityMetrics","startDate":"Jan 01 2019","endDate":"Jan 30 2019"}

Days is required for the Not Logged In Report and needs to be an integer >=1 (the UI allows 30, 60 or 90 to be selected), and is ignored for all other reports.

Encoding is optional. If set to "UTF-8" then it is the same as checking that box in the UI.

NOTE: To get the News metrics the Jostle account you have in the setup.txt file should have News Editor rights.

 

When will Jostle process the upload?

Each time a file is placed in the /input folder a program is initiated that will run the Jostle scripts that communicate the data to our servers. The output from the scripts will be placed in the /results folder, and the original file moved to the /processed folder when the processing completes. A log file (log_datetime.txt) that is used to contain the output from the job is placed in the /results folder. Output CSV files (like the ones that you would download in the UI) are saved in the /results folder, either post-pended with a date or the file suffix as described above. 

The time to process depends on the file size. We suggest waiting 10 minutes for large files prior to checking for the changes (you can gauge the uploading time by uploading a similar file in the UI). Do not modify or overwrite a file in the input folder while it is being processed.

Please note that the date-time stamps are all in UTC (GMT).

 

Attachments

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.