Drive.SaveAs

Type	Read/write	Author	Availability
Direct provider	Write	Finbourne	Provided with LUSID

The Drive.SaveAs provider enables you to write a Luminesce query that writes data to one or more files of a particular type in Drive.

Note: The LUSID user running the query must have sufficient access control permissions to both use the provider and enumerate target folders in Drive. This should automatically be the case if you are the domain owner.

At the time of writing, you can create the following file types: CSV, SqLite, JSON, XML, Parquet, Excel, Word, PDF.

Drive.SaveAs accepts any number of input variables representing tables of data, and by default writes each one to a separate file. You can change this to combine multiple tables into a single file. Note the folder you write to must exist in Drive. If files with the same name already exist, they are overwritten.

See also: Drive.File

Basic usage

@data = select <filter-expression>
@x = use Drive.SaveAs with @data
<options>
enduse;
select * from @x

Options

Drive.SaveAs has options that enable you to refine a query.

Note: The --path option is mandatory. The --fileNames option is also mandatory and, if writing to multiple files, must be specified last in the list with file names placed on separate lines underneath; see below for examples. The -type option is mandatory if you want to create a file type other than CSV.

An option takes the form --<option>=<value>, for example --fileNames=trade-file. Note no spaces are allowed either side of the = operator. If an option takes a boolean value, then specifying that option (for example --ignoreOnZeroRows) sets it to True; omitting the option specifies False.

To see a help screen of available options, their data types, default values, and an explanation for each, run the following query using a suitable tool:

@x = use Drive.SaveAs
enduse;
select * from @x

Examples

In the following examples, the select * from @x syntax at the end generates a report of actions performed. The report contains an AdditionalInformation column with a GUID identifying the output file in Drive. Note you can distribute the file to users with suitable Drive permissions using a URL of the form https://<your-domain>.lusid.com/app/data-management/drive/<guid>?type=file.

Note: For more examples, try the Luminesce Github repo.

Example 1: Write a table of data to a CSV file

Csv is the default file format if the --type option is omitted. Note that specifying / for the --path option creates a file in the root Drive folder, which is guaranteed to exist.

@data = select distinct TableName from Sys.Field order by 1;
@x = use Drive.SaveAs with @data
--path=/
--fileNames=myfile
enduse;
select * from @x

Example 2: Write a table of data to a different file type

Supported file types other than Csv at the time of writing are Excel, Sqlite, Xml, Json, Parquet, Word, Pdf.

@data = select distinct TableName from Sys.Field order by 1;
@x = use Drive.SaveAs with @data
--path=/luminesce/testing
--type=Excel
--fileNames=myfile
enduse;
select * from @x

Example 3: Write multiple tables of data to multiple files

The number of file names written must match the number of input tables. Note that mydata1file and mydata2file are on separate lines underneath the --fileNames option, which is specified last.

@data1 = select distinct TableName from Sys.Field order by 1;
@data2 = select distinct TableName, FieldName from Sys.Field order by 2;
@x = use Drive.SaveAs with @data1, @data2
--path=/luminesce/testing
--fileNames
mydata1file
mydata2file
enduse;
select * from @x

Example 4: Write multiple tables of data to a single ZIP file containing two files

If --type is Csv (the default) or Json, combining multiple tables using the --combineToOne option creates a ZIP file containing separate files for each table.

@data1 = select distinct TableName from Sys.Field order by 1;
@data2 = select distinct TableName, FieldName from Sys.Field order by 2;
@x = use Drive.SaveAs with @data1, @data2
--path=/luminesce/testing
--combineToOne=mysinglezipfile
--fileNames
mydata1file
mydata2file
enduse;
select * from @x

Example 5: Write multiple tables of data to a single file

If --type is Excel or Sqlite, then each table is given a separate tab in a single spreadsheet, or separate tables in a single database file, respectively. Names of tabs/tables are controlled by the --fileNames option.

@data1 = select distinct TableName from Sys.Field order by 1;
@data2 = select distinct TableName, FieldName from Sys.Field order by 2;
@x = use Drive.SaveAs with @data1, @data2
--path=/luminesce/testing
--type=Excel
--combineToOne=mysinglespreadsheetfile
--fileNames
mydata1tab
mydata2tab
enduse;
select * from @x

Example 6: Generate an Excel file from an Excel template

If --type is Excel, the --templatePath option generates an Excel XSLX file from an Excel XLTX template file stored in Drive.

