Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a process that load several xlsx file every night and put all the info into a QVD. The problem is how to delete all tables after the loading.
LOAD [Article Code]as ItemId,
Upper(Proveedor) as Proveedor_Tarifa,
Tarifa,
Fecha as From
FROM
NoConcatenate
QVD:
Load ItemId, Tarifa, Upper(Proveedor_Tarifa) as Proveedor_Tarifa,From,Proveedor_Tarifa&'-'&ItemId&'-'&From as Tarifa_Id
FROM
Concatenate (QVD)
load ItemId, Tarifa, upper(Proveedor_Tarifa) as Proveedor_Tarifa,From,ItemId&'-'&From as Tarifa_Id resident Tarifas_New;
drop table Tarifas_New%;
Many thank's
Eduard
List each table by its name:
DROP TABLE Tarifas, Tarifas_New;
If you have dozens of tables you may want to use a loop:
For i =1 to NoOfTables()-1
LET vTable = TableName($(i))
When Match( '$(vTable)' , 'Tarifas') DROP TABLE $(vTable) ;
Next
Is this not working?
Tarifas_New:
LOAD [Article Code] as ItemId,
Upper(Proveedor) as Proveedor_Tarifa,
Tarifa,
Fecha as From
FROM
(ooxml, embedded labels, table is Tarifas);
QVD:
NoConcatenate
Load ItemId,
Tarifa,
Upper(Proveedor_Tarifa) as Proveedor_Tarifa,
From,
Proveedor_Tarifa&'-'&ItemId&'-'&From as Tarifa_Id
FROM
Concatenate (QVD)
LOAD ItemId,
Tarifa,
Proveedor_Tarifa,
From,
Proveedor_Tarifa&'-'&ItemId&'-'&From as Tarifa_Id
Resident Tarifas_New;
DROP Table Tarifas_New;
The problem is I don't know the name, it load for example 3 tables one for each excel loaded.
Tarifas_New1
Tarifas_New2
Tarifas_New3
till 'N' tables, depending on the number of excel sheet loaded..
Then you need to check that your excel files contain exactly the same number of fields with exactly the same names. If they don't then each excel file will result in a new table.
Maybe it's easier and more flexible to load multiple files with a filelist-statement:
Loop through Folders and sub folders to get file names
Dynamically Loading Multiple Excel Files
- Marcus