Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Loading Multiple Excel files to QlikView

I have the following QlikView application which loads a folder containing several Excel workbooks into QlikView, based on the Worksheet name in each file.

Unfortunately the code does not appear to be working; when I run the script, everything executes, however, no data is retrieved.

Please find both the QlikView app and a sample dataset attached; I would appreciate any assistance as I am currently stuck.

1 Solution

Accepted Solutions
settu_periasamy
Master III
Master III

Hi Micheal,

I forgot to copy and paste the Last Line.. In the end , need to add  Next vFile

Below the complete script

LET vFolder = 'C:\BU CODE\QlikView TEST\'; 

FOR EACH vFile IN FILELIST('$(vFolder)*.xlsx') 
ODBC CONNECT32 TO [Excel Files;DBQ=$(vFile)]

tables:
SQLtables; 
DISCONNECT
LET vSheetCount = NoOfRows('tables'); 
FOR iSheet = 0 to $(vSheetCount) -1 
LET vSheetName = replace(purgeChar(purgeChar(peek('TABLE_NAME', $(iSheet), 'tables'), chr(39)), chr(36)),'#','.'); 
IF (WILDMATCH(vSheetName,'*Q_Sheet7.1*')) THEN
[$(vSheetName)]: 
LOAD *,1 AS SampleCount
'$(vSheetName)'
as Sheet
'$(vFile)'
as FileName 
FROM [$(vFile)] 
(
ooxml, embedded labels, table is [$(vSheetName)]); 
ENDIF 
NEXT iSheet 
DROP TABLE tables; 
Next vFile

View solution in original post

8 Replies
settu_periasamy
Master III
Master III

Hi Michael,

Try with ODBC instead of OLEDB.

If there is a dot(.) in your sheet name, Qv changed to '#' (don't know the reason)..you should replace it.

Try the below code..

Re: Loading Multiple Excel Files and Multiple Excel sheets

LET vFolder = 'C:\BU CODE\QlikView TEST\';

FOR EACH vFile IN FILELIST('$(vFolder)*.xlsx')

ODBC CONNECT32 TO [Excel Files;DBQ=$(vFile)];

  tables:

  SQLtables;

  DISCONNECT;

  LET vSheetCount = NoOfRows('tables');

  FOR iSheet = 0 to $(vSheetCount) -1

  LET vSheetName = replace(purgeChar(purgeChar(peek('TABLE_NAME', $(iSheet), 'tables'), chr(39)), chr(36)),'#','.');

  IF (WILDMATCH(vSheetName,'*Q_Sheet7.1*')) THEN   

     [$(vSheetName)]:

     LOAD *,1 AS SampleCount,

         '$(vSheetName)' as Sheet,

  '$(vFile)' as FileName

     FROM [$(vFile)]

     (ooxml, embedded labels, table is [$(vSheetName)]);

  ENDIF

  NEXT iSheet

  DROP TABLE tables;

jagan
Luminary Alumni
Luminary Alumni

Hi,

Check this link.

Load all Excel files and all sheets in a folder

Regards,

jagan.

Anonymous
Not applicable
Author

Hi, while your suggestion worked. It only loaded the data in the first file (i.e., if there were multiple files in the folder, only the first file was loaded into QlikView). Do you know why this would be and/or how to have all the files within the folder loaded into QlikView?

settu_periasamy
Master III
Master III

Hi Micheal,

I forgot to copy and paste the Last Line.. In the end , need to add  Next vFile

Below the complete script

LET vFolder = 'C:\BU CODE\QlikView TEST\'; 

FOR EACH vFile IN FILELIST('$(vFolder)*.xlsx') 
ODBC CONNECT32 TO [Excel Files;DBQ=$(vFile)]

tables:
SQLtables; 
DISCONNECT
LET vSheetCount = NoOfRows('tables'); 
FOR iSheet = 0 to $(vSheetCount) -1 
LET vSheetName = replace(purgeChar(purgeChar(peek('TABLE_NAME', $(iSheet), 'tables'), chr(39)), chr(36)),'#','.'); 
IF (WILDMATCH(vSheetName,'*Q_Sheet7.1*')) THEN
[$(vSheetName)]: 
LOAD *,1 AS SampleCount
'$(vSheetName)'
as Sheet
'$(vFile)'
as FileName 
FROM [$(vFile)] 
(
ooxml, embedded labels, table is [$(vSheetName)]); 
ENDIF 
NEXT iSheet 
DROP TABLE tables; 
Next vFile

Anonymous
Not applicable
Author

if the same fields names in all the excels than we can try this one

first load the one file and above the load file you can write the code as like as follows

for each sheet name '$sheetname','$sheetname','$sheetname'....

load

column names

path

and in the path we can write the $(sheet name)

Anonymous
Not applicable
Author

Hi, I apologize for the delayed response. But this code worked perfectly! Thank you very much as it became tedious having to rename all the files to removed the '.'

Anonymous
Not applicable
Author

try this code

LET vFolder = 'C:\BU CODE\QlikView TEST\'; 

FOR EACH vFile IN FILELIST('$(vFolder)*.xlsx') 
ODBC CONNECT32 TO [Excel Files;DBQ=$(vFile)]

tables:
SQLtables; 
DISCONNECT
LET vSheetCount = NoOfRows('tables'); 
FOR iSheet = 0 to $(vSheetCount) -1 
LET vSheetName = replace(purgeChar(purgeChar(peek('TABLE_NAME', $(iSheet), 'tables'), chr(39)), chr(36)),'#','.'); 
IF (WILDMATCH(vSheetName,'*Q_Sheet7.1*')) THEN
[$(vSheetName)]: 
LOAD *,1 AS SampleCount
'$(vSheetName)'
as Sheet
'$(vFile)'
as FileName 
FROM [$(vFile)] 
(
ooxml, embedded labels, table is [$(vSheetName)]); 
ENDIF 
NEXT iSheet 
DROP TABLE tables; 
Next vFile

Aditya_Chitale
Specialist
Specialist

table:
LOAD
*
FROM [lib://mycon/*.xls]
(biff, embedded labels);

exit script;