- Introduction to SAP Connector
- SAP Input
- Checking the Data in the SAP Connector
- Adding Process Specific Tags to the SAP Connector for AppOne
- Adding Process Specific Due Dates to the SAP Connector for AppOne
- Adding Automation Estimates to the SAP Connector for AppOne
- Adding Attributes to the SAP Connector for AppOne
- Adding Activities to the SAP Connector for AppOne
- Adding Entities to the SAP Connector for AppOne
- Connection String Types
- Table Scope
- Join Tables
- Global Tables
- Introduction to Table Items
- Display Format
- Maps
- Actions
- Introduction to SQL Connectors
- Setting up a SQL Connector
- CData Sync Extractions
- Running a SQL Connector
- Editing Transformations
- Releasing a SQL Connector
- Scheduling Data Extraction
- Structure of Transformations
- Using SQL Connectors for Released Apps
- Generating a Cache With Scripts
- Setting up a Local Test Environment
- Separate Development and Production Environments
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
,.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 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 |
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 ... |
---|---|
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.
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.
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'
'Driver={HDBODBC};User=MyUser;Pass=MyPass;ServerNode=MyServer:30015;DatabaseName=TDB1'
Generic Scripts
You can use a connection string to run a script to load data from datasources.
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
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 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; |
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")
+'&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>"]))
+'&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.
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
fields property is empty, all fields will be retrieved.
|
Mandatory |
where |
A |
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 |
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'
'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 |