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: 
bismart
Creator
Creator

Load all sheets in all Excel documents

I have a number of Excel docs in a folder with naming convention of FileName_YYMMDD.

The problem is that the docs may contain a different number of sheets i.e. Doc1 has Sheet1/Sheet2

while Doc2 has Sheet1/Sheet2/Sheet3 etc.

I need to create a table of Doc name and Sheets belonging to that Doc.

Then I want to load the sheets from their associated docs in a loop statement.

Anyone any ideas?

Thanks

66 Replies
Not applicable

Sir, plz refrsh the page

Not applicable

Can you paste your loading script so that I can check where it is creating the synthetic key?

If you can attach the excel files, it would have been easier to fix the issue

Not applicable

Sir, here's my script & the excel is also attached, now i think u can solve the problems of Join crosstable & load across sheets 

Regards

Not applicable

Can you explain why you are loading two crosstables from same sheet and then join it together? What is the purpose?

Not applicable

Sir,

First of all tab shud be Crosstable(A,X,9) instead of Crosstable(A,X,7)

as u can see from the two crosstables i need last 4 cols to be clubbed as A, their data as X

in Tab2: U & UA are to be clubbed into C & their values in D

Am i doing anything wrong, is their a simpler solution

REGARDS

Not applicable

Thanks for the clarification, Please add the below script to append the two tables together.

Final_Table:
LOAD *, '1' as Temp Resident Tab1;
Concatenate
LOAD *, '2' as Temp Resident Tab2;

DROP Table Tab1;
DROP Table Tab2;

If you need an inner join then use the below script

Final_Table:
LOAD *, '1' as Temp Resident Tab1;
inner Join LOAD * Resident Tab2;

DROP Table Tab1;
DROP Table Tab2;

The point is - you need to create a dummy field in the final table - i.e. '1' as Temp

Hope this is what you wanted to achieve

Regards,

Sajeevan

Not applicable

Thanks a lot Sir,it did what i needed. No synthetic key as well.

But Sir, i'm not able to understand the role of the dummy field.Could u throw some light on it.

Also Sir, when in future the data is added for Feb, Mar etc. then would it be ok to simply schedule the reload thru Windows Scheduler or there are any changes reqd. in the script as well.

Thanks

Not applicable

Hi Sanjeevan,

i have one question regarding the cross table, if in a excel if i have  three different sheet (exp: Australia, Janpan, China) and the sheets contain same fields but different values and along with the values i also want to upload the sheet name as a separete filed (in the cross table '$(sheet)' as country is not working) can you

Not applicable

i think u shud remove the quotes (' ') from $(sheet)

Regards

Not applicable

Can you please send your load script to check where the change in load script needed?