Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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!!

Tags (1)
7 Replies
Highlighted

Re: Multiple Excel Sheets

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

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)
Highlighted
MVP
MVP

Re: Multiple Excel Sheets

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
Highlighted
MVP
MVP

Re: Multiple Excel Sheets

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

Re: Multiple Excel Sheets

These are the Sheet Names

Highlighted
Creator III
Creator III

Re: Multiple Excel Sheets

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.

Highlighted
Creator III
Creator III

Re: Multiple Excel Sheets

Also, Attaching a sample excel.

Highlighted
MVP
MVP

Re: Multiple Excel Sheets

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