ReadFile
Summary
The ReadFile table function allows developers to issue SELECT queries against a CSV file located on the Connect SFTP server file system. In other words, instead of SELECTing data from a database table, developers can query a CSV in a similar manner.
Syntax
ReadFile(FilePath=<path to file> [,FirstLineNames=<true|false>] [,Separator=<string>] [,BlankIsNull=<true|false>] [,Trim=<true|false>] [IgnoreBlankLines=<true|false>]) |
Parameters
| Parameter | Description |
|---|---|
| FilePath | (Required) Fully qualified SFTP directory path and file name for the source CSV file. Unix-style directory paths are used. |
| FirstLineNames | (Optional) Specifies whether the file’s first line that contains column header names. The default is true. |
| Separator | (Optional) The character string delimiter that separates columns within the source file. The default is ‘,’ (comma). |
| BlankIsNull | (Optional) Specifies whether an empty string value is to be returned as a null value or empty string. The default is false. |
| Trim | (Optional) Specifies whether all values are to be trimmed of leading and trailing spaces. The default is false. |
| IgnoreBlankLines | (Optional) Specifies whether completely blank lines are to be ignored. The default is false. |
Return Type
ReadFile returns a table result set. All of the column types returned are string. Use xSQL data conversion functions to cast values to different data types as needed.
Details
Connect is integrated with Xactly’s SFTP server, connectftp.xactlycorp.com. Connect’s file commands can read and write files and manipulate directory structures located on the SFTP file system. An SFTP account is not required to use these functions. However, one is required to access the file system via SFTP clients. Contact Xactly Customer Support to setup an SFTP account if your business does not already have one.
The table function reads the file and returns a result set. The source files must be located within the customer’s SFTP directory structured. Xactly hosts the SFTP server. Customers can upload source files to their SFTP account using an SFTP client. Valid SFTP login credentials are required to transfer files via SFTP. Connect users do not need SFTP credentials to use Connect’s file functions on files located on the SFTP server. The files are automatically available for use in Connect queries after the SFTP file transfer completes.
Examples
select * from ReadFile(FilePath='/training_lab/source_staging_order.csv');
select order_code, item_code, amount from ReadFile(FilePath='/training_lab/source_staging_order.csv', FirstLineNames=true, Separator=',', Trim=true, IgnoreBlankLines=true);
Related Commands