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

Thanks for this Sir.

Cud u plz tell wat to do if the excel sheets are crosstables....where & how to use the crosstable prefix here.

Regards

Not applicable

Can you attach one of your excel file?

Not applicable

Sir, i'm attaching the an excel file having 3 sheets for 3 years 2009,2010,2011. As per the current problem we can also have these sheets as 3 diffrnt excel sheets in a single folder.

Regards

Not applicable

Hi,

You can use the below syntax to load all the files from a folder (e.g. "C:\Temp") and all the sheets with each file and each sheet is assumed as a crosstable structure.

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)
      LOAD Salesman,
           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

thanks for d reply......will try this & get back to u if i've any issues

Not applicable

Dear Sir,

This worked perfectly fine, but it was actually a dummy file created by me.

The actual excel file has two sheets (2011 & 2012). 2011 is fully filled with 12

mnths data while 2012 has data only for Jan.

The sheets are in crosstable format.

when i use ur script the first sheet loads fine but stucks on the 2nd sheet saying :-

Unknown file format specifier:table is '2012'_xlnm#_FilterDatabase.

Plz help

Not applicable

Can you send the excel file where you are getting this error for me to have a look at it?

Not applicable

Thanks for the reply Sir,

Actually i'm unable to share the actual data with you due to fidelity issues.

I'll try to build a dummy file again.

But where do you think the problem could be..any areas which i can rectify

Regards

Not applicable

There could be some issue with the file structure. Please open the sheet and check whether the columns are in order or not.

Not applicable

Dear Sir,

I just copied all the data from the excel file & created a brand new excel file sheet & now its working fine.

Now my next issue is how can i link the year number(2011 & 2012) to the excel sheet names & create a list box for selecting the year. Do i've to add a column for year in the sheet as well?

Regards