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

[#1]

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

[#2]

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

[#3]

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.

[#4]

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

[#5]

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

[#6]

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

[#7]

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.