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: 
psk180590
Creator III
Creator III

Multiple Excel Sheets

Hallo All,

I have few Excel Sheets with multiple files with Month_Year as the Sheet Name.

How do i load all the sheets once and also save the Sheet Name as Date.

Ex: Data.xlsx has the Following Sheets with some data

Jan 12, Feb 12.....Dec 13

I want to load all the sheets and save the Sheet Name as Jan 2012, Feb 2012...Dec 2013.


Found few links in the community but somehow was not successful. Any pointers would be appraciated.

TIA!!

7 Replies
Anil_Babu_Samineni

What are these? Jan 12 and Feb 12 ...??

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
jonathandienst
Partner - Champion III
Partner - Champion III

This technique will load all the sheets from the matching files:

For Each vFileName In FileList(....)

    ODBC CONNECT32 TO [Excel Files;DBQ=$(vFileName)];

    SpreadsheetData:

    SQLTABLES;

    DISCONNECT;

  

    For i = 0 to NoOfRows('SpreadsheetData') - 1

        Let zSheet = Peek('TABLE_NAME', $(i), 'SpreadsheetData');

        LOAD .... FROM $(vFileName)

        (biff, table is [$(zSheet)]);

    Next

Next


Fill in the missing details. Hopefully that gets you going.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

To include the date modified as requested, use this code

For i = 0 to NoOfRows('SpreadsheetData') - 1

     Let zSheet = Peek('TABLE_NAME', $(i), 'SpreadsheetData');

     Let zDate = Num(Date#(zSheet, 'MMM yy'));

     LOAD ...,

          Date($(zDate, 'MMM yyyyy'),

          ....

     FROM $(vFileName)

     (biff, table is [$(zSheet)]);

Next

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
psk180590
Creator III
Creator III
Author

These are the Sheet Names

psk180590
Creator III
Creator III
Author

Hi Jonathan,

Thanks for the suggestion. I'm able to connect to the Excel folder and the script also runs successfully but, i don't see any fields after the reload.

psk180590
Creator III
Creator III
Author

Also, Attaching a sample excel.

jonathandienst
Partner - Champion III
Partner - Champion III

I suggest you post the script you used. What I supplied was a script pattern to help you get to the correct script.You need to adapt the pattern to your specific environment and requirements.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein