Loading Issue

[#1]

Hi,

I have to load nearly 100 excel sheets, where the fields are

Project	HeadCount	Skill	Level
ABC	3	xyz	2
		cde	3
		ghik	4

The problem is few excel sheets doesn't contain the column HeadCount, so I dont want to load those sheets into QV.

There should be logic to check whether HeadCount field exists or not.

How can I handle this case?

Thanks in advance.

--Sindhu.

[Anonymous]

Hola,

Main:

Load *

from path\*.xlsx

Now, you will find a synthetic key between tables with all fields and tables less fields i.e., you will have Main and Main-1 tables in table viewer. Drop the table with minimum fields.

[#3]

Thanks for you reply.

But when I try to do so, in the table viewer the tables are like

Kindly help in this.

Thanks,

Sindhu.

[Anonymous]

Hi Sindhu,

In your script you can write logic to check if your file have all the fields or not for ex normally your table have 3 fields (including headcount column) then you can load the excel file  example -

Test:

LOAD *

FROM

(ooxml, no labels, table is Sheet1)

;

if NoOfFields('Test') > 3 then

drop table Test;

LOAD YrMonth,

     TotalAp,

     left(YrMonth,3)as Month,

    // Month(Month1)as Month,

     Right(YrMonth,2) as Year

FROM

(ooxml, embedded labels, table is Sheet1);

else

drop table Test;

LOAD * INLINE [

Month,MonthNum

Jan,1

Feb,2

Mar,3

Apr,4

May,5

Jun,6

Jul,7

Aug,8

Sep,9

Oct,10

Nov,11

Dec,12

];

Note:

in else part skip the load and check for other file.(hope you have logic to load multiple files in using loop.

[sushil353]

Hi,

If your no of column in all xl sheet is fixed.. let's say it is 4 in above case.

then simply load your excel table as temp.

Then check the no of fields in loaded table

let vFieldCount = noOfFields(temp_table);

now based on the value of vFieldCount you can load the data or else drop the temp table.

HTH

sushil

[Anonymous]

Hi Sindu,

Try below code in script:

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

XlsTables:
SQLTables;
DISCONNECT;

LET vRows = NoOfRows('XlsTables');


FOR i = 0 TO $(vRows)-1

LET vSheetName = subfield(peek('TABLE_NAME', i,'XlsTables'),'$',1);
LET vFieldCount = noOfFields(TABLE_NAME);
if $(vFieldCount) > 3 then
$(vSheetName):
LOAD *
FROM [ExcelFile.xlsx]
(ooxml, embedded labels, table is '$(vSheetName)');

NEXT i

DROP TABLE XlsTables;

Regards

Neetha 

[#7]

Hi,

I'm new to qlikview, little confused to write the logic to load multiple files.

Can you please help in this regard.

--Sindhu.

[Anonymous]

Hi Sindhu,

Have you tried above code i posted

Regards

Neetha

[#9]

Hi Neetha,

Can you help me in understanding the logic used.

So I'll know what changes I should make to it.

Regards,

Sindhu

[#10]

There are total 11 fields in my excel sheet, where as few sheets has only 10 fields.

All those 10 field sheets I don't want to load into my report.

--Sindhu