topic load excel columns in QlikView

load excel columns

rafael5958 — Tue, 06 Sep 2016 14:43:01 GMT

Hi, I have some data on excel workbooks and I need load it on Qlikviwer. Its only one table, but some months it comes with or without some columns, I cant specify when, but its always to put it in the same table, is there a way to load it creating the column on the select? and saying to put "-" in the cells, if the column not exists??

For example:

january.xlsx

A B c

1 1 1

FEb.xlxs

A B

2 2

MARCH.xlsx

C D

3 3

QLIKVIEW RESULT

A B C D

1 1 1 -

2 2 - -

- - 3 3

Re: load excel columns

Jason_Michaelides — Tue, 06 Sep 2016 17:15:19 GMT

Something along these lines maybe...

Data:

LOAD * FROM [Jan.xlsx]

(ooxml, embedded labels, table is [By User]);

FOR EACH vMonth IN 'Feb','Mar','Apr' etc

CONCATENATE (Data)

LOAD * FROM [$(vMonth).xlsx]

(ooxml, embedded labels, table is [By User]);

NEXT vMonth

Hope this helps,

Jason

Re: load excel columns

Chanty4u — Tue, 06 Sep 2016 17:19:59 GMT

PLEASE look into attached sample qvw.

Re: load excel columns

rafael5958 — Wed, 07 Sep 2016 00:18:19 GMT

Something like Jason put. But trying to figure it out. I have a lot of excel files, each one with only one sheet, named "combined". I'm trying to use the code from: Consolidate multiple sheets from multiple excel... | Qlik Community

its something like that Im trying to do. Sending the example xlsx.

PS: The columns in the excel files are not in order,

The output should be:

A B C D

1A 1B 1A -

2A 2B - -

- - 3C 3D

Re: load excel columns

tamilarasu — Wed, 07 Sep 2016 02:28:42 GMT

Hi Rafel,

Data:
LOAD '' as Temp AutoGenerate 0;

Concatenate (Data)
LOAD *
FROM
[Files\*.xlsx]
(ooxml, embedded labels, table is Sheet1);

DROP FIELD Temp;

Re: load excel columns

Anil_Babu_Samineni — Wed, 07 Sep 2016 03:57:28 GMT

1) just use concatenate in between directories

2) final table is

Directory load * from table1, table2,...

Re: load excel columns

tamilarasu — Wed, 07 Sep 2016 06:16:14 GMT

Hi Rafael,

Have a look at the attachment.

Re: load excel columns

rafael5958 — Thu, 08 Sep 2016 12:38:04 GMT

Thanks Tamil, and you all. Its what I wanted, actually I have xlsx files, so it was giving me error, when I changed it to xls, it ran as expected.

Many thanks.