Read and Write CSV Files

With xSQL commands, you can read, write, and manipulate data files located on SFTP servers hosted by Xactly. Connect users have access to the SFTP directories and files located on your company’s SFTP account via ReadFile, WriteFile, and other Connect functions. Direct access to the SFTP server using traditional SSH clients is controlled through a different credential likely held by your Incent compensation administrator.

Be aware, Connect and traditional DELTA processes use different SFTP servers. Additionally, Connect uses distinct SFTP credentials and directory paths for each pod (Implement, Sandbox, and Secure1-4). Xactly wanted to separate the files and directories used in development, sandbox, and production environments.

You can post raw data files for upload into Incent, but you might also want to place extract Incent data into CSV files, for example, on your SFTP account for later download. Our examples will give us the tools to do that.

For complete command references, syntax,  parameters, and more, see the xSQL and xCL Reference.

 

Create a Directory with MakeDir()

Before we start, it is sometimes good practice to create a variable to store your directory path:

set v_my_dir = '/tmp/';

 

Now we’ll create our directory, using the colon (:) syntax for variable substitution:

call MakeDir(Directory=:v_my_dir);

 

Create a CSV File with WriteFile()

WriteFile() creates a CSV file in your customer SFTP directory tree, from the results of an xSQL query.

Let’s create a file (periods.csv) in the directory we just created:

call WriteFile(FilePath= '{:v_my_dir}periods.csv', 
FirstLineNames=true, 
Input=(select period_id, name, start_date, end_date from xactly.xc_period), 
Separator='|', 
Quote='"');

 

Check a File or Directory with Exists()

You can verify a file or directory with Exists(). Use FilePath= for a file, and Directory= for a directory: 

call Exists(FilePath='{:v_my_dir}periods.csv');

 

Show Directory Contents with DirList()

Call DirList() to display the contents of a directory. Use Filter= to prune the returned results:

call DirList(Directory=:v_my_dir, Filter='*.csv');

In these results, we see that there is one CSV file in our directory, the one we just created.

 

Query a CSV File with ReadFile()

Use the ReadFile() table function to query a CSV file as a Connect table. Use SELECT as you would with a regular database table:

select * 
from ReadFile(FilePath='{:v_my_dir}periods.csv', 
Separator='|', 
Quote='"', 
FirstLineNames=true);

 

Remove Files & Directories

You can remove a file or directory with DeleteFile(). Use FilePath= for a file, and Directory= for a directory:

call DeleteFile(FilePath= '{:v_my_dir}periods.csv');

 

Finally, we’ll use Exists() to verify that our delete succeeded:

call Exists(FilePath='{:v_my_dir}periods.csv');