How to reset row() in loading multiples file through a For-Next loop

[A7R3]

Hi,

I am using the following load script to read Excel spreadsheets organized by date folder.

FOR Each vDir in DirList('$(vDataPath)/*')
   FOR each vFile in filelist (vDir&'\BOM*.xls' ) //New

   TableCompWorkBench:
       LOAD Distinct
       TRIM(Mid(filebasename(), 16)) as Products,
       FileTime() as ReportTime,

       PartNumber
       FROM [$(vFile)] (biff, no labels, header is 10 lines) //New
   Next vFile
Next vDir

I would like to set the counter of the first row in every file loaded to be 1.

I have tried the autonumber function as follow. But it doesn't work. The counter basically keep running 

       AutoNumber(TRIM(Mid(filebasename(), 16))&FileTime()&RecNo()) as Counter,

The input/output expected will as follow.

	Input			Output
File Folder Path	Products	ReportTime	PartNumber	Counter
20201201/A.xls	A	20201201	X1	1
20201201/A.xls	A	20201201	X2	2
20201201/A.xls	A	20201201	X3	3
20201201/A.xls	A	20201201	X4	4
20201201/B.xls	B	20201201	X1	1
20201201/B.xls	B	20201201	X2	2
20201201/B.xls	B	20201201	Y1	3
20201201/B.xls	B	20201201	Y2	4
20210101/A.xls	A	20210101	X1	1
20210101/A.xls	A	20210101	X2	2
20210101/A.xls	A	20210101	X3	3
20210101/A.xls	A	20210101	X4	4

s there any suggestion from the expert in the forum?

Thanks in advance.

[lironbaram · Accepted solution]

Hi 
if you just want a counter of records in each file then 
use recno()  with no other function

[A7R3]

Yes. The RecNo() works. I thought the RecNo() is the same as Row() that will continue incrementing the counter. 

Thank you.

[Amit]

Perfect answer!