Massive Drop tables

[ecabanas]

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 (ooxml, embedded labels, table is Tarifas);

NoConcatenate

QVD:

Load ItemId, Tarifa, Upper(Proveedor_Tarifa) as Proveedor_Tarifa,From,Proveedor_Tarifa&'-'&ItemId&'-'&From as Tarifa_Id

FROM (qvd);

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

[Gysbert_Wassenaar]

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

[sunny_talwar]

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 (qvd);

Concatenate (QVD)

LOAD ItemId,

          Tarifa,

          Proveedor_Tarifa,

          From,

          Proveedor_Tarifa&'-'&ItemId&'-'&From as Tarifa_Id

Resident Tarifas_New;

DROP Table Tarifas_New;

[ecabanas]

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..

[Gysbert_Wassenaar]

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.

[marcus_sommer]

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