Hi
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;
NEXT File
