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