topic Re: Pulling multiple excel sheets into QV when all headings are the same in QlikView

Pulling multiple excel sheets into QV when all headings are the same

Tue, 15 Oct 2013 12:30:56 GMT

Hello all,

Please forgive me if this question has a simple answer, I couldn't find an exact answer after searching the forums.

Basically we keep various financial data in an excel spreadsheet for one of our major accounts, with each tab being that years data (2002, 2003 etc. up to 2013). In each tab the headings are the same.

I am looking to use Qlikview to manipulate that data and produce various reports, which I have had success with using just the most recent data. However, pulling the other sheets into the script editor is causing the table viewer to link every entry (as the headings are the same) making the program become very slow and occasionally crashing.

I have tried uploading them separately and renaming the headings but that doesn't allow me properly manipulate the data.

Is there an easy way to pull the data into the script editor/Qlikview from multiple sheets?

Thank you

Re: Pulling multiple excel sheets into QV when all headings are the same

rajni_batra — Tue, 15 Oct 2013 13:04:21 GMT

Try this,

You can changes minyr and mxyr according to ur requirement .

LET minyr= '2002';
LET maxyr=(Today());

FOR i = $(minyr) to $(maxyr)
SET errormode = 0;
LOAD A
FROM
C:\Users\qv2\Desktop\testsheet.xlsx
(ooxml, embedded labels, table is $(i));
TRACE i;
NEXT i;

Re: Pulling multiple excel sheets into QV when all headings are the same

Anonymous — Tue, 15 Oct 2013 16:34:08 GMT

If the headings of the Columns in each Tab of you Excel spreadsheet are identical, when you load each Tab (whether manually or recursively) the resulting tables should be "automatically concatenated" into a single fact table. If this (concatenation) is not happening, check the headings are "identical" (spaces, carriage returns, etc).

Re: Pulling multiple excel sheets into QV when all headings are the same

Gysbert_Wassenaar — Tue, 15 Oct 2013 17:18:13 GMT

As Nick Scott says the headings are very likely not identical or all the data would end up in one table. You can use the concatenate keyword to force loading the data into the same table. Together with some code from Matt Fryer to load all sheets from an excel file that would look like this:

ODBC CONNECT TO [Excel Files;DBQ=ExcelFile.xlsx];

XlsTables:
SQLTables;
DISCONNECT;

LET vRows = NoOfRows('XlsTables');
SET vConcatenate =;

FOR i = 0 TO $(vRows)-1

LET vSheetName = subfield(peek('TABLE_NAME', i,'XlsTables'),'$',1);
    
Table1:
$(vConcatenate)
LOAD *
FROM [ExcelFile.xlsx]
(ooxml, embedded labels, table is '$(vSheetName)');
Set vConcatenate = Concatenate(Table1);
NEXT i

DROP TABLE XlsTables;

Re: Pulling multiple excel sheets into QV when all headings are the same

Clever_Anjos — Tue, 15 Oct 2013 17:34:19 GMT

G Wassenaar, how do you format your code to show pretty formatted like above?

Re: Re: Pulling multiple excel sheets into QV when all headings are the same

Gysbert_Wassenaar — Tue, 15 Oct 2013 17:40:46 GMT

