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

I still don't know why but when I create a new field in the new table it stays otherwise it dissappear when drop the original tables.

You can schedule the reload, it will update.

Not applicable

Hi Sajeevan,

The script is as below: 

for each sheet  in 'Australia', 'China', 'Japan'

CrossTable(Months, Data, 11)

LOAD hier,

     class,

     ATC3,

     [ATC 4],

     crp,

     [Merge crp],

     inprd,

     molecule,

     FDC,

     [Dec-10],

     [Jan-11],

     [Feb-11],

     [Mar-11],

     [Apr-11],

     [May-11],

     [Jun-11],

     [Jul-11],

     [Aug-11],

     [Sep-11],

     [Oct-11],

     [Nov-11],

     [Dec-11],

     '$(sheet)' as Country

FROM

(ooxml, embedded labels, table is $(sheet));

next;

Not applicable

You need to move the '$(sheet)' as Country from the end. Find below the changed script.

for each sheet  in 'Australia', 'China', 'Japan'

CrossTable(Months, Data, 11)

LOAD hier,
     class,
     ATC3,
     [ATC 4],
     crp,
     [Merge crp],
     inprd,
     molecule,
     FDC,
     '$(sheet)' as Country,
     [Dec-10],
     [Jan-11],
     [Feb-11],
     [Mar-11],
     [Apr-11],
     [May-11],
     [Jun-11],
     [Jul-11],
     [Aug-11],
     [Sep-11],
     [Oct-11],
     [Nov-11],
     [Dec-11]
FROM

(ooxml, embedded labels, table is $(sheet));

next;

Regards,

Sajeevan

Not applicable

hi Sajeevan,

the script is uploaded successfully, but still i cant able to get the field "Country"

Not applicable

Please check the attached files

Not applicable

hi, Iam using PE version of the QV so cant able to open your sample file

Not applicable

Find below the load script I have within my sample file. Save the excel file in C:\Temp and paste the below script and load it.

for each sheet  in 'Australia', 'China', 'Japan'

CrossTable(Months, Data, 11)

LOAD hier,
     class,
     ATC3,
     [ATC 4],
     crp,
     [Merge crp],
     inprd,
     molecule,
     FDC,
     '$(sheet)' as Country,
     [Dec-10],
     [Jan-11],
     [Feb-11],
     [Mar-11],
     [Apr-11],
     [May-11],
     [Jun-11],
     [Jul-11],
     [Aug-11],
     [Sep-11],
     [Oct-11],
     [Nov-11],
     [Dec-11]
FROM

(ooxml, embedded labels, table is $(sheet));

next;

Not applicable

thanks sajeevan now its came, there was a extra column in my excel, thaanks alot, can i ask for a small advice from you, i am a new bee in QV can you suggest me how to improve my skills i know its come by experience bt still any suggestion from your side

Not applicable

I mainly use QlikCommunity to improve my skills by trying to solve the issues the community members have or try the solutions posted in the community. I am coming from a data processing background hence can visualize the database structure. I have experience in restructuring the database to make the analysis easier.

QlikCommunity is the best source to upskill yourself.

Regards,

Sajeevan

Not applicable

K thank alot