5 Replies Latest reply: May 24, 2012 6:10 PM by Rinalldo Yasahardja RSS

Loading particular excel sheets into Qlikview

Rinalldo Yasahardja

Hello, I am a total newbie, I am learning QlikView.

 

I just have questions when it comes to loading data in Qlikview (I can't find this information in the tutorial documentation)

 

I have excel file with multiple worksheets, for simplicity, lets call them Sheet1, Sheet2, Sheet3

I am trying to load the files into Qlikview (I have multiple excel files, but they are all in similar format)

 

Currently, i just load them all using myfolder/*.xls

 

the questions I have:

1. Does Qlikview automatically load all data from each sheet in all my excel files?

2. How do I specify, for example, if I just wanted to load Sheet1 and Sheet2? or just Sheet1?

 

Thank you.

Aldo

  • Loading particular excel sheets into Qlikview
    Roland Kunle

    Hi Aldo,

     

    1. no QV does not load all excel - sheets automatically.

    2a. You can define a variable (for ex. implicit in a loop) similar to this:

     

    for i = 1 to 3

    LOAD

    . . .

    (ooxml, embedded labels, table is Sheet$(i));  // the $() is for using the variable

    NEXT i

    ;

     

    2b. sth like this:

    for Each vSheet in 'Tabelle1', 'Tabelle2'

    LOAD

    . . .

    (ooxml, embedded labels, table is $(vSheet));

    NEXT vSheet

    ;

     

    HtH

    Roland

    • Re: Loading particular excel sheets into Qlikview
      Rinalldo Yasahardja

      Hello Roland, thank you for your reply

       

      I made test file, 2 excel files in a folder, each containing Sheet1 and Sheet2.

       

      I tried to load them using the following but I got error (Unknown file format specifier:table is Sheet$(1))

      all I tried to do is only to load data from Sheet1 from both excel files

       

      Did i do something wrong?

       

      LOAD

      Date,
      Month,
      Customer ID
      FROM
      [Data\Traffic\*.xlsx]
      (
      ooxml, embedded labels, table is Sheet$(1));

       

       

       

      • Loading particular excel sheets into Qlikview
        Roland Kunle

        ok,

        I will try to prezice your exam:

        use a LOOP like this, as I wrote in my post under 2b)

         

        for Each vSheet in 'Sheet1', 'Sheet2'  // now we defined a variable called vSheet

        LOAD

        Date,
        Month,
        Customer ID
        FROM
        [Data\Traffic\*.xlsx]
        (
        ooxml, embedded labels, table is $( vSheet));  // here we use the contents of vSheet via $()-Operator

         

        NEXT vSheet ;  // looping

         

         

        HtH

        Roland

      • Loading particular excel sheets into Qlikview
        Roland Kunle

        Hi Aldo,

         

        did recognize your star '*' in the filename too late. For this you will need a second loop. Outer loop is going for every file and inner loop is going for every specified table. Check your manual for using (contents of) variables via $()-Operator.

        Examinate this code snippet:

         

        for each vFile in filelist ('Data\Traffic\*.xlsx');  // outer (first) loop

         

        FOR Each vSheet in 'Sheet1', 'Sheet2'  // inner loop

        LOAD A

        FROM  $(vFile)  // containts filename and path, one per loop(vFile)

        (ooxml, embedded labels, table is $(vSheet));

         

        NEXT vSheet;

        NEXT vFile;

         

        Regards

        Roland

        • Loading particular excel sheets into Qlikview
          Rinalldo Yasahardja

          Hello Roland, thank you very much for your reply

           

          turns out different excel format (pre-2007) can cause problem in some syntaxes, at least in the qlikview version installed in my desktop

           

          but generally, your advice was helpful :-)

           

          for example, i played around with the sheet name and change it into "sheet one"

           

          and when i loaded it, i had to use (ooxml, embedded labels, table is [sheet one]);

          when i just wanted to load that sheet one table

           

           

           

          hope that helps anyone else too