Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
patriciousa
Contributor II

Edit script to apply to all future excels load

Hi Community.

I have the Qlik Sense Desktop version and I have this in my script:

LOAD

    Legajo,

Nombre,

    Categoria,

    LoS,

    "ID Dpto",

    Departamento,

    "Tipo Hora",

    Subfield(Proyecto, '-', 1)as Proyecto,

    Subfield (Proyecto, '-', 2) as Job,

    Actividad,

    "F registro" as  "Fecha Registro",

    Descripción,

    "Descripción Cliente",

    Cantidad,

    "Categoria General",

    Categoria1 as Facturable,

    Mes,

    Año,

    "Facturable Real"

FROM [lib://audit con/archivos\No104.xls]

(biff, embedded labels, header is 1 lines, table is sheet1$);

As you can see. I'm renaming fields and creating Subfields.

In my original script there are over 20 excels that I had to do those changes to each LOAD. How can I do it once and make it apply to all the excels I have in the script?

Thank you in advance.

Regards.

1 Solution

Accepted Solutions
undergrinder
Valued Contributor II

Re: Edit script to apply to all future excels load

Hi Patricio,

You can write a subroutine for this task.

e.g.

sub ReadXls(Root)

     For Each File in filelist (Root&'\*.*')

          if right('$(File)',3)='xls' then

               Table:

                    Load     *

                    From  [$(File)];

          end if

    

     Next File

end sub

Call ReadXls(libname);

The script read all xls file in a directory given at parameter, regardless of filename.

G.

5 Replies
paul_scotchford
Valued Contributor

Re: Edit script to apply to all future excels load

Is each of the excels files the same structure ? i.e. same fields ! and same worksheet name !

If so and the only difference is the file name, then you can wildcard * the file name like the below example...

FROM [lib://audit con/archivos\No*.xls]  (biff, embedded labels, header is 1 lines, table is sheet1$);

All the Excel files will load using the same structure you have designed in the script....

If there is only a single sheet per workbook then you can leave the sheet spec out of the line e.g.

FROM [lib://audit con/archivos\No*.xls]  (biff, embedded labels, header is 1 lines);

Regards

Paul

patriciousa
Contributor II

Re: Edit script to apply to all future excels load

Hi Paul.

Yes, all the excels have the same structure and the same sheet name. The only difference is the Excel name but sometimes the excels don't have "No" as start name, they have characters...

LOAD

Name as 123Name

FROM [lib://audit con/archivos\No104.xls]

(biff, embedded labels, header is 1 lines, table is sheet1$);

LOAD

Name as 123Name

FROM [lib://audit con/archivos\SinArchivo.xls]

(biff, embedded labels, header is 1 lines, table is sheet1$);

If I want "Name" to be "123Name" in both loads, where I am suppose to add that sentence in the script?

Thank you.

Regards.

paul_scotchford
Valued Contributor

Re: Edit script to apply to all future excels load

Hi Patricio

Your code above is correct, as file names differ then you will need a separate load script as you show in your example.

Would there be major disruption if you rename the file just for the load , not the best solution I know, but unfortunately this is  this downside of multi file ETL.

Regards

Paul

undergrinder
Valued Contributor II

Re: Edit script to apply to all future excels load

Hi Patricio,

You can write a subroutine for this task.

e.g.

sub ReadXls(Root)

     For Each File in filelist (Root&'\*.*')

          if right('$(File)',3)='xls' then

               Table:

                    Load     *

                    From  [$(File)];

          end if

    

     Next File

end sub

Call ReadXls(libname);

The script read all xls file in a directory given at parameter, regardless of filename.

G.

patriciousa
Contributor II

Re: Edit script to apply to all future excels load

Thank you Undergrinder.

Community Browser