Load where..

[Anonymous]

Hi guys,

I have a load of files in a folder containing information. My code filters through and loads them all (see code below).

SET qv_path= 'C:\File-';

SET qv_filetype= 'DAT';

load

@1:47 as [PAYROLL],

@47:63 as [AccountNum]

FROM $(qv_path)*.$(qv_filetype) (fix, codepage is 1252,header is 1 lines)

WHERE not IsNull(AccountNum);

I've come across some blank files so i thought i'd write some code to say only import if the account number is not null. This crashes the file and says:

Field not found - <AccountNum>

Can anyone help?

[jagan · Accepted solution]

Hi,

Try this

load

@1:47 as [PAYROLL],

@47:63 as [AccountNum]

FROM $(qv_path)*.$(qv_filetype) (fix, codepage is 1252,header is 1 lines)

WHERE not IsNull(@47:63);

@47:63 is column name and AccountNum is alias.

Regards,

Jagan.

[hic]

Jagan's suggestion is correct, but it is just the first step towards a solution. You have an additional problem in that IsNull always will return FALSE - a text file can never contain any NULL values. Instead you should use "Where Len(Trim(@47:63))>0"

HIC