Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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.
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