Introduction
There are several types of connection strings that can be used. This guide describes the different connection string types.
Files
Files can be loaded into a table by setting a driver parameter of the connection string to {mvfile}
.
driver={mvfile}
The following types of files can be loaded:
- Excel Files
.xls
and.xlsx
- Text Files
.csv
,.tsv
and.txt
Additional parameters can be specified to load files.
Below is an overview of the generic parameters that are available or loading files.
Parameter | Description |
---|---|
filename | This can either be a relative path to a file on the server, or an HTTP URL. |
ext | By default the filename extension is used to determine the file type, but this can be overridden. |
user | The username for the remote file when using an HTTP URL. |
pass | The password for the remote file when using an HTTP URL. |
Excel files
Below is an overview of the additional parameters that can be used for loading Excel files.
Parameter | Description |
---|---|
sheet | The sheet or named range to import. For sheet names a '$' character should be appended at the end. |
firstcolumn | The first column to start from. The default setting is |
firstrow | The first row to start from. The default setting is |
The table below contains example connection strings for loading Excel files.
Connection string | Loads ... |
---|---|
| the first sheet of an Excel file |
| the sheet named 'MySheet' |
Text files
Below is an overview of the additional parameters that can be used for loading Text files.
Parameter | Description |
---|---|
hasheader | Whether the first row of the file is a header row. The default setting is |
delim | The delimiting character. The default setting is |
quote | The character used to quote values. The default setting is |
escape | The character used to escape characters. The default setting is |
encoding | The encoding used in the file, e.g., UTF8, or LATIN1. The default setting is |
The table below contains example connection strings for loading text files.
Connection string | Loads a text file ... |
---|---|
| with comma (,) separators and " quotes |
| with pipe (|) separators and " quotes |
| with tab separators and ' quotes |
| with semicolon separators and no quotes |
| with comma (,) separators, " quotes and \ escape characters |
| from a web server |
| from a web server with basic access authentication |
ODBC
ODBC is used to connect to external data sources, usually databases. You can either do this using the operating system, such as the ODBC data sources manager within Microsoft Windows, or directly supply the connection string in the correct format.
It is possible that the ODBC driver needs a query to execute. The query that needs to be executed on the database can be supplied as a string in the Query field.
When testing an ODBC connection make sure that the Query field is filled in with a valid query.
Important
All databases that have a 64-bit version can be accessed using an ODBC connection. The required 64-bit ODBC driver must be installed on the UiPath Process Mining server, and the server must be able to access that specific system.
Below is an overview of the parameters that can be used for ODBC connections.
Parameter | Description |
---|---|
driver | The ODBC driver to use for this connection. Not needed when using the |
dsn | The ODBC DSN to use for this connection. Not needed when using the Note: When setting up a DSN to use in the connection string, make sure it is a 64 bit System DSN. |
user | Username to be used to connect to the external datasource. |
pass | Password to be used to connect to the external datasource. |
other | Any other parameters are passed as specified to the ODBC driver. |
The table below contains example connection strings for ODBC connections.
Connection string | Connects ... |
---|---|
'Driver={SQL Server Native Client 11.0};Server=SERVERNAME;Database=MyDatabase;' | to a SQL Server database |
'Driver={Oracle in OraClient12Home1};Dbq=MyTSN;' | to an Oracle database |
'DSN=MyDSN; User=MyUser; Pass=MyPass;' | using a configured DSN with a username and password |
Both the Connection string and the Query field are expressions. For example, a connection string table to an imaginary database could be created.
See illustration below.
Note
Data within the table can only be used when the table has been loaded. In this example, the Globals table is needed to use data from other tables.
Tips and tricks
For connection string formats of other systems, consult the manufacturer of that system, or an online resource such as https://www.connectionstrings.com/.
If the user with access to the database is a Windows user, make sure that this Windows user:
- is a service account and not an actual user.
- has Read/Write access on the Process Mining installation folder.
- is the user who runs the scripts (e.g. in the scheduled tasks) for data load.
- is used to run the Application pool in IIS.
SAP HANA
You can use an ODBC connection to connect to SAP HANA.
Below is an example connection string for SAP HANA.
'Driver={HDBODBC};User=MyUser;Pass=MyPass;ServerNode=MyServer:30015;DatabaseName=TDB1'
Note
Instead of connecting to an SAP database, it is recommended to use the SAP connection string type to connect to the SAP application server.
Generic scripts
You can use a connection string to run a script to load data from datasources.
Important
The script handler you want to run must be installed on the UiPath Process Mining server.
Files can be loaded into a table by setting the driver parameter of the connection string to {mvscript}
and the script parameter to "generic":
driver={mvscript}; script=generic
Note
You must specify the script parameters in the Query field using URL encoding.
Below is an overview of the parameters that can be specified in the Query field.
Parameter | Description | Mandatory/Optional |
---|---|---|
scriptText | The text of the script. | Mandatory |
scriptFile | Filename from which to load the script. This can be a relative path to a file on the server. When the file is on the server, the file should be placed in the Development data or Server data tab, and the Table scope of the table must be set accordingly. | Mandatory |
inputData | A semicolon separated text with column headers that contains the input for the script. Note: The | Mandatory |
scriptType | The name of the handle to use as specified in the Note: If | Optonal |
args | Customize the arguments to pass to the script. There are two special arguments; | Optional |
debugPath | An absolute path to a folder. If this parameter is specified, UiPath Process Mining will output the input file (as given to the external process), and the external process's output (as read from the external process standard output) in this folder for debugging purposes. | Optional |
Below is an example of a simple query.
+'&scriptFile=' + urlencode("script.py")
+'&inputData=' + urlencode("a;b;c"+#10+"1;2;3"+#10+"4;5;6")
Below is an example of a more advanced query.
+'&scriptText=' + urlencode(/* your script as text */)
+'&scriptType=' + urlencode("HandlerName")
+'&inputData=' + urlencode(csvtable(
"Case_ID", jsontolist(InputData_Case_ID)
"Amount" , jsontolist(InputData_Amount)
))
+'&args=' + urlencode(listtojson(["<SCRIPT>", "--filename", "<INPUT>"]))
See also: Use Generic Script Datasources
SAP
SAP connection strings can be used to connect to an SAP application server, for example, such as SAP R3, SAP ERP, SAP ECC 5.0, SAP ECC 6.0, S/4HANA.
Important
The
SAP NWRFC
library must be installed on the UiPath Process Mining server to use a SAP datasource from the connection string.
SAP datasources can be loaded by setting the driver parameter of the connection string to {SAP}
.
driver={SAP}
Below is an overview of the parameters that can be used for SAP datasources.
Parameter | Description | Mandatory/Optional |
---|---|---|
Host | The hostname or IP address of the SAP application server. | Mandatory |
SystemNumber | The two-digit number between 00 and 99 that identifies the designated instance. | Mandatory |
FunctionModule | When setting up a native SAP extraction the SAP Overrides the default | Optional |
Username | The username of the account that is being used to log in to the SAP instance. | Mandatory |
pass | The password of the user above. Can be used in combination with the credential store, use | Mandatory |
client | The client that is being used. | Mandatory |
Language | The language that is used when logging in. | Mandatory |
Below is an overview of the parameters that can be specified in the Query field.
Parameter | Description | Mandatory/Optional |
---|---|---|
table | The SAP tablename from which the data must be loaded. For example | Mandatory |
fields | An array of fields that will be retrieved from the SAP table specified in the Note: If the | Mandatory |
where | A | Optional |
limit | An integer number that specifies a fixed number of records to be retrieved from the table. | Optional |
Note
Every field in SAP has a length. For performance reasons, make sure that the sum of all retrieved field lengths for a table does not exceed 512.
Below is an example of an SAP Connection string.
'Driver={SAP};Host=10.10.10.10;SystemNumber=00;Username=MyUser;pass=secretPassword;client=100;Language=EN'
Below are some example Queries.
Query | Retrieves... |
---|---|
| all fields of all records of table |
| the |
| all fields from |
Updated 9 months ago