Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

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.

3 Replies

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

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.


talk is cheap, supply exceeds demand
hirishv7
Honored Contributor

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

Hi,

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

Not applicable

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

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.

Community Browser