topic Line Break in Excel Field Header in QlikView

Line Break in Excel Field Header

Wed, 27 Apr 2011 10:01:27 GMT

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.

Line Break in Excel Field Header

rbecher — Wed, 27 Apr 2011 10:04:37 GMT

Hi,

maybe you can use:

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

- Ralf

Line Break in Excel Field Header

Wed, 27 Apr 2011 10:12:14 GMT

Hi Necmeltin

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

regards

christian

Line Break in Excel Field Header

Wed, 27 Apr 2011 10:14:50 GMT

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

Line Break in Excel Field Header

rbecher — Wed, 27 Apr 2011 11:47:34 GMT

@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

Line Break in Excel Field Header

Wed, 27 Apr 2011 12:07:30 GMT

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

Line Break in Excel Field Header

rbecher — Wed, 27 Apr 2011 13:22:46 GMT

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

- Ralf

Line Break in Excel Field Header

Sun, 01 May 2011 17:06:21 GMT

I have found a solution like below:

let

LOAD

[$(c)] as ProjectId

FROM ...

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

Line Break in Excel Field Header

Sun, 01 May 2011 17:10:39 GMT

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

let

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

;

LOAD

[$(c)] as

ProjectId

FROM

.....

Re: Line Break in Excel Field Header

evan_kurowski — Tue, 12 Dec 2017 13:35:56 GMT

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.