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

A7R3 — Sat, 16 Nov 2024 17:36:55 GMT

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.

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

lironbaram — Thu, 14 Jan 2021 11:56:43 GMT

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

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

A7R3 — Fri, 15 Jan 2021 01:04:14 GMT

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

Thank you.

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

Amit — Tue, 22 Aug 2023 09:23:25 GMT

Perfect answer!

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

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

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

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

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