Click on the Use advanced editor link when you create/edit a post. Select the text you want formatted and then click on the blue >> icon (that's for insert) and then choose Syntax Highlighting and finally the kind of highlighting you want.

Re: Pulling multiple excel sheets into QV when all headings are the same

Wed, 16 Oct 2013 14:12:58 GMT

Thank you all for the responses. The data is showing up fine now.

Re: Pulling multiple excel sheets into QV when all headings are the same

Fri, 01 Nov 2013 19:19:34 GMT

Hi G Wassenaar,

I'm trying to use your code but when I put 'XlsTables' after the ODBC connection then the 'SQLTables' instruction turns from blue to gray and it doesn't work. Do you know why this happens and what can I do to fix it?

Thanks a lot.

This is how my code looks and the error it shows when I run it.

Re: Pulling multiple excel sheets into QV when all headings are the same

Fri, 01 Nov 2013 20:08:13 GMT

I am out of the office until 12/11/2013.

My emails will be read upon my return. If your enquiry is urgent please

forward your email to the following inboxes:

For all Marine Cargo queries please forward your emails to

regionalcargo.claims@uk.rsagroup.com.

For all other queries please forward your email to

daniel.shaw@uk.rsagroup.com.

Note: This is an automated response to your message "Re:

- Pulling multiple excel sheets into QV when all headings are the same"

sent on 01/11/2013 19:20:04.

This is the only notification you will receive while this person is away.

Please consider the environment - Think before you print

RSA -The UK's first carbon neutral insurer

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

Royal & Sun Alliance Insurance plc (No. 93792). Registered in England & Wales at St. Mark's Court, Chart Way, Horsham, West Sussex, RH12 1XL.

Authorised and Regulated by the Financial Services Authority. For your protection, telephone calls may be recorded and monitored. The information

in this e-mail is confidential and may be read, copied or used only by the intended recipients. If you have received it in error please contact the

sender immediately by returning the e-mail. Please delete the e-mail and do not disclose any of its contents to anyone. No responsibility is accepted

for loss or damage arising from viruses or changes made to this message after it was sent.

Re: Pulling multiple excel sheets into QV when all headings are the same

Gysbert_Wassenaar — Sat, 02 Nov 2013 06:27:02 GMT

You missed a 'v' in table is '$(vSheetName)'

Re: Pulling multiple excel sheets into QV when all headings are the same

Sat, 02 Nov 2013 08:05:23 GMT

I am out of the office until 12/11/2013.

My emails will be read upon my return. If your enquiry is urgent please

forward your email to the following inboxes:

For all Marine Cargo queries please forward your emails to

regionalcargo.claims@uk.rsagroup.com.

For all other queries please forward your email to

daniel.shaw@uk.rsagroup.com.

Note: This is an automated response to your message "Re:

- Pulling multiple excel sheets into QV when all headings are the same"

sent on 02/11/2013 6:28:04.

This is the only notification you will receive while this person is away.

Please consider the environment - Think before you print

RSA -The UK's first carbon neutral insurer

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

Royal & Sun Alliance Insurance plc (No. 93792). Registered in England & Wales at St. Mark's Court, Chart Way, Horsham, West Sussex, RH12 1XL.

Authorised and Regulated by the Financial Services Authority. For your protection, telephone calls may be recorded and monitored. The information

in this e-mail is confidential and may be read, copied or used only by the intended recipients. If you have received it in error please contact the

sender immediately by returning the e-mail. Please delete the e-mail and do not disclose any of its contents to anyone. No responsibility is accepted

for loss or damage arising from viruses or changes made to this message after it was sent.

Re: Pulling multiple excel sheets into QV when all headings are the same

preminqlik — Sat, 02 Nov 2013 09:42:04 GMT

tab:

load

filebasename() as filename,

from

c:/path/*;

keep your files in path folder in above eg...

suppose if you have to choose only some files examplefile names are starting with "Year-XXXX" then use below syntax

load

num(right(filebasename(),4) ) as Year,

from c:/Path/Year*;

hope this helps you

Regards

Premhas.

Re: Pulling multiple excel sheets into QV when all headings are the same

Sat, 02 Nov 2013 10:07:12 GMT

I am out of the office until 12/11/2013.

My emails will be read upon my return. If your enquiry is urgent please

forward your email to the following inboxes:

For all Marine Cargo queries please forward your emails to

regionalcargo.claims@uk.rsagroup.com.

For all other queries please forward your email to

daniel.shaw@uk.rsagroup.com.

Note: This is an automated response to your message "Re:

- Pulling multiple excel sheets into QV when all headings are the same"

sent on 02/11/2013 9:43:03.

This is the only notification you will receive while this person is away.

Please consider the environment - Think before you print

RSA -The UK's first carbon neutral insurer

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

Royal & Sun Alliance Insurance plc (No. 93792). Registered in England & Wales at St. Mark's Court, Chart Way, Horsham, West Sussex, RH12 1XL.

Authorised and Regulated by the Financial Services Authority. For your protection, telephone calls may be recorded and monitored. The information

in this e-mail is confidential and may be read, copied or used only by the intended recipients. If you have received it in error please contact the

sender immediately by returning the e-mail. Please delete the e-mail and do not disclose any of its contents to anyone. No responsibility is accepted

for loss or damage arising from viruses or changes made to this message after it was sent.