topic Re: Load data from multiple worksheets in multiple Excel workbooks in QlikView

Load data from multiple worksheets in multiple Excel workbooks

Mon, 23 Apr 2012 18:48:00 GMT

Have 50 workbooks - one for each customer - stored in "CustomerData" folder. Workbooks can have 1 to 50 worksheets (one for each day with customer data.) In each workbook, worksheets are named "1", "2", "3", etc. -- e.g. the first worksheet in workbook "A" is named "1" as is the first worksheet in workbook "B" -- worksheet names are not unique across workbooks. For all worksheets, worksheet columns are identical - some have more rows (more records.)

With one load statement, I can load all of the worksheets named "1" from all the workbooks in "CustomerData." What I want to do is load all the data from each worksheet in each workbook with one load statement if possible. If needed, each worksheet contains a value for "CustomerName" and a value for "Date" which when combined are unique to that worksheet.

Any ideas on how to write the load statement or is this not possible?

Thanks

Bill

Load data from multiple worksheets in multiple Excel workbooks

stevedark — Mon, 23 Apr 2012 22:27:03 GMT

Hi there - you will need to put a nested loop construct in place, firstly for each workbook in the folder and then for each sheet in the workbook.

For each workbook you will require code similar to this:

for each vFileName in FileList('.\*.xls')

And then within that loop, you will need to loop for each sheet. It is possible to get a list of all sheets in an Excel spreadsheet by connecting to it with an ODBC connection and calling the SQLTables function. But as you know the sheets are sequential from 1 that seems overboard. I would probably put an extra sheet 0 on each workbook that contained a cell with the number of sheets. The code would then be a bit like this:

Temp_Sheets:

LOAD

Sheets

FROM $(vFileName)

(biff, embedded labels, header is 1 lines, table is [0$]);

let vMaxSheet = peek('Sheets', -1, 'Temp_Sheets');

DROP TABLE Temp_Sheets;

for vSheetNo = 1 to $(vMaxSheet)

Data:

LOAD

[... Field List ...]

FROM $(vFileName)

(biff, embedded labels, header is 1 lines, table is [$(vSheetNo)$])

next

Obviously this does rely on the sheets being sequential from 1, otherwise the load will fall over. In this case explore the SQLTables route.

Hope that helps,

Steve

http://www.quickintelligence.co.uk/

Re: Load data from multiple worksheets in multiple Excel workbooks

Tue, 24 Apr 2012 04:34:23 GMT

Thanks Steve –this is helpful.

Can I get a rate quote from you for some upcoming project work. We have one developer and he is about maxed out. We need about 20 hours per week for 6 weeks, for an experienced developer – primarily load scripts and user interface for a server based QV app.

Bill

Re: Load data from multiple worksheets in multiple Excel workbooks

stevedark — Tue, 24 Apr 2012 06:29:17 GMT

Hi Bill,

Certainly, please send your contact details via my website at http://www.quickintelligence.co.uk/contact/

- Steve

Load data from multiple worksheets in multiple Excel workbooks

Thu, 26 Apr 2012 14:42:54 GMT

Steve - several questions

1. Will this work if the worksheerasa are named Daily1, Daily2, Daily2A, etc.?. Does this added asaheet need to be the first worksheet in the workbook?

2. What sheet name do I give the extra worksheet I add to each workbook? Can I name it "Sheet0"

3. For this added worksheet, in which cell do I put the count of worksheets in the workbook, and does this count include the added worksheet?

Thanks

Bill

Re: Load data from multiple worksheets in multiple Excel workbooks

stevedark — Thu, 26 Apr 2012 16:55:08 GMT

Hi Bill,

1. Yes, you can prefix each sheet name with Daily, the code will change to be:

table is [Daily$(vSheetNo)$])

2. You can call this sheet what you like, and it can appear where you like.

3. The count would be the number you would need to loop up to - so only the count of daily sheets. You will need to load the value into a temporary table and then extract the value with a Peek statement. This is not entirely straightforward - but you should be able to find how to do it on QlikCommunity.

If you come unstuck I may be able to post an example up here, if I get the chance.

Regards,

Steve

Re: Load data from multiple worksheets in multiple Excel workbooks

Thu, 26 Apr 2012 16:58:26 GMT

Thanks Steve for your quick response – this is really helpful.

Bill

Re: Load data from multiple worksheets in multiple Excel workbooks

Wed, 12 Sep 2012 19:13:02 GMT

Hi everybody,

I do have a similar kind of challenge ,kindly help me out ..

well the challenge is

I have a folder in which i have the source files along with the qv file

and the source files are all xlsx files

Things to remembe are:

-Source files are not limited in number and may have different names but are all xlsx files

- source files may have no of worksheets and are not limited in number and may vary from book to book

-Name of every sheet in the every excel file is different

-In every source excel file only the 1st sheet will have the header and all other following sheets in that file will have no headers

- The no of rows filled are not confined to anylimit and may vary from sheet to sheet in the file

-the headers are similar in all the source files provided in the 1st sheet of every source file

Result i need :

based on the above conditions ,If i reload the qv by placing all the source files in the folder ,i need to the qv to be reloaded completely with the data in the source files

for refernce the source files are as attached in the image

The code im trying in qlikview is as per the following

***********************************************************************************

