Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Dynamically import table from excel sheet

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.

   

12 Replies
Thorsten-Stein
Employee
Employee

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!

Anonymous
Not applicable
Author

Is this First time you are loading this excel file?

jonathandienst
Partner - Champion III
Partner - Champion III

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.

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

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.

vikasmahajan

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

Please check if helped..

Thanks

Vikas

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
Not applicable
Author

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;                   

Not applicable
Author

Hi Vikas,

Thanks for sharing above line, but it doesn't work for me. In my case I have a file ,say Transaction 02-02-2016.xlsx. This files contain data divided in table format. Sample excel files has been attached above. I want to load data from single table "Transaction 2" and leave other tables untouched.

tamilarasu
Champion
Champion

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

Not applicable
Author

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