Load data from multiple worksheets in multiple Excel workbooks

[#1]

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

[stevedark · Accepted solution]

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/

[#3]

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

[stevedark]

Hi Bill,

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

- Steve

[#5]

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

[stevedark]

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

[#7]

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

Bill

[#8]

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

next

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

I request you help and resolve this challenge

Thanks ahead !!!

Kind Regards

Anand

[stevedark]

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

next

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

[#10]

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