Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Trying to load an excel file with a title row above the column heading row

i am trying to load an excel file into qlikview that has been generated by another system.  The first row is a multi-column header with an extra column  that doesn't appear in the main data portion.  when i delete the 1st row in the script as garbage, i lose all the column headings in the second row, as well as all of my data after loading.  the only way i seem to be able to load the data is to manually delete the 1st row in excel, which i don't want to do every time i reload - i am pretty sure that qlikview is able to manage the file as generated but i can't seem to make it work?  can anyone please help?  if this is not the right forum, please help ,as i can't see any other place to post a question.  thank you.

11 Replies
ecolomer
Honored Contributor II

Re: Trying to load an excel file with a title row above the column heading row

if we can move the file in question or part of it, we can help you better

lironbaram
Honored Contributor II

Re: Trying to load an excel file with a title row above the column heading row

when you load a file you can define the header lines which tells qlikview from which line it needs to take the headers

Picture1.png

Not applicable

Re: Trying to load an excel file with a title row above the column heading row

ok - i am attaching the 1st 3 rows of the original file - row 1 has the title running from col a to col k, then the real column headings are in row 2, but when i try embedded col headings or number of header line or garbage row, all i end up with is col a and all the other columns /fields are fxx and no data - if someone can tell me how to attach a file, happy to do that but can't see how to do that here in this forum ...

inbound order details.jpg

Not applicable

Re: Trying to load an excel file with a title row above the column heading row

i cannot read it in as a delimited file - only excel - when i remove the top row through the scripting wizard, this is the script i end up with

and this is what the list boxes look like when trying to display the info - as you can see, only column a's data is being loaded

inbound order list boxes.jpginbound script.jpg

Not applicable

Re: Trying to load an excel file with a title row above the column heading row

if you can tell me how to attach a small sample of the spreadsheet, will gladly do that - thanks

lironbaram
Honored Contributor II

Re: Trying to load an excel file with a title row above the column heading row

hi

i used the attach file

and this is the script

LOAD A,

     B,

     C,

     D,

     E,

     F,

     G,

     H,

     I,

     J,

     K

FROM

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Not applicable

Re: Trying to load an excel file with a title row above the column heading row

ok - with more playing around, if i make a copy of the file, everything works, if i copy the sheet everything works, it's just that the file as it comes out of the generating software is making it unreadable to qlikview - might it have something to do with biff - can i change that to anything else to read the file as it is generated, without having to change it every time?  thanks

Not applicable

Re: Trying to load an excel file with a title row above the column heading row

i see you have ooxml - all i see is biff - how do i get ooxml?

Not applicable

Re: Trying to load an excel file with a title row above the column heading row

ok - figured out how to add an attachment - here is the original file which does not work for me - thanks