Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rafael5958
Creator
Creator

load excel columns

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  

1    1    1

FEb.xlxs

A    B

2    2  

2    2

  

MARCH.xlsx

C    D

3    3

3    3

QLIKVIEW RESULT

A    B    C    D  

1    1    1    -

1    1    1    -

2    2    -    -

2    2    -    -

-    -    3    3

-    -    3    3

1 Solution

Accepted Solutions
tamilarasu
Champion
Champion

Hi Rafael,

Have a look at the attachment.

View solution in original post

7 Replies
Jason_Michaelides
Luminary Alumni
Luminary Alumni

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

Chanty4u
MVP
MVP

PLEASE look into attached sample qvw.

rafael5958
Creator
Creator
Author

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    -

1A   1B    1A    -

2A   2B    -    -

2A   2B    -    -

-    -    3C    3D

-    -    3C    3D

tamilarasu
Champion
Champion

Hi Rafel,

Data:
LOAD '' as Temp AutoGenerate 0;


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

DROP FIELD Temp;

Anil_Babu_Samineni

1) just use concatenate in between directories

2) final table is

Directory load * from table1, table2,...

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
tamilarasu
Champion
Champion

Hi Rafael,

Have a look at the attachment.

rafael5958
Creator
Creator
Author

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.