where clause not working

[vvvvvvizard]

I have a list of excel files 201401.xlsx,201402.xlsx,201403.xlsx ..... until 201505.xlsx

the first loads works perfect where i wna load all data before 201404 including 201404

Load

FROM

(ooxml, embedded labels, table is Export)

Where  SubField(Filename(),'.',1)<=201504;

the second load gives  alot of table not found error

FROM

(ooxml, embedded labels, table is Export)

Where  SubField(Filename(),'.',1)>201504;

This works fine

so if i changed it to load 201505 file only i get no table found errors, and the field names in the previous load is a copy and past to the ones used here , so this tells me its the where clause and not the fields missing

FROM

(ooxml, embedded labels, table is Export);

[swuehl]

Have you double checked that the Excel file actually contains a sheet called Export and can be loaded using the script Editor wizard?

[MayilVahanan]

Hi

I think

Export sheet is not available in 201505.xlsx.

Please confirm that

[PrashantSangle]

Hi,

try below

date#(SubField(Filename(),'.',1),'YYYYMM')>Date#(201504,'YYYYMM');

Regards

[Gabriel]

Hi,

It sound to me as the first table has same name as the second table and if this is the case the second table with concatenate with the first table. usually this is the cause of table not found error. What you might want to do is enter NOCONCATENATE between the 2 tables

[swuehl]

A second comment, if you want to exclude the files from being loaded, I would use a

For each File in Filelist('C:\Users\E936682\Desktop\Report_Builder_Data\ADDM\*.xlsx')

.

Next File

to iterate over all files and check the filename in a IF...THEN...LOAD.. ENDIF statement, instead of doing the filename check on a per record base.

[vvvvvvizard]

from the wizard loads fine, i edited the automatically generated script and added the where clause which gives the table not found error

[vvvvvvizard]

the second table has a few more fields than the first table , i will outer join the two tables once i get the second table to load

[vvvvvvizard]

I dont know how to , some guidance please

For Each file in FileList('C:\Users\E936682\Desktop\Report_Builder_Data\ADDM\*');

  if(Right(SubField($(file),6),'.',1)<=201504,

  LOAD Name,

     Annotationapm,

    Date(Date#(SubField(Filename(),'.',1),'YYYYMM'),'MMM-YY') as the_date

FROM

[$(file)]

(ooxml, embedded labels, table is Export);)

NEXT file;

[swuehl · Accepted solution]

Try like

For Each file in FileList('C:\Users\E936682\Desktop\Report_Builder_Data\ADDM\*')

IF SubField( file,'.',1) <= 201504 THEN

  LOAD Name,

     Annotationapm,

    Date(Date#(SubField(Filename(),'.',1),'YYYYMM'),'MMM-YY') as the_date

FROM

[$(file)]

(ooxml, embedded labels, table is Export);

ENDIF

NEXT file

I would also suggest running the script in the debugger first to check the values of variables.