File Exchange
Overview
The File Exchange action can be used to create and download data to a CSV, XML or HTML file or to import CSV, XML and other Text files.
An Import - Select File option can be used to import a file manually.
The dropdown menu of the Actions button on the Home screen is populated with the File Exchange services. This allows you to select a file from your local drive and import it to a database on a cloud server.
The FTP fields are required if files are imported from or exported to an FTP folder.
FTP Fields
FTP Host - varchar(100) - If this field is null, the file is saved to a local drive using the FILENAME.
FTP Username - varchar(100)
FTP Password - varchar(100)
Export
When exporting files, the data type and the filename is created in the SQL. A File is exported for each record which is returned using the select SQL.
SQL
The SQL must only return one or more records including the following fields:
FILENAME - varchar(255) - This field must contain the full path and file name when saving to a local drive. When saving to FTP it must contain the file name only. When downloading to an FTP site, the Filename must contain the folder... eg. Folder/FileName.csv
FILEBODY - blob sub-type text - This field contains the CSV / XML / JSON data.
NOTE - blob sub-type text - This optional output parameter can be used to return an error or confirmation message when importing files.
Import CSV
The Import action will import each file which is found in the designated Folder where the filename contains the Tag. The first row of the import file must contain headers that are the same as the input parameters used in the SQL that will update the database. If the file is successfully imported, it is moved to an Imported folder, if errors are returned, it's moved to a Rejected folder.
Integrity checks include
Every parameter used in the Update SQL, needs to be in the file header, but they don't need to be in the same order. Error: Column not found: Column Name
Required fields
Type - Set to Import CSV
Folder - this can be a local folder or a folder on the FTP site. For use on the FTP site, it needs to include forward slashes eg. /foldername/
File Tag - This is a case-insensitive tag used to identify the files that will be imported. If the filename contains the File Tag, the file will be imported.
Connection String
SQL
Import XML
The Import action will import each file which is found in the designated Folder where the filename contains the Tag. The first row of the import file must contain a header that indicates the XML version (eg: <?xml version="1.0"?>) the rest of the file needs to be encapsulated by one opening and closing tag. If the file is successfully imported, it is moved to an Imported folder, if errors are returned, it's moved to a Rejected folder.
Note:
Each XML file may contain one record only.
The contents of the first matching tag will be passed to the procedure - nested tags are not currently supported.
Integrity checks include
Every parameter used in the Update SQL, needs to have a matching tag in the XML file. Error: Tag not found: Tag Name
Required fields
Type - Set to Import XML
Folder - this can be a local folder or a folder on the FTP site. For use on the FTP site, it needs to include forward slashes eg. /foldername/
File Tag - This is a case-insensitive tag used to identify the files that will be imported. If the filename contains the File Tag, the file will be imported.
Connection String
SQL
Import Text
The Import action will import each file which is found in the designated Folder where the filename contains the Tag. The file needs to contain text which will be imported to one field in the database. If the file is successfully imported, it is moved to an Imported folder, if errors are returned, it's moved to a Rejected folder.
Required fields
Type - Set to Import Text
Folder - this can be a local folder or a folder on the FTP site. For use on the FTP site, it needs to include forward slashes eg. /foldername/
File Tag - This is a case-insensitive tag used to identify the files that will be imported. If the filename contains the File Tag, the file will be imported.
Connection String
SQL
SQL
The SQL that is used to import the file must contain the following input parameters:
FILENAME- varchar(??) - The file name, excluding the path, will be passed to this parameter. Note: Set the varchar() to have sufficient characters to receive any file name.
FILEBODY - blob sub-type text - The body of the file will be assigned to this field as text.
NOTE - blob sub-type text - This optional Output parameter can be used to return an error or confirmation message when importing files.