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
rohit214
Creator III
Creator III

then u have to change a=1 to 4

thanks

rohit

Not applicable

i want automatically,without changing in script. How do i do ???

Not applicable

Hi All,

If you want to load all the sheets from all excel files in a folder, the below code should do the work for you.

Let us assume that the folder is "C:\temp" and you need to load all the sheets from each of these files. The script is below.

for each File in filelist ('C:\Temp\*.xls')

  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'), chr(39));

     Data:

     LOAD * FROM $(File) (biff, embedded labels, table is $(sheetName)) where right('$(sheetName)',1) = '$';

  NEXT

  DROP Table tables;

next File

Let me know whether this works for you.

Best Regards,

Sajeevan

Not applicable

Hi ,

im using 'xlsx' format then how do i that ???

Not applicable

Hi Sajeevan,

         I did, its working perfectly.

Thanks,

Antony.

Not applicable

Hi Antony,

Did it work on "xlsx" or "xls" format?

If it was on "xlsx" what changes did you make to the script? Please share this.

Thanks,

Sajeevan

Not applicable

Hi Sajeevan,

               I have one query, if i want to load latest excel file in my application then how do i do ?

Example :

Data_Sep.xlsx;

Data_Oct.xlsx;

Data_Nov.xlsx;

I need to load Data_Nov.xlsx, if it is Dec month then it should load Data_Dec.xlsx.

Thanks,

Antony.

rohit214
Creator III
Creator III

hi saleevan

your code working fine but i am not able to understand

sheetName = purgeChar(peek('TABLE_NAME', i, 'tables')

why you use this

can  you please explain this in detail..

or is there any other solution for that line so i can use in place of that

thanks &regards

rohit

Not applicable

Hi Rohit,

PurgeChar will delete the unwanted characters.

Peek will return the fieldvalue from TABLE_NAME field in table call 'tables'.

You can always refer to the help file to understand these better.

Why are you insisting that you want to use another solution - is there a reason?

Regards,

S A J E E V A N

rohit214
Creator III
Creator III

hi  sjeevan

thak you for explainig me all those thing

regards

rohit