topic Re: Column names are coming like @1 @2 @3 in QlikView

Column names are coming like @1 @2 @3

mishraamit2485 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I was trying to load data from XLSB fle format, for that I had created ODBC connection as conventional file wizard wont work here.

First i created the qvds(select * from XLSB file no transformation) and then loaded the data in qvw, while reading the data from qvw i deleted some header columns from the qvd like below.

Remove(Col, Pos(Top, 1))

Remove(Col, Pos(Top, 2))

My problem is now all the column names are coming like @1 @2 @3 after loading.

Format of my XLSB is like.

Header 1	Header 2	Header 3	Header 4
Crap Header
Crap Header
Account Number	Date	State	City

attached the sample app and qvd(note we must create the odbbc connection to read the xls)

Re: Column names are coming like @1 @2 @3

sunny_talwar — Tue, 14 Jun 2016 01:39:57 GMT

May be something need to fixed when you convery XLSB into a qvd. What is the script you are using there?

Re: Column names are coming like @1 @2 @3

mishraamit2485 — Tue, 14 Jun 2016 02:11:56 GMT

I'm doing select * from XLSB file and storing the same in qvd, no transformation or logic.

Re: Column names are coming like @1 @2 @3

sunny_talwar — Tue, 14 Jun 2016 02:18:02 GMT

Does the script include something like this?

(ooxml, embedded labels, table is Sheet1);

(ooxml, no labels, table is Sheet1);

Make sure it says embedded labels.

Disclosure: I have never loaded from XLSB, so not really sure how it works

Re: Column names are coming like @1 @2 @3

mishraamit2485 — Tue, 14 Jun 2016 02:20:34 GMT

it is like

Select *

----

'Connectionstring'.'1$'

1$ is my tab name of my xls.

Re: Column names are coming like @1 @2 @3

sunny_talwar — Tue, 14 Jun 2016 02:24:30 GMT

I guess I don't really know how it can be done. May be someone who has worked with it can offer better help.

Apologize for not being able to help

Re: Column names are coming like @1 @2 @3

mishraamit2485 — Tue, 14 Jun 2016 02:25:43 GMT

Re: Column names are coming like @1 @2 @3

tamilarasu — Tue, 14 Jun 2016 03:06:59 GMT

Please share your full script.

Re: Column names are coming like @1 @2 @3

mishraamit2485 — Tue, 14 Jun 2016 03:08:21 GMT

it is there in qvw..that is all...if you open the qvd file you will things are messed up.

Re: Column names are coming like @1 @2 @3

mishraamit2485 — Tue, 14 Jun 2016 03:20:41 GMT

Or can we write some where clause where it reads data from rowno 4.

Note: Where rowno>4 is not working here.

Re: Column names are coming like @1 @2 @3

tamilarasu — Tue, 14 Jun 2016 03:21:48 GMT

Hi Amit,

Instead of transformation step, you can try like below,

Tab1:

LOAD F1,

F2,

F3,

F4,

[Crap Row]

FROM

TAB1.qvd

(qvd) where Len(Trim(F1))>0;

Here, F1 is a Column name. You can adapt the name as per your requirment.

Re: Column names are coming like @1 @2 @3

mishraamit2485 — Tue, 14 Jun 2016 03:34:15 GMT

Nagaraj, while generating the qvd itself i want to remove the crap rows and want data with headers(ABC,...).

Without that qvd is coming with everything and I have to delete those rows(under transformation tab) manually....and the worst part is my column names(ABC..) are not coming in QVD.

Re: Column names are coming like @1 @2 @3

tamilarasu — Tue, 14 Jun 2016 04:14:13 GMT

Amit,

I tried your script but I am not able to see headers rows. Strange.

Re: Column names are coming like @1 @2 @3

mishraamit2485 — Tue, 14 Jun 2016 04:24:25 GMT

yes exactly that is what happening, now if you load this qvd you wont get headers names...you will get @1 @2...

Very strange problem

Re: Column names are coming like @1 @2 @3

Anonymous — Tue, 14 Jun 2016 10:05:50 GMT

Hi Amit,

With your example files:

For Excel file:

LOAD ABC,

EFG,

XYZ

FROM

[Test-1.xlsx]

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

For QVD File:

LOAD F1 as ABC,

F2 as EFG,

F3 as XYZ,

FROM

[..\Downloads\TAB1.qvd]

(qvd)

WHERE Len(F1)>0

;

Regards!