scripting help need with rowno() when loading bulk files ?

[ashmitp869]

Hi there,

i have 100 files which I need to load -

The file is like below -

I have created a script to load the file -

HCW:
LOAD
Date(Date#(left(FileName(),6),'DDMMYY'),'D/M/YYYY') as _Date,
if(rowno()=1, 1,0) as count_active,
if(rowno()=2, 1,0) as count_total_isolation,
if(rowno()=3, 1,0) as count_total_community,
Nursing,
Medical,
"Allied Health" as AH,
"Scientific/Technical" as scientific,
"Admin/ Corporate" as admin_corporate,
"Other",
FileName() as HCW.FileName
FROM [lib://NSLHD HWC Exposures/*.xlsx]
(ooxml, embedded labels, table is [LHD Summary]);

But the count_active,count_total_isolation and count_total_community is not working because I am taking RowNo() .

Its working when I will load one by one file ?

Do anyone suggest , any way how to load the files at a time and the rowno() also works ?

Thanks

[Digvijay_Singh]

How about Recno(), you may try that.