Example for Qlik Sense to synchronize users and attributes from excel

Lisa_Sun — Tue, 20 Aug 2024 10:10:51 GMT

This article provides an example of how to retrieve user attributes from Excel.

For more information on the Qlik Sense User Directory Connector and its options for reading user attributes from different sources, see User directory connectors.

Synchronizing user data and attributes does not carry out authentication. It is intended to enable the use of additional attributes which may not otherwise be synchronized or to pre-emtively distribute licenses. Authentication must be handled by your choice of identity provider. In the Excel example, this would typically be Windows.

Environment:

Qlik Sense any version

ODBC example

Each data source has a different configuration and the following example (Excel) of adding an ODBC user directory connector.

ODBC example (Excel)

Do the following:

Verify that the Microsoft Excel Driver is installed.
Set up an ODBC source on the server.

You need to store the data in two separate sheets in the same excel file, for example, for example you can place it in this location: %ProgramData%\Qlik\Sense\temp.

The temp folder is not included in the default installation. You need to create the temp folder, if not already done by another QMC administrator.

SheetA contains the users and SheetB the user attributes.

Example:

SheetA

userid name

1
John

2
Bill

3
Tom
SheetB

userid type value

1 email jd@email.com

1 lastname Doe

2 email bg@email.com

2 lastname Gates

3 email th@email.com

3 lastname Hanks

Select User directory connectors on the QMC start page or from the Start drop-down menu to display the overview. Create a new user directory connector (ODBC) and edit the properties.

Identification

All fields are mandatory and must not be empty.

Property Description

Name	The name of the UDC configuration, defined from the QMC.
Type	The UDC type.

User sync settings

Property Description Default value

Sync user data for existing users

When selected, only the existing users are synchronized. An existing user is a user who has logged in to Qlik Sense and/or been previously synchronized from the configured directory service.
When not selected, all the users, defined by the properties for the UDC, are synchronized from the configured directory service.

The user attributes are only synced when a user logs in to the hub. Even if you delete the user in the QMC, the active session is still valid for the user that has been deleted. If the hub is only refreshed, the user is added to the database, but without any attributes.

Selected

Connection

Property Description Default value

User directory name	The name of the user directory. Must be unique, otherwise the connector will not be configured. The name must not contain spaces.	-
Users table name	The name of the table containing the users. Include the file extension in the table name, for example: [SheetA$].	-
Attributes table name	The name of the table containing the user attributes. Include the file extension in the table name, for example: [SheetB$].	-
Visible connection string	The visible part of the connection string that is used to connect to the data source.	-
Encrypted connection string	The encrypted part of the connection string that is used to connect to the data source. Typically, this string contains user name and password. The two connection strings are concatenated into a single connection string when making the connection to the database.	-
Synchronization timeout (seconds)	The timeout for reading data from the data source.	240

Example:

User table name: [SheetA$]

Attributes table name: [SheetB$]

Visible connections string: Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};Dbq=%ProgramData%\Qlik\Sense\temp

Click Apply to apply your changes.
Go to the User directory connectors overview and check if the user directory is displayed as Configured and Operational.
If the User directory name is not unique the connector will not be configured. If not operational, check the repository system log in: %ProgramData%\Qlik\Sense\Log\Repository\Trace.

You have added an ODBC data source and initial synchronization will be performed within five minutes (by default).

Re: Example for Qlik Sense to synchronize users and attributes from excel

RaviBasserah — Thu, 15 Aug 2024 15:06:56 GMT

What will be the password for User ?

Re: Example for Qlik Sense to synchronize users and attributes from excel

Sonja_Bauernfeind — Tue, 20 Aug 2024 10:11:22 GMT

Hello @RaviBasserah

userid	type	value
1	email	jd@email.com
1	lastname	Doe
2	email	bg@email.com
2	lastname	Gates
3	email	th@email.com
3	lastname	Hanks

userid	name
1	John
2	Bill
3	Tom

article Example for Qlik Sense to synchronize users and attributes from excel in Official Support Articles

Example for Qlik Sense to synchronize users and attributes from excel

Environment:

ODBC example

ODBC example (Excel)

Re: Example for Qlik Sense to synchronize users and attributes from excel

Re: Example for Qlik Sense to synchronize users and attributes from excel