Skip to content

SqlToCsv

Akram El Assas edited this page Sep 19, 2019 · 4 revisions

This task executes a collection of SQL scripts (select queries) and outputs the results in CSV files.

It supports Microsoft Sql Server, Microsoft Access, Oracle, MySql, SQLite, PostGreSql and Teradata.

Below the format of the CSV files:

columnName;columnName;columnName;...
columnValue;columnValue;columnValue;...
...

The CSV files generated are loaded by this task so that other tasks can select them through the selectFiles setting.

Below the settings of this task:

<Task id="$int" name="SqlToXml" description="$string" enabled="true|false">
  <!-- 
    The SQL script files loaded by the task having as id $taskId will be
    executed.
  -->
  <Setting name="selectFiles" value="$taskId" />
  <Setting name="selectFiles" value="$taskId" />
  <!-- You can add as many selecteFiles as you want.-->
  <!-- The database engine type. Possible options: sqlserver|access|oracle|mysql|sqlite|postgresql|teradata|odbc-->
  <Setting name="type" value="sqlserver|access|oracle|mysql|sqlite|postgresql|teradata|odbc" />
  <!--- The connection string.-->
  <Setting name="connectionString" value="$string" />
  <!-- Optional. It is possible to execute an SQL script through this option.-->
  <Setting name="sql" value="$string" />
  <!-- Optional and defaults to ';'. The separtor character in the CSV files.-->
  <Setting name="separator" value="$string" />
  <!-- Optional and defaults to empty string. The quote character for the values. example: ' or &quot;-->
  <Setting name="quote" value="$string" />
  <!-- Optional and defaults to true. True to include the headers, False otherwise.-->
  <Setting name="headers" value="$bool" />
  <!-- Optional and defaults to false. If set to true and the result set is empty, no file will be generated. If set to false, a file will be generated even if the result set is empty.-->
  <Setting name="doNotGenerateFilesIfEmpty" value="$bool" />
</Task>

Below a sample workflow using this task:

<Workflow xmlns="urn:wexflow-schema" id="67" name="Workflow_SqlToCsv" description="Workflow_SqlToCsv">
	<Settings>
		<Setting name="launchType" value="trigger" />
		<Setting name="enabled" value="true" />
	</Settings>
	<Tasks>
		<Task id="1" name="SqlToCsv" description="SQlite" enabled="true">
			<Setting name="type" value="sqlite" />
			<!-- https://www.connectionstrings.com/sqlite/ -->
			<Setting name="connectionString" value="Data Source=C:\WexflowTesting\sqlite\HelloWorld.db;Version=3" />
			<Setting name="sql" value="select * from Data;" />
			<Setting name="separator" value=";" />
		</Task>
		<Task id="2" name="FilesMover" description="Moving SqlToXml files from temp folder" enabled="true">
			<Setting name="selectFiles" value="1" />
			<Setting name="destFolder" value="C:\WexflowTesting\SqlToCsv\" />
		</Task>
	</Tasks>
</Workflow>

This workflow starts by executing a select SQL query and outputs the result in a CSV file. Finally, The CSV file is moved to C:\WexflowTesting\SqlToXml\.

Clone this wiki locally
You can’t perform that action at this time.