topic Load all sheets in all Excel documents in QlikView

Load all sheets in all Excel documents

bismart — Tue, 12 Apr 2011 16:56:57 GMT

I have a number of Excel docs in a folder with naming convention of FileName_YYMMDD.

The problem is that the docs may contain a different number of sheets i.e. Doc1 has Sheet1/Sheet2

while Doc2 has Sheet1/Sheet2/Sheet3 etc.

I need to create a table of Doc name and Sheets belonging to that Doc.

Then I want to load the sheets from their associated docs in a loop statement.

Anyone any ideas?

Thanks

Load all sheets in all Excel documents

vgutkovsky — Tue, 12 Apr 2011 17:11:08 GMT

You might be able to do this is with a script macro. But a more elegant way would be to turn off errors by setting ErrorMode=0 and then doing a loop with the assumption that all workbooks have the number of sheets that the largest workbook has. With error reporting turned off, the script should be able to just proceed to the next load statement if a sheet is missing.

Regards,

Load all sheets in all Excel documents

bismart — Tue, 12 Apr 2011 18:18:56 GMT

Anybody got some sample script I could use???

Cheers Vlad... but sample script would be appreciated

Load all sheets in all Excel documents

vgutkovsky — Tue, 12 Apr 2011 18:30:28 GMT

Sure, it would look something like this:


SET ErrorMode = 0;
for a=1 to 3 //in this example, 3 is the number of sheets your maximum workbook has
 LOAD * FROM
 *.xls (biff, embedded labels, table is Sheet$(a)$);
next
SET ErrorMode = 1;

You can then define different table structures, etc. Or if the fields are identical across all sheets, it will autoconcatenate for you.

Cheers,

Load all sheets in all Excel documents

bismart — Wed, 13 Apr 2011 13:00:55 GMT

Thanks Vlad

Worked like a charm

I didn't know about the errormode setting so that got me over the problem

Thanks again

Dave

Load all sheets in all Excel documents

Thu, 10 Nov 2011 07:25:06 GMT

Hi Vlad,

I have tried your above condition in my application but its not working.

Below condition i used in my script :

SET ErrorMode = 0;

for a=1 to 3

LOAD * FROM

C:\Users\napo\Desktop\Excel Data\*.xlsx (ooxml, embedded labels, table is Sheet$(a)$);

SET ErrorMode = 1;

Load all sheets in all Excel documents

vgutkovsky — Mon, 14 Nov 2011 15:09:50 GMT

Are the worksheets in your workbook named Sheet1, Sheet2, etc?

Load all sheets in all Excel documents

Tue, 15 Nov 2011 04:25:56 GMT

Yes, Sheet1 , Sheet2 .....

Load all sheets in all Excel documents

rohit214 — Tue, 15 Nov 2011 06:17:40 GMT

hi antony

try this

SET ErrorMode = 0;

for a=1 to 3

LOAD * FROM

C:\Users\napo\Desktop\Excel Data\*.xlsx (ooxml, embedded labels, table is Sheet$(a));

SET ErrorMode = 1;

may it helps you

thanks

rohit

Load all sheets in all Excel documents

Tue, 15 Nov 2011 06:23:01 GMT

Hi Rohit,

Thanks,

Its working. Suppose if i am updating some data on sheet4 , how do i get Sheet4 data without touching in script part.

Regards,

Antony.

Load all sheets in all Excel documents

rohit214 — Tue, 15 Nov 2011 06:29:18 GMT

then u have to change a=1 to 4

thanks

rohit

Load all sheets in all Excel documents

Tue, 15 Nov 2011 06:41:40 GMT

i want automatically,without changing in script. How do i do ???

Load all sheets in all Excel documents

Tue, 15 Nov 2011 08:33:47 GMT

Hi All,

If you want to load all the sheets from all excel files in a folder, the below code should do the work for you.

Let us assume that the folder is "C:\temp" and you need to load all the sheets from each of these files. The script is below.

for each File in filelist ('C:\Temp\*.xls')

ODBC CONNECT TO [Excel Files;DBQ=$(File)];

tables:

SQLTables;

DISCONNECT;

FOR i = 0 to NoOfRows('tables')-1

LET sheetName = purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39));

Data:

LOAD * FROM $(File) (biff, embedded labels, table is $(sheetName)) where right('$(sheetName)',1) = '$';

DROP Table tables;

next File

Let me know whether this works for you.

Best Regards,

Sajeevan

Load all sheets in all Excel documents

Tue, 15 Nov 2011 10:18:49 GMT

Hi ,

im using 'xlsx' format then how do i that ???

Load all sheets in all Excel documents

Tue, 15 Nov 2011 10:42:01 GMT

Hi Sajeevan,

I did, its working perfectly.

Thanks,

Antony.

Load all sheets in all Excel documents

Tue, 15 Nov 2011 10:44:21 GMT

Hi Antony,

Did it work on "xlsx" or "xls" format?

If it was on "xlsx" what changes did you make to the script? Please share this.

Thanks,

Sajeevan

Load all sheets in all Excel documents

Tue, 15 Nov 2011 10:51:29 GMT

Hi Sajeevan,

I have one query, if i want to load latest excel file in my application then how do i do ?

Example :

Data_Sep.xlsx;

Data_Oct.xlsx;

Data_Nov.xlsx;

I need to load Data_Nov.xlsx, if it is Dec month then it should load Data_Dec.xlsx.

Thanks,

Antony.

Load all sheets in all Excel documents

rohit214 — Tue, 15 Nov 2011 12:41:23 GMT

hi saleevan

your code working fine but i am not able to understand

sheetName = purgeChar(peek('TABLE_NAME', i, 'tables')

why you use this

can you please explain this in detail..

or is there any other solution for that line so i can use in place of that

thanks &regards

rohit

Load all sheets in all Excel documents

Tue, 15 Nov 2011 13:04:11 GMT

Hi Rohit,

PurgeChar will delete the unwanted characters.

Peek will return the fieldvalue from TABLE_NAME field in table call 'tables'.

You can always refer to the help file to understand these better.

Why are you insisting that you want to use another solution - is there a reason?

Regards,

S A J E E V A N

Load all sheets in all Excel documents

rohit214 — Wed, 16 Nov 2011 06:09:37 GMT

hi sjeevan

thak you for explainig me all those thing

regards

rohit