Loading Multiple Excel Files and Multiple Excel sheets

[#1]

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.

[datanibbler]

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

[Anonymous]

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

[#4]

@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

[#5]

@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)

[Gysbert_Wassenaar]

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

[#7]

@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??

[rwunderlich]

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

-Rob

[Anonymous]

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

[#10]

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.