Loading Multiple Excel files to QlikView

[Anonymous]

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.

[settu_periasamy]

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]

Hi,

Check this link.

Load all Excel files and all sheets in a folder

Regards,

jagan.

[Anonymous]

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 · Accepted solution]

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]

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]

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]

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]

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

exit script;