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
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
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
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
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.
Related Topics
Note: This feature is available for Business license only |