This details the steps required to use a Microsoft Excel (XLS) file as a User Directory in the User Directory Connector (UDC).
Environment:
Any version of Qlik Sense
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.
The program can't start because VCRUNTIME140.dll is missing from your computer.
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 creation verified.
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 synchronizing all users for ...
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
ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed??ERROR [01S00] [Microsoft][ODBC Microsoft Access Driver]Invalid connection string attribute
==> 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.