Read multiples and changing tables from only one sheet from Excel files

[#1]

Hi everyone,

I was looking about a topic with this scenario but unfortunately I didn't find anything interesting.

The scenario is the following:

I have a set of Excel files with distinct structure like several tables (at Only one Sheet for every Excel file), could be 3, 4, 5.... these tables have dynamic rows and colums too.

Tables inside the Excel file are separates between them with a changing numer of blank colums (for example: sometimes will be one column, another could be other number like 4,6,10...).

Tables included rows who extends from many columns at the beggining and also includes blank rows what I don't want to load.

Requirements:

I need to stablish an script algorithm who can read the different tables from distinct Excel files which can change their structure (on numer, number of colums and number of rows) whitout have errors during load data when that sctructure change from another load on future reload.

Best Regards. Nahum.

[Gysbert_Wassenaar]

Give names to the ranges in excel that contain the tables. You can then use these table names in qlikview to load data from them. See Define and use names in formulas - Excel for more information.

[HirisH_V7]

Hi,

Can you explain more clear with some examples and probable occurrences .

[#4]

Hi,

I upload a couple of files to ilustrate the problem and help to understand my explanation.

I have a data source made by Excel files (one for year since 2012), each file has one sheet for every month, each sheet has several tables which share some headers as u can see in attached file ("Tables Relantionship.png").

Tables are delimited by an indeterminated number of columns, also every table could have a different number of columns from one sheet/file to other, one example of this are on attached file ("Example.png").

I have to make an scripting algorithm to automate the load of balance and P&L tables from every sheet from every file from the past and some which will come in the future. All balance tables must to be in only one table, and same with P&L tables.

But hese tables haven't got the format what I need, so I need to transpose them looking for these schema:

Table:

Epigrahp ------- Bank Code -------- Bank Name ------- Date --------- Quantity

I attached my code ("PruebaExcel.qvw"), but it doesn't work. Im using neutral header (@) to be able to load the changing number of fields of every table but when I try to concatenate inside of loops diferent tables of the same type dont work because they have different number of fields.

Hope now you have more information to understand and can help me.

Thanks in advice and Regards. Nahúm.