topic load excel file only if it has particular column else do not load it in QlikView

load excel file only if it has particular column else do not load it

Mon, 20 May 2013 10:46:30 GMT

Hi All,

I am loading multiple excel files.

In a excel file the Particula feild for example (NAME) does not appear if the Project is not active, but the NAME appears in file if the project is active.

so keeping this in mind, I have 20 to 30 excel files for different project. So is there a way to load all files which has particular feild and leave the remaining file unloaded?

Any suggestions and help is very much appreciated.

Thank you in advance

Khusi

Re: load excel file only if it has particular column else do not load it

Mon, 20 May 2013 11:37:50 GMT

assuming the files are in the same location (C:\Direcotory ) in the exa\mple below,

then the following should load the data with the specified field populated.

for each File in filelist ('C:\Directory\*.xls')
for A = 1 to 10

LOAD

    @1   as Name,
       @2 as [Data1] ,
    @3 as [Data2]

FROM $(File) (biff, no labels, table is Sheet$(A)$) where @1 > ' ';
next A

Re: load excel file only if it has particular column else do not load it

Mon, 20 May 2013 11:45:58 GMT

Hi,

which code here will unload the file if column is not available, would you explain?

sorry I am not able to understand it

anyways thanks again for trying to help.

Re: load excel file only if it has particular column else do not load it

Mon, 20 May 2013 11:51:54 GMT

If you have 2 excel spreadsheets a.xls & b.xls containing the following

a.xls

Name project status

Fred Live

in progress

John completed

b.xls

Name Project status

Tim on going

cancelled

mark finishing

then the code would look at both spreadsheets & only return entries where field 1 (name) contains a value

so the results would be

name data1

Fred Live

John completed

Tim on going

mark finishing

Re: load excel file only if it has particular column else do not load it

Tue, 21 May 2013 06:59:54 GMT

hi,

Thanks again,

but my requirement is to check if the feild NAME is there or not, if not then do not load that file.

example

a.xls has

NAME Age

and b.xls has

empty(no feild name) Age

Now my requirement is,

I need the script that check a.xls and b.xls if feild NAME is there or not, in this situation script should load only file a.xls, regardless of the value under that feild.

I have this case because I am using some formulas in excel files and due to this if project is active, then only the feild will be shown else it will be empty. and whole coulmn will be empty if project is not active.

still waiting for the solution, can anyone pls help to fix this problem

Re: load excel file only if it has particular column else do not load it

Tue, 21 May 2013 09:31:51 GMT

the easiest way would be to have the field always defined in excel & just populate it when it has a value then the script already supplied would work. Assuming your data always has the label "Name" in it the following would work.

In this case we do 2 passes of the data. The first pass checks to see if the data contains a label of "name"

for each File in filelist ('C:\Directory\*.xls')
for A = 1 to 10

checktable:

LOAD

@1 as Name
FROM $(File) (biff, no labels, table is Sheet$(A)$) where @1 = 'Name ';

next A

if trim(peek('Name',0,'checktable')) = 'Name' then

drop table checktable

data:

LOAD *
FROM $(File) (biff, embedded labels, table is Sheet$(A)$) ;

end if

Re: load excel file only if it has particular column else do not load it

Tue, 21 May 2013 09:59:28 GMT

Hi,

Thank you.

I have now changed in exel itself so that I will have the feild name of non active projects also but the value will be "Exclude".

so in this case I will use the filter and wont select the one with "Exclude".

once again thank for the effort.