Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Aug 24, 2020 7:49:11 AM
Jul 24, 2017 3:46:52 AM
This details the steps required to use a Microsoft Excel (XLS) file as a User Directory in the User Directory Connector (UDC).
Environment:
The required Microsoft Excel Driver may not be installed by default.
Step 1: Verify if the drivers are already installed on your server
Open the Windows Registry Editor and navigate to Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\
?
Step 2: If no, download the latest Microsoft package
This file can be directly sourced from the Microsoft Download site. The 64bit version is required.
Example: Microsoft Access Database Engine 2016 Redistributable
Step 3: Run the installer
If the installer fails with CVRUNTIME140.dll error, cancel the installation.
Try reinstalling the program to fix this problem.
Obtain a valid CVRUNTIME140.dll and place the file in C:\Windows\System32
A copy of this file is attached to this article.
Run the installer again.
Step 4: Refresh the registry editor and verify that the drivers are correctly installed
Step 5: Create and XLS file that contains your users
A sample XLS file is attached to this article.
- This file contains 2 sheets "MyUsers" and "Attributes" - they can be renamed to fit your naming convention.
- The first sheet contains 2 columns: "userid" and "name" Those names are important and mandatory. Do not change them, or the import will fail.
- The second sheet contains 3 columns "userid", "type" and "value"
Step 6: Create a new UDC (Select "Excel (via ODBC)")
- Navigate to your User Directory Connector in the Qlik Sense Management Console
- Create a new connection: Excel (via ODBC)
- Untick the checkbox "Sync user data for existing users"
- "User table Name" must match your first sheet ("[MyUsers$]" here)
- "Attribute table name" must match the second sheet ("[Attributes$]" here)
- Modify the connection string to point to your xls file "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\temp\SomeUsers.xls"
Step 7: Validate and check in the logs
The below should be logged:
Connection open verified.
Tables verified.
The userid field of the [MyUsers$] table is verified successfully.
The name field of the [MyUsers$] table is verified successfully.
The userid field of the [Attributes$] table is verified successfully.
The type field of the [Attributes$] table is verified successfully.
The value field of the [Attributes$] table is verified successfully.
Step 8: Sync the UDC and check in the logs
Started syncing users
ODBC dataReader have been successfully created
Retrieved 3 entities from directory 'TEST' of type Repository.UserDirectoryConnectors.ODBC.OdbcExcel
Database done with 3 users and 0 groups in user directory (TEST, ...)
Ended saving users in repository
Finished synchronizing all users for ...
Step 9: In the QMC > users
Verify that the users have been added
The Attributes are reflected in the user's properties
Errors you may find in the C:\ProgramData\Qlik\Sense\Log\Repository\Trace\<SERVER>_UserManagement_Repository.txt
==> ODBC drivers are not correctly installed
==> Error in the connection string
ERROR [HY000] [Microsoft][ODBC Excel Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x19e0 Thread 0x8f04 DBC 0xcf6bffe8
==> Check the security permission for the XLS file ; the service account must be granted READ access
==> Error in the connection string
Exception when trying to access the name field of the [MyUsers$]
Exception when trying to access the userid field of the [MyUsers$]
ERROR [07002] [Microsoft][ODBC Excel Driver] Too few parameters. Expected 1.??Exception when trying to access the name field of the [MyUsers$] table:
==> Verify step 5 ; names "name" and "userid" of the columns is important, you cannot change them
The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.
