Automatically pulling data from multiple Excel files.

[Anonymous]

Hi All,

I have an excel "Mapping Details" which has the cell and sheet details I need to pull for various departments (Each department has different EXCEL files with different formats). Can I do any automation so that if I update any mapping in Mapping Details file, the script should pull the data from the new column automatically when reloaded....

[ahaahaaha]

Hi Shiva!

When loading files EXCEL  you can be processed on the transformation step. An example of how to transform using Wizard transformation to be found on this postLoad script to load this type of (unfriendly format) table?

If the structure of your files XLS is not changed, only the changed data, loading at Qlik will take place without changing the script code. If the file XLS structure change will have to use a step transformation again.

Regards,

Andrey

[avinashelite]

Check this thread

Dynamically Loading Multiple Excel Files

[Anonymous]

The script in the link provided is excellent.But what i am actually expecting is i have an excel sheet which says me which column and which row needs to be pulled from various files for respective departments for respective KPIs. I need to write a script which reads the information in this excel and should automatically go to the sheets mentioned and pull the data from the cells mentioned . Hope am making sense.

[Anonymous]

Hi shiva ,

Refer this URL : Dynamically Loading Multiple Excel Files

[avinashelite]

You can achieve this  by defining the sheet name, column and excel name in the variable i.e. in script

share the sample data and mapping so that we could give the example script for the same

[cotiso_hanganu]

We had once to concatenate some XLS files coming with P&L info from different countries of a group, each of them with a different logic. In some cases, page names were Business Units, in others, BU was a column , while the sheet was a different dimension, (or nothing in the file coming from another country).

It can be done, because with Qlik (both View and Sense) you can load, in a variable, a pretty dynamic/self-generated string that contains a script, that can be afterwards executed, within the same QVW reload execution.

We did even script parts that were creating script parts that were reading data... or script parts that were creating a 30k characters formula in a Qlik variable ! (though not recommended, performance wise)

In order to give a dedicated answer, you need to put on the line some simple example of the XLS files you want to read and some words regarding the data alignment needs.