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

[effinty2112]

Hi,

Would it be right to say that for every record like:

Date	Header 1	Header 2	Header 3	Header 4	Person 1	Person 2	Person 3
a	b	c	d	e	Curly	Larry	Moe

You want to get to:

Date	Header 1	Header 2	Header 3	Header 4	Person
a	b	c	d	e	Curly
a	b	c	d	e	Larry
a	b	c	d	e	Moe

?

If so then I would load the entire sheet and use the File Wizard transformation step and follow a strategy something like:

Delete all header rows apart from the first one.

Crosstable with Date and Header 1,2,3 & 4 as Qualifier Fields

Call the Attribute Field Person

Call the Data Field something appropriate to the measure.

Good luck

Andrew

[ciaran_mcgowan]

Hi Andrew,

That's what I was thinking but running into difficulties when I get to the second table, where the people are different. I would need a solution that caters for multiple cross tables.

[Gysbert_Wassenaar]

You can force concatenation so the tables are concatenated into one table. In that case you'd get different fields for the last three columns of each excel table. So you'd have person1 through person6 in the final table.

Or you can name the fields yourself and don't use the column headers as field names:

MyTable:

LOAD @1 as Date, @2 as Header1, .... @6 as A, @7 as B, @8 as C

FROM abc.xlsx (ooxml, no labels, header is 1 lines).

[avinashelite]

check this

Dynamically Loading Multiple Excel Files

[effinty2112]

Hi Ciaran,

I would load the entire spreadsheet as the first step , all tables, like

Date	Header 1	Header 2	Header 3	Header 4	Person 1	Person 2	Person 3
a	b	c	d	e	Curly	Larry	Moe
sd	w	af	g	af	Curly	Larry	Moe
qrwe	a	gw	e	g	Curly	Larry	Moe
g	af	q	w	er	Curly	Larry	Moe
qrwe	rqw	rt	qwr	w	Curly	Larry	Moe
Date	Header 1	Header 2	Header 3	Header 4	Person 4	Person 5	Person 6
qrwe	rqw	rt	qwr	w	Geddy	Alex	Neil
dvz	sf	sfsf	sf	fsfd	Geddy	Alex	Neil
qrwe	a	gw	e	g	Geddy	Alex	Neil
g	af	q	w	er	Geddy	Alex	Neil
sd	w	af	g	af	Geddy	Alex	Neil
etc.

Then delete all rows where column 1 equals 'Date', starting from row 2:

Date	Header 1	Header 2	Header 3	Header 4	Person 1	Person 2	Person 3
a	b	c	d	e	Curly	Larry	Moe
sd	w	af	g	af	Curly	Larry	Moe
qrwe	a	gw	e	g	Curly	Larry	Moe
g	af	q	w	er	Curly	Larry	Moe
qrwe	rqw	rt	qwr	w	Curly	Larry	Moe
qrwe	rqw	rt	qwr	w	Geddy	Alex	Neil
dvz	sf	sfsf	sf	fsfd	Geddy	Alex	Neil
qrwe	a	gw	e	g	Geddy	Alex	Neil
g	af	q	w	er	Geddy	Alex	Neil
sd	w	af	g	af	Geddy	Alex	Neil

Then carry on with the crosstable part. The file wizard transformation may be a bit awkward to use at first but when you get into the way of using it's very powerful.

cheers

Andrew

[ciaran_mcgowan]

Thanks again for your reply,

If I load all tables into one and delete all header rows after the first one, then won't I lose the names of all people who aren't in the first table?

[ciaran_mcgowan]

Hi Gysbert,

I don't know concatenation could work here as there is an unknown number of tables, each with different People (I've updated my requirements to make it more clear).

Unless there was a way to move each Person (n) header into a row?

[effinty2112]

Hi Ciaran

No you won't. The header lines are loaded just as text. The first one can be used to create the headers names in your QV table (embedded labels) and the rest can be deleted. Use the file wizard. Hit the conditional Delete button and enter the condition shown:

don't forget to click Add:

click OK Edit

Add the additional condition that we do this from the second row to the last.

Select the Range button. Click From and enter 2 from the top. Now click To and enter 1 from the bottom. click Select and just press OK here.

don't forget to click Add:

Click OK. As you add transformations you should see the effect on the screen. In a simiar way you can delete blank lines or any other garbage too.

Good luck

Andrew

[ciaran_mcgowan]

Thanks again for your help but I'm just not that savvy with the Transformation Wizard. You've shown me that I need to study it more.

For now, I have found the solution I was looking for, which I'll post shortly.