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

[#1]

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.

[simenkg]

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,

     D,

     E,

     F,

     G,

     H,

     I,

     J,

     K

FROM

[../Data/*.xlsx]

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

[#3]

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

[#4]

You can load multiple file using:

Load *

select*

from yourpath/*.xlsx

[#5]

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...

[maxgro]

PFA

[#7]

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!!!

[maxgro]

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;

[#9]

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!!!