SQL Query to CSV File

<< Click to Display Table of Contents >>

Navigation:  Actions > CSV Data >

SQL Query to CSV File

This action is used to create a CSV file based on an SQL query from a database. RoboTask allows you to export data from SQL to CSV using other actions, but the algorithm would be more cumbersome. Using a single action is much simpler.

 

Dataset

CSVFromSQL1

 

Connection string

The database connection string. You can either manually write the connection string or generate it using the system dialog.

 

Overwrite connection string parameters

You can specify authentication parameters (username and password) directly in the connection string. If you want to override these parameters, enable this checkbox and fill in the "User ID" and "Password" fields.

 

Show password

Show the password in plain text for ease of editing, as the entered string may contain RoboTask variables.

 

SQL query

The SQL query text. Please note that the syntax may vary slightly depending on the type of database.

 

Query timeout

Timeout for executing an SQL query. The default is 60 seconds. However, sometimes a query requires more time to execute.

 

SQL Paramaters

CSVFromSQL2

 

Sometimes it's more convenient to use query parameters instead of generating a query using variables. For example, when the value of a variable contains quotes or other special characters. If you use variables (or macro substitution) when forming a SQL query, the presence of such characters can "break" the syntax of the query and you will get a run-time error.

Parameters SQL are designated in the inquiry by means of colon (:) before the parameter name.

For example:

Select * from employee where hiredate > :dt and salary >= :amount

:dt and :amount are parameters of the query.

Parameters can be several types:

String – any string

Integer – an integer number

Float – a real number or the number with a floating dot.

Boolean – a logical value True or False.

DateTime – a date and time. The value of such parameter must be defined in current system format of date and time (see the system regional settings). Notice that using of date-time can have its own characteristics for different databases. For example, it is better specify such parameters for the MySQL as a string parameter in the format YYYY-MM-DD or YYYY-MM-DD hh:nn:ss.

 

Fill from SQL

Fill the list of parameters from the current query. If a parameter is already in the list, this parameter will be without change. The function fills only the missing parameters in the list.

 

Add

Add a parameter manually. You will be proposed to type or select from the list the parameter name, select the parameter type and input an expression as the parameter value.

Using of variables in a parameter name and in its value is allowed. The RoboTask will substitute variable values instead of variables before performing the SQL.

 

Edit

Edit the properties of the selected parameter.

 

Delete

Delete the selected parameter from the list.

 

If you specify the parameter which is absent in the query, the action ignores this parameter when performing and writes the proper warning in the log of the task.

 

Target CSV file

CSVFromSQL3

 

File name

The name of the CSV file.

 

Encoding

Choose the desired file encoding. The following popular encodings are available for selection:

Default – Use the system encoding.

UTF-7

UTF-8

Unicode

ASCII

ANSI

 

Use field names

Enable this checkbox if you want to save column headers from the SQL query.

 

With memo fields

If you want to save MEMO fields in the CSV file, enable this checkbox. The MEMO field text will be saved as a comma-separated string. Please note that it doesn't make sense to save long texts in CSV format, as CSV format is not suitable for this purpose.

 

Limit field length to

Limit the field length to the specified size. The default is 100 characters. You can change this value or disable the limitation.

 

Use custom date-time format

This action writes the DateTime field values in the current system format. You can specify any format that suits your needs. The default is the universal format: yyyy-mm-dd hh:nn:ss.

 

CSV format

CSVFromSQL4

 

Specify the format of the CSV data: delimiter, quote character.

Note that if the "Strict delimiter" switch is not enabled, spaces are also considered delimiters. And all fields containing spaces must be enclosed in quotes.

 

Reset to standard format

Sets the standard parameters:

Delimiter: Comma (,)

Quotes: Double quotes (")

Non-strict delimiter (spaces are also considered delimiters)

 

Reset to Excel format

Sets the parameters used by MS Excel by default:

Delimiter: Semicolon (;)

Quotes: Double quotes (")

Strict delimiter.

 

note Related Topics

CSV Open Data

CSV Get Data Information

CSV Add Column

CSV Remove Column

CSV Sort by Column

CSV Move Column

CSV Read Cells

CSV Write Cells

CSV Merge Data

CSV Filter Data

CSV Data Loop

CSV Remove Duplicates

CSV Save Data

Read system events to CSV

Load process info to CSV

 

Note: This feature is available for Business license only