Reading an Excel file with multiple header rows

[Dilie]

I've seen multiple posts about this topic, but couldn't find an answer for my case. I have an Excel file with a lot of headers in an inconsistent way in the file. Is there any way to read this in Qlik sense without editing the source file?

This is how the file is built up:

The 'TOTAAL' needs to be removed,

The first row means which week,

Second row is which day, 

Third row is which day of the month,

Fourth row is which time of the day.

The rest is data, and the vertical header is which person did what.

Is there any way to get this in a table (star schema)?

Thanks in advance!

[QFabian]

hi, first thing to do, add a 'recno() as id' field in the script, to get your index.

then, read with resident, but using where clause, to avoid rows you dont want.

for example :

where

id <> 5 'when 5 is the id for TOTAL unwanted row

[Dilie]

@QFabian Ok it is clear how to remove the unwanted row, but how to read the other rows?

[QFabian]

should be using crosstable function, probably one for each of those rows

[Dilie]

@QFabian Hey I tried using it but really couldn't make anything of it. Are you able to explain some more?

[QFabian]

send me a sample excel to show you

[Dilie]

@QFabian 

[QFabian]

try this, and then recreate date fields using functions :

Aux:
LOAD F1,
F2,
F3,
F4,
F5,
[1],
F7,
[2],
F9,
[3],
F11,
[4],
F13,
[7],
F15,
[8],
F17,
[9],
F19,
[10],
F21,
[11],
F23
FROM [lib://desktop (fabian_fabian)/exampleexcel.xlsx]
(ooxml, embedded labels, header is 2 lines, table is Sheet1)
Where (recno() = 3 or recno()>= 5) and F1<>'TOTAAL';

Aux2:
CrossTable(DiaX, Datos, 5)
LOAD F1,
F2,
F3,
F4,
F5,
[1],
F7,
[2],
F9,
[3],
F11,
[4],
F13,
[7],
F15,
[8],
F17,
[9],
F19,
[10],
F21,
[11],
F23
Resident Aux;
drop table Aux;

left join
Load
A as Date
FROM [lib://desktop (fabian_fabian)/exampleexcel.xlsx]
(ooxml, no labels, table is Sheet1)
Where
RECNO()=1;

Aux3:
Load
F1 as Index,
F2 as Persona,
rowno() as fila,
Datos as Valor,
Date,
num#(if(left(DiaX,1) = 'F', previous(DiaX), DiaX) ) as DiaXXX,
(num#((if(left(DiaX,1) = 'F', previous(DiaX), DiaX) )) + Date)-1 as Fecha,
if(left(DiaX,1) = 'F', 'orch','di') as Tipo

Resident Aux2;
drop table Aux2;

[Dilie]

@QFabian  Thanks for your answer!

When I try to load in the script I get an unknown error. I used the exact same excel and changed the FROM statement to my local folder and file.