12 Replies Latest reply: Feb 3, 2016 1:54 AM by Tamil Nagaraj RSS

    Dynamically import table from excel sheet

    Priyanka Pandey

      Hi All,

       

      I have an excel datasource in which data is coming in format, attached is the screenshot. I received this excel file on daily basis and want to pull data only from table containing table name "PQR". Since, the files are changing row number of that table is also changing.

       

      So, how can I implement this in qlikview. Please help.

       

      Thanks in Advance.

         

        • Re: Dynamically import table from excel sheet
          Thorsten Stein

          Hello,

           

          'POR' should be a value of a new column for each table. So it's easy to read it with Qlikview.

           

          Hope this helps!

          • Re: Dynamically import table from excel sheet
            balraj ahlawat

            Is this First time you are loading this excel file?

            • Re: Dynamically import table from excel sheet
              Jonathan Dienst

              Please upload a sample of the spreadsheet. You can load this in Qlikview, but the details are a little complicated. The screenshot is not very useful and without a sample, you might not get any accurate answers.

              • Re: Dynamically import table from excel sheet
                Priyanka Pandey

                I am attaching a sample file for your reference.

                 

                In the attached file I have three tables with table header (Transaction 1, Transaction 2, Transaction3...) and always I want to load data from transaction 2. How will I achieve this.

                • Re: Dynamically import table from excel sheet
                  vikas mahajan

                  you want this https://community.qlik.com/thread/197094 ?

                   

                  Please check if helped..

                   

                  Thanks

                   

                  Vikas

                  • Re: Dynamically import table from excel sheet
                    Byron Van Wyk

                    Hi,

                     

                    Add this to script. Gave me what I think you looking for but as Jonathan pointed out, will need more detail if it does not as this can get a little complicated to dynamically account for

                     

                    PQRRowsTABLE:                             

                    LOAD *, RowNo() as PQRRows                              

                    FROM                        

                    [directory\commdata.xlsx]                             

                    (ooxml, no labels, table is Sheet1);                            

                                                       

                                PQRStartEnd:            

                                LOAD PQRRows as PQRStartEnd              

                                RESIDENT PQRRowsTABLE                      

                                WHERE A = 'PQR' or A = 'MNO';                

                                                       

                                LET zRowStart = Peek('PQRStartEnd',0)+2;                       

                                LET zRowEnd = Peek('PQRStartEnd',1);                

                                                       

                    PQRData:                              

                    LOAD                         

                                A          as        No,

                                B          as        Name,

                                C         as        Gender,

                                D         as        Address,

                                E          as        Phone

                    RESIDENT PQRRowsTABLE                                  

                    WHERE PQRRows >= $(zRowStart) and PQRRows < $(zRowEnd) and (not isnull(A) or A='');                           

                    DROP TABLES PQRRowsTABLE, PQRStartEnd;                         

                                                       

                    exit script;                   

                    • Re: Dynamically import table from excel sheet
                      Tamil Nagaraj

                      Hi,

                       

                      Try something like below,

                       

                      Temp:
                      LOAD
                      RecNo() as DataStart
                      From Format.xlsx
                      (ooxml, No Labels, Table is Sheet1)
                      WHERE A = 'Transaction2';
                      
                      LET vHeaderSize = Peek('DataStart');
                      DROP TABLE Temp;
                      
                      Data:
                      LOAD [Job ID]
                        ,[Job Name]
                        ,[Job Start Date]
                        ,[Job End Date]
                        ,[job status]
                       From Format.xlsx
                           (ooxml, embedded Labels, Header is $(vHeaderSize) Lines, Table is Sheet1)
                           Where Not IsNull([Job Name]) and  [Job ID] <>'Job ID' ;
                      
                      
                      

                       

                      Sample QVW file attached against your file. Let me know

                        • Re: Dynamically import table from excel sheet
                          Byron Van Wyk

                          Close but this extends to transaction 3 which OP does not want, only transaction 2. Hence why I loaded a start and end

                            • Re: Dynamically import table from excel sheet
                              Tamil Nagaraj

                              Byron,

                                       I misunderstood question. I thought the OP wants to load data from second transaction. I guess the below one should work in that case.


                              Temp: 

                              LOAD 

                              RecNo() as DataStart 

                              From Format.xlsx 

                              (ooxml, No Labels, Table is Sheet1) 

                              WHERE A = 'Transaction2'

                               

                              LET vHeaderSize = Peek('DataStart'); 

                              DROP TABLE Temp; 

                               

                              Data: 

                              LOAD [Job ID] 

                                ,[Job Name] 

                                ,[Job Start Date] 

                                ,[Job End Date] 

                                ,[job status] 

                              From Format.xlsx 

                              (ooxml, embedded Labels, Header is $(vHeaderSize) Lines, Table is Sheet1) 

                              Where RecNo()= Autonumber([Job Name]);


                              I was trying to simplify the script.

                                • Re: Dynamically import table from excel sheet
                                  Byron Van Wyk

                                  That's really clever with the where clause, I like that!

                                   

                                  Without knowing how the data is populated I wonder what would be the best approach. From a simplicity perspective, your solution requires less lines of code to get the desired result. In my solution, more lines are required but the constraint is not on any dimensional value so you always guaranteed to get just transaction 2, unless transaction 3 name changes. Likewise with the RecNo() approach, if Job Name happens to be named the same for any transaction then incorrect records would get fetched. So the two solutions both have constraints, one at a 'table name' level and the other at the field level.