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: 
alec1982
Specialist II
Specialist II

concatenate excel files and add null if field doesnt exist

I have 300 excel files and trying to concatenate. the columns I need lets say are A, B, C, D,E,F. some of these excel dont have some of these fields so I want to add null if it doesnt exist. 

I tried to create an empty table using inline and then concatenate the 300 files.

T1:

Load * inline [A,B,C,D,E,F]; and then 

concatenate (T1)

load * from *.xlsx 

the problem here is that the 300 tables have other random fields (each one has different set) so I ended up having over 2000 fields.. 

how do I load only the fields I need and just add null if the field doesnt exist?

4 Replies
Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

how about after loading all excels having one extra resident load  and drop table, like:

 

NoConcatenate
T2:
Load
A,B,C,D,E,F
Resident T1;
Drop Table T1;
cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
alec1982
Specialist II
Specialist II
Author

I actually tried that but didnt work. as the number of columns on the first load get extremely big the tool freeze and then error out.

Gabriel
Partner - Specialist III
Partner - Specialist III

Hi,

My 1st question is, do you need all the 2000 fields?

If you don't need them all, best practice says, only load fields needed for performance reasons.

 

 

marcus_sommer

If the files are so heterogenous is it really sensible to concatenate all of them? To load them in a classical way you will not only know which fields are there else also which do you need from them and which kind of data contain the fields especially could equally fields contain different content or reverse and also what are invalid data. Therefore I'm not sure if it's possible just to apply a rather generic approach ...

I wouldn't be surprised if your files are in reality cross-tables - if so it you should load them with a crosstable-statement. Even if it are not really cross-tables you may consider to load the files in this way.

- Marcus