Loading multiple excel files with Cross table in Script

[Krish]

I am trying to load multiple files in my load script. I use cross table to load the data as it is in pivot.

Script:

CrossTable(Dates, Usage, 4)
LOAD
*

FROM
[\\MyPC\*filename*.xlsx]
(ooxml, embedded labels, header is 5 lines, table is Sheet, filters(
Remove(Row, RowCnd(CellValue, 5, StrCnd(longer, 1)))
))

The file contains numbers for date range. Some files have data for 10 days while others have data for 20 or 25 days etc. I am getting error when the date range is different.

				1	2	3	4
10302040230	T1	ABC		0.14	0.07	0.06	0.4
10202032103	T2	BCD		0.5	0.6	0.8	0

1,2,3,4  are dates. One file will have 4 days while other might have 30 days. After doing a cross table load, my data would look like 

F1	F2	Date	Amt
10302040230	ABC	01	0.14
10302040230	ABC	02	0.07
10302040230	ABC	03	0.06
10302040230	ABC	04	0.4
10202032103	BCD	01	0.5
10202032103	BCD	02	0.6
10202032103	BCD	03	0.8
10202032103	BCD	04	0

I am also looking to load the month info from source file. FileName is filename - Oct 2021.xlsx, I am using Right(Subfield(FileBaseName(), '-', 2),8) as FileDetails in script 

[marcus_sommer · Accepted solution]

You may try it in this way:

for each file in filelist('\\MyPC\*filename*.xlsx')
   Script: CrossTable(Dates, Usage, 4) LOAD * FROM [$(file)] (ooxml, ...)
next

- Marcus