...
It's worthy at this point to note any datetime fields. Postgresql prefers to import datetime strings as YYYY-MM-DD HH24:MI:SS so some manipulation will be required to adjust these fields into the correct format. The following are two examples from one Brightly Confirm installation.
Street Light Faults Field Definitions
Field Name | Data Type | Example / Notes |
Asset Id | character varying | AB/13/E |
Central Asset Id | character varying | Unique ID in Confirm |
Job Number | integer | 173243 |
Job Entry Date | datetime (DD/MM/YYYY HH24:MI:SS) GMT | 19/06/2022 11:37:28 |
Site Name | character varying | Albert Road |
Locality Name | character varying | N4 |
Feature Location | character varying | S/O No 86A Florence Road |
Job Notes | character varying | General Street Lighting Issue - Lamp on 24/7 |
Priority Name | character varying | Street Lighting - 7 days |
Status Code | character varying | JS33 |
Status Name | character varying | Job Received by Contractor |
Asset Number | decimal | 200009.01 |
Feature Type Code | character varying | LSL |
Cms Activation Date | datetime (DD/MM/YYYY HH24:MI:SS) GMT | 19/06/2022 11:37:28 |
Feat Cent East | decimal | 530981.73 |
Feat Cent North | decimal | 187613.56 |
Longitude | character varying | 51.57218637 |
Latitude | character varying | -0.12345678 |
Url | character varying | |
Defect Type | character varying | Column - Day Burner |
Defect Code | character varying | LCDB |
wkb_geometry | well-known text geometry | POINT (-0.09102906 51.58654038) |
Feature Group Code | character varying | SLCO |
Feature Group Name | character varying | SL-Street Lighting Unit |
Feature Type Name | character varying | Street Light Column |
Bin Assets Field Definitions
Field Name | Definition | Example |
Feature Type Name | character varying | Dog Bin |
Site Code | character varying | P421054 |
Site Name | character varying | Albany Close |
Locality Name | character varying | N15 |
Ward Name | character varying | St Anns |
Asset Number | character varying | 7,001.00 |
Feature Location | character varying | by L/C AC805H |
Central Asset Id | character varying | H5400002729 |
Material | character varying | Plastic |
Manufacturer | character varying | Unknown |
Mounting Configuration | character varying | Floor mounted |
Asset Position On Site | character varying | Back of Path |
Surface Base Type | character varying | Block Paving |
Owner | character varying | Waste Management |
Asset Id | character varying | S1 |
Survey Date | datetime (DD/MM/YYYY HH24:MI:SS) GMT | 11/24/23 0:00 |
Feat Cent East | decimal | 532047.79 |
Feat Cent North | decimal | 189119.58 |
Latitude | character varying | 51.58547145 |
Longitude | character varying | -0.09542232 |
Feature Start Date | datetime (DD/MM/YYYY HH24:MI:SS) GMT | 6/13/24 17:34 |
Feature Type Code | character varying | GRIT (for icon and/or colour coding) |
url | character varying | |
Feature Group Name | character varying | HI-Grit/Salt Bins |
Feature Group Code | character varying | GRIT |
...
The following is a Python 3 script that is used to download the data from the SFTP server. A configuration file is defined for the server details and the local location of the downloaded file.
Create the python 3 virtual environment
Ensure Python 3 is installed.
| Paste code macro |
|---|
mkdir e:\isharedata\utilities\SFTPFileDownloader cd e:\isharedata\utilities\SFTPFileDownloader c:\python3\python.exe -m venv ./venv venv\scrpts\activate.bat python -m pip install --upgrade pip pip install pysftp |
Create the SFTP file downloader script
Copy the script below and save it as E:\iShareData\Utilities\SFTPFileDownloader\sftp_file_downloader.py
| Paste code macro | ||
|---|---|---|
| ||
import sys
import configparser
import pysftp
import warnings
if __name__ == "__main__":
warnings.filterwarnings("ignore")
config = configparser.ConfigParser()
config.read(sys.argv[1])
host = config['SFTP']['host']
user = config['SFTP']['user']
password = config['SFTP']['password']
sftp_file_location = config['SFTP']['file_location']
local_file_location = config['LOCAL']['file_location']
cnopts = pysftp.CnOpts()
cnopts.hostkeys = None
with pysftp.Connection(host, username=user, password=password, cnopts=cnopts) as sftp:
try:
sftp.get(sftp_file_location, local_file_location)
except:
print(f"Download failure: {sftp_file_location}") |
Create the SFTP file downloader config file
A config file will be required for each file on the SFTP server. Adjust the following example and save under E:\iShareData\Data\<project>\sftp_<project>.config
| Paste code macro |
|---|
[SFTP] host = sftp.some.server user = some_user password = some_password file_location = path\file.csv [LOCAL] file_location = E:\iShareData\Data\path\file.csv |
Create a Program Task in Studio
Create a program task with the following details. Adjust the location of the configuration file.
| Paste code macro |
|---|
Program: E:\iShareData\Utilities\SFTPFileDownloader\venv\Scripts\python.exe Additional Parameters: E:\iShareData\Utilities\SFTPFileDownloader\sftp_file_downloader.py E:\iShareData\Data\<project>\sftp_<project>.config |