topic Re: Loading Multiple Excel Files and Multiple Excel sheets in QlikView

Loading Multiple Excel Files and Multiple Excel sheets

Wed, 04 Mar 2015 12:25:47 GMT

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.

Re: Loading Multiple Excel Files and Multiple Excel sheets

datanibbler — Wed, 04 Mar 2015 12:40:08 GMT

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

Re: Loading Multiple Excel Files and Multiple Excel sheets

Anonymous — Wed, 04 Mar 2015 12:57:55 GMT

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

FROM [$(vFile)]

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

DROP TABLE Tables;

Re: Loading Multiple Excel Files and Multiple Excel sheets

Wed, 04 Mar 2015 13:29:25 GMT

@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

Re: Loading Multiple Excel Files and Multiple Excel sheets

Wed, 04 Mar 2015 13:43:44 GMT

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

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

Gysbert_Wassenaar — Wed, 04 Mar 2015 13:57:56 GMT

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

Re: Loading Multiple Excel Files and Multiple Excel sheets

Wed, 04 Mar 2015 14:11:40 GMT

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

DROP TABLE Tables;

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

rwunderlich — Wed, 04 Mar 2015 14:27:56 GMT

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

-Rob

Re: Loading Multiple Excel Files and Multiple Excel sheets

Anonymous — Wed, 04 Mar 2015 14:37:40 GMT

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

Re: Loading Multiple Excel Files and Multiple Excel sheets

Wed, 04 Mar 2015 14:37:53 GMT

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.

Re: Loading Multiple Excel Files and Multiple Excel sheets

Wed, 04 Mar 2015 14:50:19 GMT

Hi gabriel_kirst ,

Everything is fine. I have made it proper

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

But still it is not working.

Can I get the Sample application with code ?? I have attached the sample Excel.

Re: Loading Multiple Excel Files and Multiple Excel sheets

Anonymous — Wed, 04 Mar 2015 14:53:17 GMT

Are your file a xls or a xlsx?

'Cause the for each is configured to read .xlsx files.

Regards,

Gabriel

Re: Loading Multiple Excel Files and Multiple Excel sheets

Wed, 04 Mar 2015 15:02:10 GMT

Hi gabriel_kirst ,

I had tried that as well: Look below for the script:

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

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

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)]);

DROP TABLE Tables;

I am getting Error which I have attached.:

So I have changed SQL Tables; to SQLTables;

Then I am getting BadZip File

Data:

Load *

From [C:\Users\Axd\Sample\Data.Xls];

Re: Loading Multiple Excel Files and Multiple Excel sheets

rwunderlich — Wed, 04 Mar 2015 16:37:03 GMT

According to the log it processed no files. It looks like you are missing the \ at the end of "Sample\".

-Rob

Re: Loading Multiple Excel Files and Multiple Excel sheets

Gysbert_Wassenaar — Wed, 04 Mar 2015 17:47:42 GMT

You can find a working example in the attached zip file.

Re: Loading Multiple Excel Files and Multiple Excel sheets

Thu, 05 Mar 2015 07:17:23 GMT

Hi gwassenaar

Is it possible to attach the files Individually?? I am not able to download the ZIP file(Have problem in downloading ZIP files)

Thanks in advance

Re: Loading Multiple Excel Files and Multiple Excel sheets

laujerry — Thu, 30 Apr 2015 02:07:55 GMT

hello, may i know how is this connection string generated?

i want to create a 64 bit connection and i have installed Microsoft ACE OLEDB 12.0

but which oledb provider should i choose?

thanks

Re: Loading Multiple Excel Files and Multiple Excel sheets

andreas_koehler — Wed, 11 May 2016 22:49:06 GMT

Gysbert,

this is super-versatile. Thanks!