I am trying to load all sheets from an Excel workbook into Qlik Sense. I won't know how many sheets there are in advance. But, I do know that they will always be the same structure. So, unless there is a better solution, I need to create an ODBC connection to my Excel workbook (not a native Excel connection), such that I can use the SQLTables command in ODBC, which will give me the sheet count.
I have a Microsoft Excel Driver on my machine called 'Excel Files', which is under User DSN, not System DSN. (I don't have admin rights to my machine. So, I cannot move it to System DSN.) I have confirmed that this driver is operating as expected by successfully importing an Excel workbook into another Excel workbook via Data \ From Other Sources \ From Microsoft Query.
In Qlik Sense Desktop, I created an ODBC connection to my 'Excel Files' User DSN (with the same name). I had to specify a 32-bit connection, as Qlik threw an error about mismatch between Driver and Application.
The pertinent section of my load script is as follows:
vFile = 'C:\Users\MyName\Desktop\multiple_sheets_test.xlsx'; LIB CONNECT TO [Excel Files;DBQ=$(vFile)];
When I run this script, I get the error:
Connection 'Excel Files;DBQ=C:\Users\MyName\Desktop\multiple_sheets_test.xlsx' not found
I tried changing my LIB statement to ODBC, but Qlik threw the error:
CONNECTS other than LIB CONNECT are not available in this script mode
Also, if I try to use 'Select data' under my ODBC connection, nothing returns under the [Database] or [Owner] lists. I'm not sure if this is expected, or if this is another indicator of Qlik's inability to find my file.
If I simply run:
LIB CONNECT TO 'Excel Files';
the load script executes successfully (but, of course, no data loads). So, I suspect that something is wrong in my syntax of my CONNECT code. How can I resolve this?
instead of specifying the file path in the LIB CONNECT, try in the LOAD statement:
LIB CONNECT TO 'Excel Files';
LOAD A, `B`; SQL SELECT A, `B` FROM `C:\Users\MyName\Desktop\Excel.xlsx`.`Plan1$`;
I can't defer the filepath specification to the LOAD statement. It needs to be specified earlier, in the LIB CONNECT statement (I think). I first need to determine the number of sheets in the workbook via the SQLTables ODBC command before I loop over a LOAD statement.
However, your solution is helping me to diagnose a possible deeper problem. I am running the following code:
LIB CONNECT TO 'Excel Files'; Load * ; SQL SELECT * FROM 'C:\Users\MyName\Desktop\multiple_sheets_test.xlsx'.'Sheet1' ;
This returns the following error on the SQL statement:
Connector reply error: SQL##f - SqlState: 08003, ErrorCode: 0, ErrorMsg: [Microsoft][ODBC Driver Manager] Connection not open
How can I resolve this?
Strange, it worked for me.
But first, I went to the Windows ODBC Data Sources x64, chose the User DSN Excel Files, click the Config button and selected a workbook.
After that, I went to Qlik Sense, created a new ODBC using the User DSN, and was able to use the code as described earlier...
Ah, and in case you don't know it, you can use the SQLTables statement to get the Excel sheets.
Here is what I did to get this all working:
1) Configure an Excel data source (using the Excel Driver) in the ODBC Administrator to point to the desired Excel workbook. I had 3 issues with this. First, I don't have an admin rights to my machine. So, I had to configure this under User DSN, not System DSN. Second, I had to configure all of this in the 64-bit version of odbcad32.exe (in the SysWOW64 folder, not in the System32 folder). When I attempted to configure this in the 32-bit version, it threw the error "The setup routines for the Microsoft Excel Driver ... ODBC driver could not be found. Please reinstall the driver." I don't know if this is expected, but when I configured it under the 64-bit version, I can see the changes persist to the 32-bit version. Third, I didn't know that data source names are so restrictive. I initially used a fairly long name, but I had to shorten it.
2) Create an ODBC connection (User DSN) in Qlik Sense. I encountered an issue with this. Even though my OS is 64-bit, Qlik Sense is 64-bit, and I configured the data source in the 64-bit version of odbcad32.exe, I had to use a 32-bit connection. When I tried to use a 64-bit connection, Qlik threw the error "...the specified DSN contains an architecture mismatch between the Driver and Application."
3) All of the above lets me get the required metadata (really, the total number of sheets) from the Excel workbook. I used the following code in Qlik Sense for this:
LIB CONNECT TO 'Excel Files';
//create table 'sheets' to hold metadata about the Excel file sheets: SQLTables ;
//get the total number of sheets Let vNumberOfSheets = NoOfRows('sheets');
4) Now that I have the total number of sheets, I need to read the data in each of those sheets. I can do this in 2 ways.
In method #1, I am using an absolute path to the file. (This has no relation to the ODBC connection.) Because Qlik Sense only allows library connections to files, I had to modify the Settings.ini file (mine is located in C:\Users\MyName\Documents\Qlik\Sense) to use Legacy mode, not Standard mode. I modified this file by inserting the following line:
*Note: You need to be logged out of Qlik Sense in order to modify this file.
Then, I used the following code to read this file:
//load all sheets in the workbook For i = 0 to $(vNumberOfSheets) - 1 Let vSheetNameTemp = PurgeChar(Peek('TABLE_NAME', i, 'sheets'), chr(39)); //sheet names that contain blanks will be surrounded by single quotes; remove these (and any other) single quotes Let vSheetName = Left(vSheetNameTemp, Len(vSheetNameTemp) - 1); //the Excel ODBC driver perceives sheets in Excel workbooks as system tables; system tables get a $ sign appended to the end of them Data: Load * From C:\Users\MyName\Documents\Qlik\Sense\Apps\multiple_sheets_test.xlsx (ooxml, embedded labels, table is $(vSheetName)) ; Next i Drop Table sheets; //drop the metadata table
Note that, in this solution, the ODBC data source does not necessarily need to point to the same location as that used in the Load statement (though, it would make sense to use the same path). And, as others have pointed out, flipping Qlik to run in Legacy mode instead of Standard mode (which is what you're doing with the modifying of the Settings.ini file) means that Qlik administrators cannot control connections that are hard-coded outside of the QMC and in the apps themselves.
In method #2, to avoid the security issue above, I created a folder connection in Qlik to the same path, as follows:
//load all sheets in the workbook For i = 0 to $(vNumberOfSheets) - 1 Let vSheetNameTemp = PurgeChar(Peek('TABLE_NAME', i, 'sheets'), chr(39)); //sheet names that contain blanks will be surrounded by single quotes; remove these (and any other) single quotes Let vSheetName = Left(vSheetNameTemp, Len(vSheetNameTemp) - 1); //the Excel ODBC driver perceives sheets in Excel workbooks as system tables; system tables get a $ sign appended to the end of them; however, folder connections won't see the $ sign; so, need to trim it off Data: Load * From [lib://test/multiple_sheets_test.xlsx] (ooxml, embedded labels, table is $(vSheetName)) ; Next i Drop Table sheets; //drop the metadata table
However, with all of this being said, my Qlik admins won't allow either solution method. Issue #1 is the admin overhead that they will incur to configure the data sources to each Excel workbook in the ODBC Admin tool. (It is technically feasible to create multiple Excel data sources in the ODBC Admin tool, but becomes a nightmare to manage.) Issue #2 is the admin overhead that they will incur to set up new connections (ODBC and perhaps a folder connection) in the QMC. Issue #3 is specific to solution method #1. They will not allow file paths that they cannot control via the QMC. So, from all of this, my organization has now created a new rule: Qlik Sense is not allowed to point to Excel workbooks. Those workbooks must first be loaded into a SQL DB.
>>Second, I had to configure all of this in the 64-bit version of odbcad32.exe (in the SysWOW64 folder, not in the System32 folder).
In spite of what the names imply, on a 64 bit system, the version in SysWOW64 is the 32 bit version, and the version in System32 is the 64 bit version. Crazy but true.
>>When I tried to use a 64-bit connection, Qlik threw the error "...the specified DSN contains an architecture mismatch between the Driver and Application."
The connection has to match the architecture of the driver.
Having said that, I don't fully understand your problem. On an administrator controlled machine, the ODBC DSNs are usually controlled by the administrators. If you cannot get local admin rights, you will be unable to create System DSNs (which will be required for access by the QS service account). Once the DSN has been created, you can connect to it if you have the rights in QS to create connections.
Note that you use the ODBC connection to execute the SQLTables query, but then use a normal load to fetch the data from the Excel sheets:
LIB CONNECT TO 'TestExcel32'; TABLES: SQLTables; DATA: LOAD 0 as Dummy AutoGenerate 0; For i = 0 To NoOfRows('TABLES') Let vTable = Peek('TABLE_NAME', i, 'TABLES'); Concatenate(DATA) LOAD * FROM [lib://SourceFolder/ResourcePlan.xls] (biff, embedded labels, table is '$(vTable)'); Next
I'm in a scenario where I need to detect the number of sheets in my Excel workbook at runtime, not at dev time. Nobody at my organization has ever encountered this problem before. So, even though I'm not an admin of Qlik Sense at my organization, I had to create a prototype solution for my admins to review. They don't want the additional administrative overhead of creating ODBC DSNs at all, and they definitely don't want to lose control of the QMC. That's why we are now banning Excel sources in Qlik Sense.