Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Loading Multiple Excel Files and Multiple Excel sheets

Hi All,

I need help in loading Multiple Excel Files and Multiple Excel sheets.

I am developing an application on my local machine . I have a folder on my local machine(Not on server).

I have few excel files with different names. Each Excel file has different sheets(Each sheet name represents each table name) and with different names(No pattern).

Now I need to load all these excel files.

Can any one help me with proper code?

Please don't provide any links.. I have tried few codes but not working so please provide the code which can satisfy my requirement.

Appreciate for your response.

17 Replies
datanibbler
Esteemed Contributor

Re: Loading Multiple Excel Files and Multiple Excel sheets

Hi,

to load multiple Excel_files on your machine, you can use a FOR EACH loop to parse through the directory (you can set the working_directory using the DIRECTORY command so you don't have to repeat the file_path). You just need some commonality to provide a search_mask for your loop - but the ending .xls or .xlsx should be enough.

Loading multiple sheets is a bit more difficult if there is no recognizable pattern - then you cannot step through the sheets in a loop. If you know the exact sheet_names or you can load them from somewhere, you can use the variables in the LOAD_statements.

I guess you could also use the * in some way in your LOAD statements to just load every sheet that QlikView finds - the trouble with that is that if anything goes wrong, there are no details in the log to tell you which sheet made bahbah ...

HTH

Best regards,

DataNibbler

gabriel_kirst
Contributor III

Re: Loading Multiple Excel Files and Multiple Excel sheets

If all your sheets in all your files have the same fields, you can try this:

let vDataFolder = 'C:\Users\xxxxx\Documents\Qlikview\Community\Source\';

for each vFile in filelist('$(vDataFolder)*.xlsx')

  OLEDB CONNECT32 TO [Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=Excel Files;DBQ=$(vFile);DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"];

  Tables:

  sqltables;

  let vFileName = mid(vFile, index(vFile, '\', -1) + 1);

  for iSheet = 0 to NoOfRows('Tables') - 1

  Data:

  LOAD

      A,

      B,

      C

  FROM [$(vFile)]

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

  next

   DROP TABLE Tables;

next

Not applicable

Re: Loading Multiple Excel Files and Multiple Excel sheets

@gwassenaar: Yes I could have done what You have suggested. Good Idea. Will remember from next time.

@DataNibbler: Can you explain more with sample code?

@gabriel_kirst: As I have mentioned in the post each sheet is separate table. So the column names are different

Not applicable

Re: Loading Multiple Excel Files and Multiple Excel sheets

@gwassenaar:

I am using Excel data (with multiple sheets and multiple excels) for the first time:

I have used the below query just to load only one Excel (Which has multiple sheets):

FOR i = 0 to NoOfRows('tables')-1

  LET sheetName = purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39));

  Main:   

  LOAD *,

  '$(sheetName)' as Sheet 

  FROM workbook.xls (biff, embedded labels, table is [$(sheetName)]);

NEXT

DROP TABLE tables;

It is not working (shows blank).

How to modify my code so that it reads multiple sheets of a single excel(Each sheet name is different from other sheet names)

Re: Loading Multiple Excel Files and Multiple Excel sheets

The code you pasted is missing the creating of the table named tables. Normally this is created with the SQLTABLES command after making an ODBC connection to the excel file. See Gabriel Rocha's post above


talk is cheap, supply exceeds demand
Not applicable

Re: Loading Multiple Excel Files and Multiple Excel sheets

@gwassenaar : I have tried the code Gabriel Rocha's post.

let vDataFolder = 'C:\Users\Axd\Sample';

for each vFile in filelist('$(vDataFolder)*.xlsx')

  OLEDB CONNECT32 TO [Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=Excel Files;DBQ=$(vFile);DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"];

  Tables:

  sqltables;

  let vFileName = mid(vFile, index(vFile, '\', -1) + 1);

  for iSheet = 0 to NoOfRows('Tables') - 1

  Data:

  LOAD *

// '$(sheetName)' as Sheet

  FROM [$(vFile)]

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

  next

   DROP TABLE Tables;

next

This is giving me nothing(Excel is not loaded. Giving blank DataModel). What am I missing??

Re: Loading Multiple Excel Files and Multiple Excel sheets

Take a look at the Document log to see what files were actually processed.

-Rob

gabriel_kirst
Contributor III

Re: Loading Multiple Excel Files and Multiple Excel sheets

Isn't missing the last "\" in your variable  vDataFolder?

Should be let vDataFolder = 'C:\Users\Axd\Sample\';


By the way, to load your tables separated, you can try using QUALIFY before your load, and maybe setting your load name as something like "[$(vFile)]_[$(vSheetName)])".



Hope it helps.


Gabriel

Not applicable

Re: Loading Multiple Excel Files and Multiple Excel sheets

HI rwunderlich,

I have checke the logfile.

Following is the message

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'

let vDataFolder = 'C:\Users\Axd\Sample''

for each vFile in filelist('C:\Users\Axd\Sample*.xlsx')

Execution finished.



Community Browser