Data loading issue bcoz of row inconsistency

[Anonymous]

Hi, I have attached 2 sample excel files where I need to load multiple files like these.

Concern:  Header (Highlighted in Yellow) is at 18th row in Book1 & 16th Row in Book2.

How to load these files automatically by excluding data above the headers?

[alexandros17]

You have to normalize the xls file and delete all rows above your header

[its_anandrjs]

Hi,

When loading use wizard and use Enable Transformation Step and delete above rows.

Hope this helps

Thanks & Regards

[its_anandrjs]

Hi,

When you using Table file wizard and after deleting the rows above the excel file in qlikview your syntax would be..

Book1:

LOAD F1,

     [Server Name],

     Date,

     F4,

     Time,

     [User Name],

     [Terminal name],

     TCode,

     Program,

     [Security Audit Log message text]

FROM

(ooxml, embedded labels, table is Sheet1, filters(

Remove(Row, Pos(Top, 14)),

Remove(Row, Pos(Top, 13)),

Remove(Row, Pos(Top, 12)),

Remove(Row, Pos(Top, 11)),

Remove(Row, Pos(Top, 10)),

Remove(Row, Pos(Top, 9)),

Remove(Row, Pos(Top, 8)),

Remove(Row, Pos(Top, 7)),

Remove(Row, Pos(Top, 6)),

Remove(Row, Pos(Top, 5)),

Remove(Row, Pos(Top, 4)),

Remove(Row, Pos(Top, 3)),

Remove(Row, Pos(Top, 2)),

Remove(Row, Pos(Top, 1))

));

Book2:

LOAD F1,

     [Server Name],

     Date,

     F4,

     Time,

     User,

     Terminal,

     TCode,

     Program,

     [Security Audit Log message text]

FROM

(ooxml, embedded labels, table is Sheet1, filters(

Remove(Row, Pos(Top, 12)),

Remove(Row, Pos(Top, 11)),

Remove(Row, Pos(Top, 10)),

Remove(Row, Pos(Top, 9)),

Remove(Row, Pos(Top, 8)),

Remove(Row, Pos(Top, 7)),

Remove(Row, Pos(Top, 6)),

Remove(Row, Pos(Top, 5)),

Remove(Row, Pos(Top, 4)),

Remove(Row, Pos(Top, 3)),

Remove(Row, Pos(Top, 2)),

Remove(Row, Pos(Top, 1))

));

Hope this helps

Thanks & Regards

[tresesco]

Somehow you have to give your files a generic definition. For example, from your two files, I take it as 'accept rows when second column is non-null'. Then the transformation would work like below:

LOAD [Server Name],

     Date,

     Time,

     User,

     Program,

     [Security Audit Log message text]

FROM

(ooxml, embedded labels, table is Sheet1, filters(

Remove(Row, RowCnd(CellValue, 2, StrCnd(null)))

));