Load multiple excel from same folder

[joancasellasvega]

Hi,

I currently have around 20 excel files  called the same: output_2020_05_06,output_2020_05_15, output_2020_06_03..., output_2020_07_20.

The only thing that changes is the month and the day.

They all have the same columns, but I dont need them all.

All save in the same folder.

FROM [lib://Ficheros Qlik/output 2020_05_04.xlsx]
(ooxml, embedded labels, table is [Table 1]);

the scrip that I'm currently using is the following ( I will put only two tables as an example)

LOAD
INSTANCIA,
ESTADO,
Proceso as Proceso_old,
if(Proceso='Mail erroneo del cliente','Mail erróneo del cliente',
If(Proceso='SDR erronea','SDR errónea',
If(Proceso='SDR No Aportada' or Proceso='SDR no aportada','SDR No aportada',
if(len(trim(Proceso))=0, '',Proceso)))) as Proceso,
LastModified,
Agente
FROM [lib://Ficheros Qlik/output 2020_05_05.xlsx]
(ooxml, embedded labels, table is [Table 1]);

Concatenate

LOAD
INSTANCIA,
ESTADO,
Proceso as Proceso_old,
if(Proceso='Mail erroneo del cliente','Mail erróneo del cliente',
If(Proceso='SDR erronea','SDR errónea',
If(Proceso='SDR No Aportada' or Proceso='SDR no aportada','SDR No aportada',
if(len(trim(Proceso))=0, '',Proceso)))) as Proceso,
 Date(Date#(LastModified,'YYYY-MM-DD'),'DD/MM/YYYY') as LastModified,
Agente
FROM [lib://Ficheros Qlik/output 2020_05_06.xlsx]
(ooxml, embedded labels, table is [Table 1]);

as you can see, I do some changes at column  Proceso and for some tables, I need to modify the LastModified date.

all tables should have the DD/MM/YYYY, but there are some that have this format YYY-MM-DD

Is there a way I can create some sort of loop so I don't need to have one script per table?

Thanks in advance,

Joan

[martinpohl]

you can use

date(alt(date#(LastModified,'DD/MM/YYYY'),date#(LastModified,'YYYY-MM-DD'))) as LastModified

for example

[chandu441]

For Each Ext in 'xlsx'
For Each File in FileList ('lib://Ficheros Qlik/'&'output *.'&Ext)
/* this piece Dummy table code added to avoid synthetic keys while loading multiple excel file if all the columns were not same in the source files */
Dummy_Table:
Load 0 as Dummy Autogenerate 1;

Concatenate (Dummy_Table)
LOAD
INSTANCIA,
ESTADO,
Proceso as Proceso_old,
if(Proceso='Mail erroneo del cliente','Mail erróneo del cliente',
If(Proceso='SDR erronea','SDR errónea',
If(Proceso='SDR No Aportada' or Proceso='SDR no aportada','SDR No aportada',
if(len(trim(Proceso))=0, '',Proceso)))) as Proceso,
Date(Date#(LastModified,'YYYY-MM-DD'),'DD/MM/YYYY') as LastModified,
Agente
FROM $(File)(ooxml, embedded labels, table is [Table 1]);
Next File
Next Ext
Drop field Dummy;

[joancasellasvega]

Hi @chandu441 ,

thanks for your answer. QS is thowing me an error:

Does not seems it is loading anything. Any thoughts?

thanks,

joan

[chandu441]

Hi,

Try like this below with your file location paths and include your fields list in the  table load.

Dummy_Table:
Load
'0' as Dummy
Autogenerate 1;

For Each Ext in 'xlsx'
For Each File in FileList ('D:\Users\703246081\Downloads\'&'Variables *.'&Ext)
/* this piece Dummy table code added to avoid synthetic keys while loading multiple excel file if all the columns were not same in the source files */
Concatenate (Dummy_Table)
Data:
LOAD * FROM $(File)(ooxml, embedded labels, table is Variables);
Next File
Next Ext

Drop field Dummy;

if it is not working... please check all the excel files data (first sheet) and the sheet name should be same for all.

[joancasellasvega]

Hi @chandu441 ,

Unfortunately this is not working for me. I need to try to spend more time with the code you sent and see if I can see where the error is.
Thanks for your help anyway.
Joan

[buchberger_a · Accepted solution]

If you just want an automatic concatenation, you can do this

FullTable:
LOAD * 
From [lib://Ficheros Qlik/output 2020_*.xlsx]
(ooxml, embedded labels, table is [Table 1]);

[ShrutiV]

Hi,

My data contains date in the filename.

I want to set a date range in the  variable for data load(like Load only last 6 months data, based on the 'MMM-YYYY'in the filename)

QVD Filename: Data_Sep_2023

Let vdateoutput=Date(Today(),'MMM_YYYY');

Load *

FROM [lib://Excel Data/DATA_$(vdateoutput).XLSX]
(ooxml, embedded labels, table is Sheet1);

Currently it is loading only current month data. [Because of Variable vdateoutput]

How can I load data from Mar2023. Date is not present in the file, only in filename.

Also when I use * (asterik) in the filename it give column not found error, though the column is present.

FROM [lib://Excel Data/DATA_*.XLSX]
(ooxml, embedded labels, table is Sheet1);

[buchberger_a]

You can create a  temp-table with your last 6 month with autogenerate and then loop through this table:

TempCalendar:

LOAD

    Date(MonthStart(AddMonths(Today(), -IterNo() + 1)),'MMM_YYYY') AS Month

AUTOGENERATE 1 WHILE IterNo() <= 6;

For i=1 to NoOfRows('TempCalendar')

Let vdateoutput = FieldValue('Month',$(i));

    Trace LoadedMonth: $(vdateoutput);

    Data:

    Load *

    FROM [lib://Excel Data/DATA_$(vdateoutput).XLSX]

    (ooxml, embedded labels, table is Sheet1);

Next i;

Drop Table TempCalendar;