Qlik Sense Enterprise on Windows: Create and Sync a UDC with Microsoft Excel Driver

[Sonja_Bauernfeind]

This article is provided as is. For further customization or assistance, reach out to Qlik's Professional Services or engage in our Qlik Sense Administration and Configuration forum.

This details the steps required to use a Microsoft Excel (XLS) file as a User Directory in the User Directory Connector (UDC).

The instructions in this article are written for Microsoft Office 2016, which has reached end of life as of October 2025. As an alternative, use Microsoft 365 Access Runtime. See Download and install Microsoft 365 Access Runtime.

The required Microsoft Excel Driver may not be installed by default.  

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\
   
   

   

   
   
   If it is not installed, download the latest Microsoft package.
   
   This file can be directly sourced from the Microsoft Download site. The 64-bit version is required.
   
   Example: Microsoft Access Database Engine 2016 Redistributable
   
   
   
2. Run the installer
   
   
3. Refresh the registry editor and verify that the drivers are correctly installed
   
   
   
   
4. Create an XLS file that contains your users
   
   A sample XLS file is attached to this article:
   
   
   • This file contains two sheets, "MyUsers" and "Attributes"; they can be renamed to fit your naming convention
   • The first sheet contains two columns: "userid" and "name"; those names are important and mandatory.

     Do not change them, or the import will fail.

   • The second sheet contains three columns: "userid", "type", and "value"
     
     
5. Create a new UDC
   1. Navigate to your User Directory Connector in the Qlik Sense Management Console
   2. Create a new connection: Excel (via ODBC)
   3. Untick the checkbox Sync user data for existing users
   4. User table Name must match your first sheet ("[MyUsers$]" here)
   5. Attribute table name must match the second sheet ("[Attributes$]" here)
   6. Modify the connection string to point to your Excel file: DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\temp\SomeUsers.xls
      
      
      
      
6. Validate and check in the logs
   
   The following 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.  
   
   
7. Sync the UDC and check 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 ...
   
   
8. Verify that the users were added in the Qlik Sense Management Console
   
   
   
   
9. Verify the attributes are reflected in the user's properties
   
   
   

Troubleshooting

The following errors you may be logged in 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

This indicates the ODBC drivers are not correctly installed or an 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. May also indicate an 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 that the name and userid columns in the file are correctly named and have not been renamed. 

Environment 

• Any version of Qlik Sense

[lizetteber]

Muchas gracias me fue de mucha ayuda

[JohanW99]

Hi @Sonja_Bauernfeind. What about 2016 reaching End of support next month. Do you have any alternatives? We don't find any that supports easy access to user Excel sheets.

[Sonja_Bauernfeind]

Hello @JohanW99 

Let me check internally; however, I may need to direct you to a support case.

All the best,
Sonja

[Sonja_Bauernfeind]

Hello @JohanW99 

I've checked with my experts, and we've verified this with Microsoft 365 Access Runtime. The article was updated accordingly (though it does still document the old instructions, you'll now find a link to the alternative).

All the best,
Sonja 

[JohanW99]

Hi @Sonja_Bauernfeind 

From what we found out it still is 2016 you get if you check the version of the ACEODBC.DLL installed by Microsoft 365 Access Runtime. We don't have Office installed on the server. Searching more info on the internet it seems like Microsoft 365 Access Runtime could install a later version depending on the Office version on the server. So then we are dependent on installing Office on the server which we would like to avoid. Any comments?

BR Johan  

[Sonja_Bauernfeind]

Hello @JohanW99 

I'd recommend posting the query in the Qlik Sense Administration and Configuration forum to see how other users have worked with this setup.

All the best,
Sonja

[JohanW99]

Microsoft 365 Access Runtime does not expose itself to external apps like the ODBC System DSN so on a server without Office this will not work. Tested and not working.