load excel columns

[rafael5958]

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

[Jason_Michaelides]

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]

PLEASE look into attached sample qvw.

[rafael5958]

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]

Hi Rafel,

Data:
LOAD '' as Temp AutoGenerate 0;

Concatenate (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,...

[tamilarasu · Accepted solution]

Hi Rafael,

Have a look at the attachment.

[rafael5958]

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.