for each vFileName in FileList('D:\Users\aju\Desktop\anand\*.xlsx');

for Each vSheet in $(vFileName)

Temp_Sheets:

LOAD

S_no,

Name,

[Roll no],

DOJ,

marks

FROM $(vFileName)

(biff, embedded labels, header is 1 lines, table is $(vSheet));

let vMaxSheet = peek('Sheets', -1, 'Temp_Sheets');

DROP TABLE Temp_Sheets;

for vSheetNo = 1 to $(vMaxSheet)

Data:

LOAD*

FROM $(vFileName)

(biff, embedded labels, header is 1 lines, table is $(vSheet));

****************************************************************************************************************

I request you help and resolve this challenge

Thanks ahead !!!

Kind Regards

Anand

Re: Load data from multiple worksheets in multiple Excel workbooks

stevedark — Wed, 12 Sep 2012 20:02:40 GMT

Hi Anand,

As far as I am aware there is no 'Sheets' collection that you can enumerate around in spreadsheets. As I mentioned above it is possible to get a list of sheets however by connecting to the Excel file with an ODBC connection.

Typically you would not connect to Excel with ODBC (as there is a native method), but it does give you a couple of extra commands. The connection string will be something like:

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

Once connected you should be able to issue the following command:

Temp_Tables:

sqltables;

This will give you a list of sheets that can be enumerated around:

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

let vSheetName = peek('TABLE_NAME', iSheet, 'Temp_Tables')

[... load data from sheet ...]

All of the above will need to sit in the loop that goes around the spreadsheets.

In order for the load * that you have in your code to work successfully you will need to ensure all sheets are in the exact same format - or some dodgy concatenation will occur. If the sheets are all similar, but not the same, you will need to force concatenate - which you can either do by working out if you are on the first time through the loop or not (and only concatenate if you are not) or load a dummy row into a table to concatenate onto ahead of the loop.

Only having the headers on the first sheet may cause issues, as I doubt the sqltables command will allow you to see the order of the tabs. In this case, I would suggest loading without embedded lablels on all sheets (which will mean you will need to alias A to s_no etc) and place a where statement on WHERE A <> 'sn_no'. This should then remove the headers from the first sheets when they are encountered.

Hopefully that gives you all the building bricks you need to put together a solution.

If you hit problems and you have some dummy data you can share in the format you are attempting to load, upload this in a zip file and I may be able to find time to build you a working example.

Regards,

Steve

www.quickintelligence.co.uk

Re: Load data from multiple worksheets in multiple Excel workbooks

Thu, 13 Sep 2012 04:07:51 GMT

Hi steve,

could you please help me to get a worked out excersie of this issue from the sample attached for convenience.-

the headers are same in the 1st sheet of each and every source file so can go ahead with a load* in my case.

find the attached source files as described in the imgs above in the following direct post

http://community.qlik.com/message/258417#258417

kind Regards

Anand

Re: Load data from multiple worksheets in multiple Excel workbooks

stevedark — Thu, 13 Sep 2012 21:04:21 GMT

Working example uploaded to http://community.qlik.com/message/258952 .

Regards,

Steve

Re: Load data from multiple worksheets in multiple Excel workbooks

rwunderlich — Thu, 13 Sep 2012 22:50:13 GMT

Note also that you can load sheets by position, rather than name.

"...table is @n"

where n is 1,2, etc.

-Rob

Re: Load data from multiple worksheets in multiple Excel workbooks

stevedark — Thu, 13 Sep 2012 23:37:42 GMT

Hi Rob - thanks for that tip - I was not aware of that syntax. Do you know if there a simple way of getting the number of tabs (without the OLEDB connection), as my document could be simplified if so.

Cheers,
Steve

Re: Load data from multiple worksheets in multiple Excel workbooks

rwunderlich — Fri, 14 Sep 2012 22:24:34 GMT

I don't know of any other way to get the sheet count. But what I seen done is to asume a max number of sheets like 50, SET ErrorMode=0 and then just try from @1 to @50. Crude, but simple.

-Rob

Re: Load data from multiple worksheets in multiple Excel workbooks

r3iuk — Fri, 27 Sep 2013 15:12:03 GMT

Should this method also work for .xlsx files as we seem to be having difficulties with files of this format?

Re: Re: Load data from multiple worksheets in multiple Excel workbooks

r3iuk — Tue, 29 Oct 2013 11:25:31 GMT

I developed the following workaround so that I can use a numeric sequence with .xlsx spreadsheets:

FOR vMonthNum = 1 to 12

Let vSheet = pick( vMonthNum, 'For Jan','For Feb','For Mar','For Apr','For May',

'For Jun','For Jul','For Aug','For Sep','For Oct','For Nov','For Dec');

Data:
LOAD

FROM [$(vSourceFile)]
(ooxml, embedded labels, table is [$(vSheet)]);
NEXT

Re: Load data from multiple worksheets in multiple Excel workbooks

stevedark — Tue, 29 Oct 2013 19:49:15 GMT

Hi Mike,

That's a good use of pick there to get the sheet names. I like my approach of having the sheets listed on a meta data sheet in the spreadsheet (as per my previous response) as new sheets can be added without amending QlikView code. This approach works fine as long as the sheet names are not going to change - and I doubt anyone is going to add a new month name any time soon!

Cheers,

Steve