I have a lot of xls files (1000+) with information in a table format. I need to concatenate this information. The date is in another page in the xls, in ONE cell for the whole table, so to "join" it to the table, i had used Join to make a cartesian product.
The problem I have is that the script finishes with table Buses and Table Buses-1. And in the other hand, it only makes the cartesian product with the first date loaded, the others are totally discarded.
I've tryied loading only the dates, at they are all loaded, but when I do the Cartesian Product (Join) all are discarded except the first one.
¿can anybody help me?
This is the structure of the xls files is:
a) Table "Ocupacion"
Bus
Servicio1
Number People
Total Capacity
1000
City1-City2
25
40
1232
City2-City3
34
41
...
...
...
...
b) Table Planilla
And in another sheet, there is a Cell with date: (Column 7, Row 2)
"25-20-2015"
This is the code I've used:
FOR each File in filelist ('Subidas y bajadas Turno\Subida *.xls')
[Buses]:
LOAD @1 as Bus,
@2 as Servicio,
@3 as [# Personal Transportado],
@4 as Capacidad,
filename() as Archivo
FROM [$(File)] (biff, no labels, header is 1 lines, table is [Ocupación$]);
Left Join (Buses)
LOAD
@7 as Fecha
FROM
[$(File)]
(biff, no labels, table is Planilla$) Where RecNo()=1;
