Skip to main content
Skip table of contents

csvExport

This procedure allows the user to export the contents of a table in CSV format.

Parameters

ParameterDescription
sourceSchemaSource schema in the Data Virtuality Server; mandatory
sourceTableSource 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
targetFileName 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
delimiterIf 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

headerIf 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
orderByMay be used to sort the output
encodingEncoding 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

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.