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

You can add '$(sheetname)' as Year in the load script.

Not applicable

Sorry to bother u again & again Sir,

Since i've a cross table with form Crosstable(A,B,7) Load col1,col2........col9, col10,col11 from excel file;

where shud i add this ?

if i add this in script then is it correct to have as:

Crosstable(A,B,8) Load  col1,col2,'$(sheetname)' as Year........col9, col10,col11 from excel file;

   i've also tried:

load *,

'$(sheetname)' as Year resident data;

drop table data;

But failed, plz tell what to do

Regards

Not applicable

You can use the below load statments to get the sheetname as Year field.

for each file in FileList('C:\Temp\*.xlsx')

    ODBC CONNECT TO [Excel Files;DBQ=$(file)];
tables:
SQLTABLES;
DISCONNECT;

    for i = 0 to NoOfRows('tables')-1
      let sheetname = purgeChar(peek('TABLE_NAME', i, 'tables'), '$');
      data:
      CrossTable(Month, Quantity, 2) // Hear control the number of Qualifier fields - i.e. 2 means, Salesman and Year are qualifier fields and rest are the crosstable
      LOAD Salesman,
       $(sheetname) as Year, // Here you call the sheetname as Year
       Jan,
       Feb,
       Mar,
       Apr,
       May,
       Jun,
       Jul,
       Aug,
       Sep,
       Oct,
       Nov,
       Dec
FROM '$(file)' (ooxml, embedded labels, table is $(sheetname));
    next i
    drop table tables;   
next file

Regards,

Sajeevan

Not applicable

I don't have words thank you, it worked fine.

Now Sir, actually i'm loading the table as:

Tab1:

Crosstable(A,B,7) Load col1,col2........col9, col10,col11 from excel

Tab2:

Crosstable(C,D) Load col1,col2,col3 from excel

Since i've two crosstable, i'vetwo questions

1) How to join these two crosstables in the script to have one table (QV doesn't allow me to directly use the Join prefix before Tab2).

2) How to modify the script you suggested for this scenario of two Crosstables

Regards

Not applicable

Sir,

Ques. 2 is solved

I just copied & pasted the script for Tab2, & its has fetched the rows but the problem lies with the $(sheetname) as Year, if i use it again in this script it generates a synthetic key (Year+the common Col1).

Is there a way to avoid this,still linking this table data with Year.

& yes the general problem 1 remains to be solved.

Regards

Not applicable

Can you attach the screenshot of your table. Press Ctrl+T to go to the table layout.

You can join the two tables by

Fianl_Table:

Load * Resident Tab1;

concatenate

Load * Resident Tab2;

Drop Table Tab1;

Drop Table Tab2;

Regards,

Sajeevan

Not applicable

Dear Sir,

When i do this then due to drop statements Tab1 & Tab2 are lost & also Final_Tab is also not there in the Layout Diag, thus all the expressions are lost

Regards

Not applicable

Then, can you attach a screenshot of your original table structure and loading statements?

Not applicable

Ok,

here are the diags before & after the resident

Not applicable

I can't find the picture. I need the screenshot of table structure before combining the resident tables. Use the advanced editor and attach the picture or paste the picture.