SQL/CSV Node
- Phil Holtz
The SQL/CSV node is available in GlobalCapture only.
Only ONE SQL/CSV node should be present within the workflow. Using multiple SQL/CSV nodes will cause the workflow to error.
When a document moving through a workflow encounters the SQL/CSV node, a SQL query is run, and the returned data is output as a CSV file. The CSV file then branches into a separate path and the document can continue moving through the process. This can be useful if a CSV export of data based on the document moving through the process is needed.
Node Properties
Title
The title for you should be brief but descriptive about the nodes purpose.
Titles are useful when importing workflows or when viewing the process history of a workflow for easier understanding of the overall process.
Ex.
Description
Provide a synopsis of what this node is doing, make note of any important details. This is useful for providing additional information such as the purpose of the CSV export, the database being queried, etc. A good description is helpful when returning to modify the workflow in the future.
Ex.
Connection String
The Connection String defines the connection from GlobalCapture to the SQL Database.
For more information on formatting a connection string, please consult your Database Administrator.
String Delimiter
The String Delimiter is used to enclose a string to distinguish it from other data elements. This field is required and the default value is quotes (“).
Field Delimiter
The Field Delimiter is used to separate different data values. This field is required and the default value is a comma (,).
If our SQL Statement returns the Vendor Name, AP Document Type and Amount using quotes (“) as the String Delimiter and the comma (,) at the field delimiter, a returned line of data would look like this: “SoundCoast Music”, “Vendor Invoice”, 152.25
Include headers in CSV
When enabled, the first row of the CSV file will contain the field names for each column from SQL.
SQL Statement
If the SQL Statement is left blank or is invalid, the workflow will error.
Write your SQL Statement to return the desired data in the desired order for export. Inject data into your SQL Statement using S9 Notation. The SQL statement can only read and return data, it cannot be used to update the database.
Example of SQL statement with S9 Notation.
Database Type
Select the type of database from which you querying. Currently, the only option is SQL Server.
Data Validation
Workflow Connection
You must have paths for all 3 options to successfully publish your workflow.
The SQL/CSV node output options.
CSV - The path the generated CSV file will follow. This path does NOT keep the main document process field data.
What could you do if you wanted to associate the original process data with this document?
Original Process - The path the original document will follow IF the SQL Statement successfully returns data and generates a CSV file.
No Data - The path the original document will follow when the SQL Statement is unsuccessful in returning data.
Uses
What is a use case for this?