@data1 = select distinct TableName from Sys.Field order by 1;
@data2 = select distinct TableName, FieldName from Sys.Field order by 2;
@x = use Drive.SaveAs with @data1, @data2
--path=/luminesce/testing
--templatePath=/luminesce/testing/templates/mytemplate.xltx
--type=Excel
--combineToOne=mysinglespreadsheetfile
--fileNames
ws1:Table1
ws1:Table2
enduse;
select * from @x

Example 7: Generate a Word file from a Word template

You can upload a Word template as a DOTX file to Drive and then use Drive.SaveAs to generate a Word document as a DOCX file from the Word template. When creating your template, note:

• Any text fields you want your query to fill out should be written in the template as {{<your-field-name>}}.
• You must give any table you want your query to fill out a title via Table Properties. See how to do this.

In this example, let's imagine you upload a Word template containing the following fields to Drive:

You can now write a query specifying the data you want to populate each field with and write the completed Word document to Drive:

• Specify --type is Word and provide the --templatePath to the Word template in Drive.
• Use the --fileNames option to specify that the table @TABLE_DATA should be appended to the table with title TABLE_DATA in your Word template.
• Create a table of @TEXT_VALUES, on each line specifying the name of a field in the template and providing a value to populate that field with. For instance, you might request that the TITLE field is populated with “Providers”.
• Map @TEXT_VALUES to the specific --fileNames option Text:REPLACEMENTS. This tells Drive.SaveAs that @TEXT_VALUES contains lines of text to populate multiple fields with, rather than a single table to fill a single table field (which the provider expects by default).

@TABLE_DATA = select distinct TableName from Sys.Field order by 1 limit 20;

@TEXT_VALUES = values
    ('DATE', '16/02/2023'),
    ('TITLE', 'Providers'),
    ('DETAIL', 'This document contains a current list of the first 20 Luminesce providers.'),
    ('TABLE_CAPTION', 'A table of Luminesce providers');

@doc =
use Drive.SaveAs with @TABLE_DATA, @TEXT_VALUES
--templatePath=WordTemplate.dotx
--path=/luminesce/testing
--type=Word
--combineToOne=MyCompleteWordDoc.docx
--fileNames
TABLE_DATA
Text:Replacements
enduse;

select * from @doc;

The query outputs the following Word document in Drive, with each field populated:

Example 8: Generate a PDF file from a PDF template

You can upload a PDF template which contains form fields to Drive and then use Drive.SaveAs to fill out and store a completed form. In this example, let's imagine you upload a PDF template containing the following form fields to Drive (the PDF template in this example was created using DocFly, other options are available e.g. Adobe Acrobat):

You can now write a query specifying the data you want to populate each form field with and write the completed form to Drive:

• Specify --type is Pdf and provide the --templatePath to the PDF template in Drive.
• Use the --fileNames option to specify that the table @TABLE_DATA should be appended to the form field key TABLE_DATA in your PDF template.
• Create a table of @FIELD_VALUES, on each line specifying the key of a form field in the template and providing a value, font and font size to populate that form field with. For instance, you might request that the ADDRESS form field is populated with “123 Main Street” in the font Liberation Sans, font size 14.
• Map @FIELD_VALUES to the specific --fileNames option Text:REPLACEMENTS. This tells Drive.SaveAs that @FIELD_VALUES contains lines of text to populate multiple form fields with, rather than a single table to fill a single form field (which the provider expects by default). 

Note the following:

• The form field to which each table of data is appended in the PDF template is controlled by the --fileNames option. For the provider to correctly map your table of data to the intended form field, the order of your tables in use Drive.SaveAs with <your-tables> must match the order of your --fileNames.
• If a form field is not specified for a table of data, the table is appended to the end of the PDF.
• Font and font size default to Liberation Sans, font size 11 if set to Null or not specified.

@TABLE_DATA = select distinct TableName from Sys.Field order by 1 limit 20;
    
@FIELD_VALUES = values
    ('COMPANY', '123456789', 'Liberation Sans', 14),
    ('ADDRESSEE', 'Acme Co.', Null, 12),
    ('NAME', 'Jane Doe', Null, 12),
    ('ADDRESS', '123 Main Street', 'Liberation Sans', 14);

@PDF_DOCUMENT =
use Drive.SaveAs with @FIELD_VALUES, @TABLE_DATA
--templatePath=/luminesce/testing/pdfTemplateWithFormFields.pdf
--type=Pdf
--path=/PDF_Templates/
--combineToOne=myPdf.pdf
--fileNames
Text:REPLACEMENTS
TABLE_DATA
enduse;

select * from @PDF_DOCUMENT

The query outputs the following form in Drive, with each form field populated: