DatabaseToCSV


The DatabaseToCSV Add-In generates one or more CSV files from an SQL query.

features                                                                                                                                                      
Add-in type Logic
Interfaces In: Database
Out: CSV-file
Transactions 1 per file
Events Per created file: <instance>.Output (Parameter = file)
1x at the end: <instance>.Done
   
parameter  
connectionString Database connection string
delimiter Separator in the CSV file (optional, default = semicolon)
encoding Coding of the CSV file (optional, default = “UTF-8”)
fileNamePattern File name of the CSV file
is ignored if the file name is returned by the SQL query. The placeholder {0} is replaced by a current time stamp. Example: File_{0: yyyyMMdd_hhmmss}.csv = File_20160523_115620.csv
Existing files will be overwritten.
fileSaveLocation Target directory of the CSV file
includeColumnName true: column names are written in the 1st line (optional, default = false)
query A SQL query that returns the data to be exported.
Only SELECT or EXEC are allowed. A CSV file is created for each result set
timeOut Timeout in seconds until the SQL command is canceled. (Optional, default = 30)
endpoint Name of the endpoint that is used in the transaction (optional, default = “”)

Application examples

IT often receives the order to periodically export data records from a database so that the departments can check or process the information.

For this purpose, an SQL query is defined which returns the data. A timer, which periodically calls the DatabaseToCSV add-in, and a mail sender, which informs the department about the newly created CSV files, complete the package.

Define file name in SQL query

If a 2nd result set is returned by the SQL query, the 1st column of the 1st line of the 2nd result set is interpreted as the file name. The “fileNamePattern” parameter is ignored in this case. Example of an SQL query:

SELECT * FROM DataTable;
SELECT 'DataTable_export.csv';
Create multiple CSV files

If the SQL query returns a multiple of 2 result sets, several CSV files are created. In this case, the file name cannot be specified using the “fileNamePattern” parameter. Example of an SQL query that creates two CSV files:

SELECT * FROM DataTable;
SELECT 'DataTable_export.csv'; 
SELECT * FROM StatisticData;
SELECT 'Statistic_export.csv';