topic Re: Loading Multiple Excel files to QlikView in QlikView

Loading Multiple Excel files to QlikView

Anonymous — Fri, 26 Feb 2016 19:19:16 GMT

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.

Re: Loading Multiple Excel files to QlikView

settu_periasamy — Mon, 29 Feb 2016 02:53:11 GMT

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;

Re: Loading Multiple Excel files to QlikView

jagan — Mon, 29 Feb 2016 03:34:08 GMT

Hi,

Check this link.

Load all Excel files and all sheets in a folder

Regards,

jagan.

Re: Loading Multiple Excel files to QlikView

Anonymous — Mon, 29 Feb 2016 14:28:18 GMT

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?

Re: Loading Multiple Excel files to QlikView

settu_periasamy — Wed, 02 Mar 2016 02:13:21 GMT

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

Re: Loading Multiple Excel files to QlikView

Anonymous — Wed, 02 Mar 2016 05:26:37 GMT

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)

Re: Loading Multiple Excel files to QlikView

Anonymous — Thu, 24 Mar 2016 19:28:49 GMT

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 '.'

Re: Loading Multiple Excel files to QlikView

Anonymous — Thu, 31 Mar 2016 09:35:46 GMT

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

Re: Loading Multiple Excel files to QlikView

Aditya_Chitale — Tue, 23 Jul 2019 11:23:50 GMT

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

exit script;