Line Break in Excel Field Header

[#1]

I have a problem in loading an Excel file as QV doesnt parse the headers. I guess it is because of the line breaks in the headers like this one:

Project
Id

QV reads it as:

Directory;
LOAD [Project
Id],

and gives error.

Manuel correction in the Excel file is not one of the solutions in my case.

Thanks.

[rbecher]

Hi,

maybe you can use:

LOAD @1 as [Project Id], ...

- Ralf

[#3]

Hi Necmeltin

can you attach the file for i have a look at it?

regards

christian

[#4]

No doubt it will work. A better solution may handle the changes in the order of the headers.

[rbecher]

@1 was wrong..

You could load the header line first and analyze the record for changes. Then, maybe use variables to set the field names. After that, load verything from second row:

LET vField1='Project_Id';

LOAD A as $(vField1)
FROM
C:\buffer\header.xlsx
(ooxml, no labels, header is 1 lines, table is Tabelle1);

- Ralf

[#6]

I really wonder why QV doesnt handle any Excel field header that has a new line break in it.

[rbecher]

Maybe it's just a bug in script interpretation... I've tried some workarounds but with no success.

- Ralf

[#8]

I have found a solution like below:

let

LOAD

[$(c)] as ProjectId

FROM ...

c=('Project' & chr(10) & 'Id');

[#9 · Accepted solution]

s

orry for the not readable text above. it is just here:

let

c=('Project' & chr(10) & 'Id')

;

LOAD

[$(c)] as

ProjectId

FROM

.....

[evan_kurowski]

Not sure why this thread from 2011 is popping up in the recent queue, but..

to add comments, white-space including line breaks do work in Excel field names (but it is a horrid way to name fields).  Just be sure to match the amount of white space in the QlikView script programmatically to the amount embedded in the Excel spreadsheet.  It is a good idea to encapsulate the white-space with square braces so you know where it stops/starts.   Again though.. the worst field names ever.