Load Dynamic Headers from Excel

[ciaran_mcgowan]

Hi all,

Can anyone share a good method for loading multiple tables from the same excel tab into QV? Each table has a similar structure except the amount of rows can vary (between 9 and 15) and the last 3 columns have different headers.

Table 1:

Date	Header 1	Header 2	Header 3	Header 4	Person 1	Person 2	Person 3

Table 2:

Date	Header 1	Header 2	Header 3	Header 4	Person 4	Person 5	Person 6

The tab can contain anywhere between 10 & 30 tables and as I mentioned, the values in each cell are the same format, only the header names.

Thanks.

Update: Apologies if my requirements were too vague, here is a little more detail:

Headers 1-4 fields contain picklists relating to each person.

Person (n) contains numeric scores and their name is the header.

The sheet has more than 2 tables (unknown amount)

Message was edited by: Ciarán McGowan

[ciaran_mcgowan · Accepted solution]

Hi Gysbery,

Thanks for pointing me in the right direction. I am loading all files in the excel sheet and then using the following code to store each header name as a field:

  if(Peek(Header) = 'Date',
  Peek(Person1),
   Peek(Person)) AS Person

This fills creates a Person field for everyone in the Person1 column and fills in the previous row's data. I repeat & concatenate for the Person2 & Person3 columns. Then drop blank rows between tables and all header rows, manually naming each column.