Connection string types

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, .tsvand .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. 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. When you use an HTTP URL, you can specify the user and pass parameters for authentication.
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 dollar sign character should be appended at the end. If not specified the first sheet is loaded.
firstcolumn	The first column to start from. The default setting is {autodetect}.
firstrow	The first row to start from. The default setting is {autodetect}.

The table below contains example connection strings for loading Excel files.

Connection string	Loads ...
'driver={mvfile}; filename=Data/Dataset.xlsx;'	the first sheet of an Excel file
'driver={mvfile}; filename=Data/Dataset.xlsx; sheet=MySheet$;'	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 {true}.
delim	The delimiting character. The default setting is {tab}.
quote	The character used to quote values. The default setting is {autodetect}.
escape	The character used to escape characters. The default setting is {none}.
encoding	The encoding used in the file, e.g., UTF8, or LATIN1. The default setting is {UTF8}.

The table below contains example connection strings for loading text files.

Connection string	Loads a text file ...
driver={mvfile}; filename=Textfile.txt; delim=,; quote=";'	with comma (,) separators and " quotes
'driver={mvfile}; filename=Textfile.txt; delim=|; quote=";'	with pipe (|) separators and " quotes
"driver={mvfile}; filename=Textfile.txt; delim=9; quote=';"	with tab separators and ' quotes
'driver={mvfile}; filename=Textfile.txt; delim=59; quote=none;'	with semicolon separators and no quotes
'driver={mvfile}; filename=Textfile.txt; delim=,; quote="; escape=\;'	with comma (,) separators, " quotes and \ escape characters
'driver={mvfile}; filename=http://www.example.com/files/example.txt;'	from a web server
'driver={mvfile}; filename=http://www.example.com/files/example.txt; User=Jane; mv-credential-Pass=example_com_credentials'	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.

Important: 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' parameter.
dsn	The ODBC DSN to use for this connection. Not needed when using the 'driver' parameter. 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 Note: only if scriptFile is not used.
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 Note: only if scriptText is not used.
inputData	A semicolon separated text with column headers that contains the input for the script. Note: The csvtable function can be used to generate data in the correct format.	Mandatory
scriptType	The name of the handle to use as specified in the GenericScriptHandlers setting in the Server Settings. Note: If scriptFile is specified, this will be automatically detected based on the file extension.	Optonal
args	Customize the arguments to pass to the script. This should be a JSON encoded list. There are two special arguments; '<SCRIPT>', and '<INPUT>'. Occurrences of '<SCRIPT>' are replaced with the path to a script file, and occurrences of '<INPUT>' are replaced with the path to the input file. This enables you to customize the call to the script handler with additional arguments. The default is ["<SCRIPT>", "<INPUT>"].	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 Z_XTRACT_IS_TABLE Function Module is required. If, for example, the Function Module is renamed in your SAP system, you can specify the correct name in the FunctionModule parameter in the SAP connection string.FunctionModule parameter. Overrides the default Z_XTRACT_IS_TABLE function module that is used when querying this table.	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 mv-credential-pass in this case.	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 EKPO.	Mandatory
fields	An array of fields that will be retrieved from the SAP table specified in the table property. Note: If the fields property is empty, all fields will be retrieved.	Mandatory
where	A where clause to retrieve a subset of a table.	Optional
limit	An integer number that specifies a fixed number of records to be retrieved from the table. Note: if limit is set to '0', there will be no limit.	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...
'table=' + urlencode("EKPO")	all fields of all records of table EKPO.
'table=' + urlencode("EKPO") + '&fields=' + urlencode(listtojson(["MANDT", "EBELN"]))	the MANDT and EBELN field for all records of the EKPO table.
'table=' + urlencode("EKPO") + '&where=' + urlencode("EBELN = 4500000001")	all fields from EKPO table where EBELN is equal to 4500000001.