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

[#1]

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

[rajni_batra]

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;

[Anonymous · Accepted solution]

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

[Gysbert_Wassenaar]

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;

[Clever_Anjos]

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

[Gysbert_Wassenaar]

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.

[#7]

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

[#8]

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.

[#9]

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.

[Gysbert_Wassenaar]

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