topic Re: How to load multiple excel files which has different field names? in QlikView

How to load multiple excel files which has different field names?

Wed, 18 Feb 2015 12:52:55 GMT

Hi all,

I need to load multiple files from the specific folder.

It is easy to load multiple files with the same set of field names. But the challenge here I am facing is to load different field names.

Scenario explained below,

File1:

ID,Name,Address

1, abc , 1st main road

File2:

ID,Name,Addressline

2, def , 2nd main road

The number of the files in the folder will be varying on daily basis. So every day the dashboard should reload the from all the files which are available.

Could any one please help me out on this?

Thanks & Regards,

Karthikeyan A R.

Re: How to load multiple excel files which has different field names?

simenkg — Wed, 18 Feb 2015 12:57:50 GMT

Will they always have the fields in the same order?

If so, then you can use Explicit labels.

LOAD A as ID,

B as Name,

C as Adress,

FROM

[../Data/*.xlsx]

(ooxml, explicit labels, table is [Sheet1]);

Re: How to load multiple excel files which has different field names?

Wed, 18 Feb 2015 12:58:59 GMT

Yes. They will be in same order but only the name differs as mentioned below...

File1:

ID,Name,Address1, Address2, Address3

1, abc, 1st main road, 2nd main road, 3rd main road

File2:

ID,Name,Addressline1, Addressline2, Addressline3

2, def, 1st main road, 2nd main road, 3rd main road

File3:

ID,Name,line1, line2, line3

3, ghi, 1st main road, 2nd main road, 3rd main road

Re: How to load multiple excel files which has different field names?

Wed, 18 Feb 2015 13:00:30 GMT

You can load multiple file using:

Load *

select*

from yourpath/*.xlsx

Re: How to load multiple excel files which has different field names?

Wed, 18 Feb 2015 13:06:01 GMT

You are correct. But the data from different files needs to be concatenated properly...

like Address1,Addressline1,line1 should be considered as same field while concatenating...

The whole idea is to automate these reports in QlikView without any manual intervention whenever the new files are placed...

Re: How to load multiple excel files which has different field names?

maxgro — Wed, 18 Feb 2015 13:14:52 GMT

PFA

Re: How to load multiple excel files which has different field names?

Wed, 18 Feb 2015 14:31:46 GMT

Hi Massimo,

Thanks for your effort to help me out.

But still in output I see three fields Filea1, Filea2 and Filea3.

I need to see only one field as Filea1 and concatenate all the values in same column.

Thanks again!!!

Re: How to load multiple excel files which has different field names?

maxgro — Wed, 18 Feb 2015 16:22:25 GMT

maybe this

Excel: load '' as field autogenerate 0;

For Each vFile in FileList('2013??_??.xlsx')

trace file=$(vFile);

ODBC CONNECT32 To [Excel Files;DBQ=$(vFile)];

Sheets:

SQLTABLES;

DISCONNECT;

// for first sheet

For i = 0 To 0

Let vSheet = left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1);

trace $(vSheet);

concatenate(Excel)

LOAD

'$(vSheet)' as [Tab Name],

A as f1,

B as f2,

C as f3

From [$(vFile)]

(ooxml, no labels, table is [$(vSheet)])

where RecNo()>1;

Next;

Next;

Re: How to load multiple excel files which has different field names?

Fri, 20 Feb 2015 15:19:54 GMT

Hi Masssimo,

Thanks for your response..

But it was bit tough to implement in the same way for me...

I have got an easy way to do this through explicit field naming in transformation step while loading the file..

Thanks everyone!!!