Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Looping through Excel Sheets and Columns

Hi,

I have our company budget in the 'prescribed' tabular format of account number in Column A; and each period of 2013 extending from columns B thorugh M

Each sheet is a department which I need to tag and reference later.

My basic script on edited is.

LOAD F1 as Account,

     January,

     February,

     March,

     April,

     May,

     June,

     July,

     August,

     September,

     October,

     November,

     December,

     Year

FROM

(ooxml, embedded labels, table is [R - 50040]);

I was thinking that at very least I would need to do something along the lines.

LOAD F1 as Account,

     January as Budget,

     '01' as Period,

     '50040' as Department

FROM

(ooxml, embedded labels, table is [R - 50040]);

LOAD F1 as Account,

     February as Budget,

     '02' as Period,

     '50040' as Department

FROM

(ooxml, embedded labels, table is [R - 50040]);

I have 92 sheets with 12 months in each - even recycling the code via copy and paste etc would be poor design (at best).

I am actually looking to expand my knowledge and best practice rather than just create a working but downright ugly script.

I would welcome contribution please and alternate smarter approaches.

Many thanks,


Rob

1 Solution

Accepted Solutions
MVP
MVP

Re: Looping through Excel Sheets and Columns

Rob

There are a couple of steps here.

First of all, to decompose the tabular structure, use the CrossTable keyword. This will transform the multiple columns into two fields, one containing the column name (ie month) and one containing the cell value. Use the cross table wizard to set this up. For example:

CrossTable(Tenor, Data, 3)

LOAD [Funding Area],

     [Sub Funding Area],

     [Total Limit],

     ON,

     [1M],

     [3M],

     [6M],

     [12M],

     [2Y],

     [5Y],

     [10Y],

     [>10Y],

     indtmt

FROM [.......xlsx]

(ooxml, embedded labels, table is [.....]);

The result table will have the columns: [Funding Area],  [Sub Funding Area],  [Total Limit], Tenor, Data. Tenor will contain the values 'ON', '1M', '3M' etc, and Data will contain the corresponding values from the fields [ON], [1M] etc.

Now to loop over files and sheets. Use a script similar to this:

For Each zFile In FileList('$(zFilepath).xlsx')

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

    SpreadsheetData:

    SQLTABLES;

    DISCONNECT;

       //.... subfield zFile here to analyse components for dates etc

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

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

         //.... subfield zSheet here to analyse components for dates etc

        Data:

        LOAD ...

                   ...

            FileBaseName() As SourceFile,

            '$(zSheet)' As SourceSheet

        FROM [$(zFile)]

        (ooxml, no labels, table is [$(zSheet)]);

    Next

    DROP Table SpreadsheetData;

Next

The outer loop iterates over all the files matching the filepath in FileList. The ODBC connection gets the sheet names with the SQLTABLES command and returns the list of sheetnames in the table SpreadsheetData. The inner loop iterates over all the sheets in the file.

If you need to get information such as dates from the file or sheet names, you can break them up using subfield and extract the information. The comments show where you could do this. When I do this type of load, I like to know where each row came from, so I add the SourceFile and SourceSheet fields. If the sheets all contain the same structure, they should auto concatenate in Data, creating a single table.

Hope the gets you started

Jonathan

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

Re: Looping through Excel Sheets and Columns

If I have understood you have a lot of sheets containing same data (columns name);

If this is exact, try to define a variable that conatins sheets name and then cycle on the variable,

you can read (with peek) the variable from a table for instance

MVP
MVP

Re: Looping through Excel Sheets and Columns

Rob

There are a couple of steps here.

First of all, to decompose the tabular structure, use the CrossTable keyword. This will transform the multiple columns into two fields, one containing the column name (ie month) and one containing the cell value. Use the cross table wizard to set this up. For example:

CrossTable(Tenor, Data, 3)

LOAD [Funding Area],

     [Sub Funding Area],

     [Total Limit],

     ON,

     [1M],

     [3M],

     [6M],

     [12M],

     [2Y],

     [5Y],

     [10Y],

     [>10Y],

     indtmt

FROM [.......xlsx]

(ooxml, embedded labels, table is [.....]);

The result table will have the columns: [Funding Area],  [Sub Funding Area],  [Total Limit], Tenor, Data. Tenor will contain the values 'ON', '1M', '3M' etc, and Data will contain the corresponding values from the fields [ON], [1M] etc.

Now to loop over files and sheets. Use a script similar to this:

For Each zFile In FileList('$(zFilepath).xlsx')

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

    SpreadsheetData:

    SQLTABLES;

    DISCONNECT;

       //.... subfield zFile here to analyse components for dates etc

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

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

         //.... subfield zSheet here to analyse components for dates etc

        Data:

        LOAD ...

                   ...

            FileBaseName() As SourceFile,

            '$(zSheet)' As SourceSheet

        FROM [$(zFile)]

        (ooxml, no labels, table is [$(zSheet)]);

    Next

    DROP Table SpreadsheetData;

Next

The outer loop iterates over all the files matching the filepath in FileList. The ODBC connection gets the sheet names with the SQLTABLES command and returns the list of sheetnames in the table SpreadsheetData. The inner loop iterates over all the sheets in the file.

If you need to get information such as dates from the file or sheet names, you can break them up using subfield and extract the information. The comments show where you could do this. When I do this type of load, I like to know where each row came from, so I add the SourceFile and SourceSheet fields. If the sheets all contain the same structure, they should auto concatenate in Data, creating a single table.

Hope the gets you started

Jonathan

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

Re: Looping through Excel Sheets and Columns

Many thanks - much space saved :-)

Community Browser