csvExport
This procedure allows the user to export the contents of a table in CSV format.
Parameters
Parameter | Description |
---|---|
sourceSchema | Source schema in the Data Virtuality Server; mandatory |
sourceTable | Source table in the Data Virtuality Server; mandatory |
targetSchema | File data source in the DataVirtuality Server. The file will be stored in the directory assigned to the file data source; mandatory |
targetFile | Name of the file to store the exported data. If a file with the same name already exists, it will be overwritten. If omitted, name will be created: sourceSchema_sourceTable.csv |
delimiter | If not specified otherwise, comma(, ) is used as the delimiter by default |
quote | If not specified otherwise, double quotes("") are used as the quote character by default. All values will be quoted |
header | If specified, the result contains the header row as the first line - the header line will be present even if there are no rows in a group |
orderBy | May be used to sort the output |
encoding | Encoding for the created file. The system's default encoding is used by default |
Usage
SQL
CALL "UTILS.csvExport"(
"sourceSchema" => 'string_sourceSchema',
"sourceTable" => 'string_sourceTable',
"targetSchema" => 'string_targetSchema',
"targetFile" => 'string_targetFile',
"delimiter" => 'string_delimiter',
"quote" => 'string_quote',
"header" => boolean_header,
"orderBy" => 'string_orderBy',
"encoding" => 'string_encoding'
);;
Definition
SQL
CREATE VIRTUAL PROCEDURE csvExport
(
IN sourceSchema string NOT NULL OPTIONS (ANNOTATION 'The source schema in Data Virtuality Server.')
, IN sourceTable string NOT NULL OPTIONS (ANNOTATION 'The source table in Data Virtuality Server.')
, IN targetSchema string NOT NULL OPTIONS (ANNOTATION 'A file data source in Data Virtuality Server. The file will be stored in the directory assigned to the file data source.')
, IN targetFile string OPTIONS (ANNOTATION 'The name of the file to store the exported data. If a file with same name exists, it will be overwritten. If omitted, name will be created: sourceSchema_sourceTable.csv.')
, IN delimiter string OPTIONS (ANNOTATION 'When DELIMITER is not specified, by default comma(,) is used as delimiter.')
, IN quote string OPTIONS (ANNOTATION 'Double quotes(") is the default quote character. Use QUOTE to specify a different value. All values will be quoted.')
, IN header boolean OPTIONS (ANNOTATION 'If HEADER is specified, the result contains the header row as the first line - the header line will be present even if there are no rows in a group.')
, IN orderBy string OPTIONS (ANNOTATION 'An ORDER BY-clause may be used to sort the output.')
, IN encoding string OPTIONS (ANNOTATION 'Encoding for the created file. Default is the systems default encoding.')
)
OPTIONS (ANNOTATION 'Exports content of a table into a textfile.')
AS
BEGIN
DECLARE string variables.source_schema = LCASE(REPLACE(sourceSchema, '"', ''));
DECLARE string variables.source_table = LCASE(REPLACE(sourceTable, '"', ''));
DECLARE string variables.target_schema = LCASE(REPLACE(targetSchema, '"', ''));
DECLARE string variables.target_file = targetFile;
DECLARE boolean variables.header = COALESCE(header,FALSE);
DECLARE string variables.order_by = LCASE(REPLACE(orderBy, '"', ''));
DECLARE string variables.delimiter = delimiter;
DECLARE string variables.quote = quote;
DECLARE string variables.encoding = encoding;
DECLARE string variables.columns = '';
DECLARE string variables.export_command = '';
IF
(
(
SELECT a."Name"
FROM "SYS.Schemas" a
WHERE LCASE(a."Name") = variables.source_schema
)
IS NULL
)
BEGIN
ERROR 'Schema ' || variables.source_schema || ' does not exist';
END
IF
(
(
SELECT a."Name"
FROM "SYS.Schemas" a
WHERE LCASE(a."Name") = variables.target_schema
)
IS NULL
)
BEGIN
ERROR 'Schema ' || variables.target_schema || ' does not exist';
END
IF
(
(
SELECT a."Name"
FROM "SYS.Tables" a
WHERE
LCASE(a."Name") = variables.source_table
AND LCASE(a."schemaName") = variables.source_schema
)
IS NULL
)
BEGIN
ERROR 'Table ' || variables.source_table || ' does not exist';
END
IF
(
variables.target_file IS NULL
OR variables.target_file = ''
)
BEGIN
variables.target_file =
(
variables.source_schema
|| '_'
|| variables.source_table
|| '.csv'
)
;
END
LOOP ON
(
SELECT a."Name"
FROM "SYS"."Columns" a
WHERE
LCASE(a."tableName") = variables.source_table
AND LCASE(a."schemaName") = variables.source_schema
ORDER BY a.position
) AS cursor_column_names
BEGIN
variables.columns =
(
variables.columns
|| 'COALESCE("'
|| cursor_column_names."Name"
|| '",'''') AS "'
|| cursor_column_names."Name"
|| '",'
)
;
END
variables.columns = TRIM(TRAILING ',' FROM variables.columns);
variables.export_command =
'EXEC '
|| variables.target_schema
|| '.saveFile( '''
|| variables.target_file
|| ''', ( SELECT TEXTAGG('
|| variables.columns
|| CASE WHEN variables.delimiter IS NOT NULL THEN ' DELIMITER '''
|| variables.delimiter
|| '''' ELSE '' END
|| CASE WHEN variables.quote IS NOT NULL THEN ' QUOTE '''
|| variables.quote
|| '''' ELSE '' END
|| CASE WHEN variables.header = TRUE THEN ' HEADER ' ELSE '' END
|| CASE WHEN variables.encoding IS NOT NULL THEN ' ENCODING "'
|| variables.encoding
|| '"' ELSE '' END
|| CASE WHEN variables.order_by IS NOT NULL THEN ' ORDER BY '
|| variables.order_by ELSE '' END
|| ')'
|| ' FROM '
|| variables.source_schema
|| '.'
|| variables.source_table
|| ') )'
;
EXECUTE IMMEDIATE variables.export_command;
END
;
Example
SQL
CALL UTILS.csvExport(
"sourceSchema" => 'test_tables',
"sourceTable" => 'product',
"targetSchema" => 'file',
"targetFile" => 'productsExport.csv'
);;
See Also
CSV Export with Filter to see how to filter the result set before the actual CSV export
CSV Export with FTP Upload to learn how to use the FTP data source directly as the target schema