topic Re: Loading Excel data into QlikView in QlikView

Loading Excel data into QlikView

Mon, 03 Aug 2009 22:28:46 GMT

Hi,

I think I have a simple data loading problem. I have an .xls file exported from our bug tracking tool "Jira". You can find the file as attatchment.

Now I am trying to load all the data into my QlikView application, but I was not able to do this.

Details:

I don't need the first 3 rows from the .xls file. The 3 columnames should be "Issue Type", "Key", and "Issue Links"

And I also don't need the last row of the excel output but this is not so important.

I hope somebody could help me.

Thanks.

Regards,

Herbert

Loading Excel data into QlikView

markmccoid — Tue, 04 Aug 2009 00:54:21 GMT

Hi Herbert,

When you are in the edit script mode, click on the "Table Files" button to get the wizard for loading excel files.

In your example you said you didn't want the first three rows or the last.

The following code is what the wizard produced:


LOAD [Issue Type],
 Key,
 [Issue Links]
FROM DELETE.xls (biff, embedded labels, table is general_report$, filters(
Remove(Row, Pos(Top, 2)),
Remove(Row, Pos(Top, 1)),
Remove(Row, RowCnd(CellValue, 1, StrCnd(contain, 'Generated at')))));

When the wizard opens and you choose your Excel file, click on the "Transform" button and you can select rows to always delete, like the first three and you can create a conditional delete, so for the last row the conditional delete says to delete and column with "Genereate at" in it.

Hope this helps.

Mark

Loading Excel data into QlikView

Tue, 04 Aug 2009 14:13:15 GMT

Hi,

Thanks for your help. But I think I haven't explained my problem enough. I got nearly the same code as you, but the thing is, I can't use the excel output from "Jira", it is not recongnized as a excel file in the script editor wizard. I can only use it when I open it before manually within Excel and convert it to a normal excel file. I think markmccoid also have converted into a right excel with the name DELETE.xls

Please look at the screenshots. The thing is this that everything should be automatically, the file is saved automatically somewhere and qlikview imports it than automatically, but it should be not like this that I must convert the excel format.

I don't know exactly which type of excel output it is, a little bit strange.

Is it also possible to import the data without change of the rare data file?

Regards,

Herbert

Loading Excel data into QlikView

Wed, 16 Dec 2009 01:42:59 GMT

Having the same problem. It's xls files but formatted as .csv or .txt and I get a Execution of script failed. Reload old Data? Error every time.

Anyone have a solution how to get qlikview to parse these "wrongly" formatted .xls files?

Loading Excel data into QlikView

Wed, 16 Dec 2009 02:20:16 GMT

It looks like it saves it as HTML, so you can select that in the wizard dialog. No file re-saving is required first. The wizard will generate something like this:

LOAD [Issue Type],

Key,

[Issue Links]

FROM

Testdata.xls

(html, codepage is 1252, embedded labels, table is @2);

Your data is in table @2, if you want the header block it's in table @1.

Re: Loading Excel data into QlikView

Tue, 18 Mar 2014 16:13:16 GMT

hi,

I was wondering if you have an example of the qlikview app you were creating for JIRA data.

I know this was a long time ago, but i'm trying to do something similar now with our on-site JIRA installation.

Anything you can provide will be gratefully received

thanks in advance,

Matt

Re: Loading Excel data into QlikView

Mon, 25 Aug 2014 18:30:08 GMT

Hi,

I have the same question as Matthew Johnson. Please update if there is any way to connect JIRA data directly or through third party into Qlikview. We have been currently exporting from JIRA and then importing into Qlikview. It becomes too tedious as we can't do live reporting.

Appreciate any help on this topic.

Thanks

Regards,

Janaki Venkitasubramanian

Re: Loading Excel data into QlikView

Mon, 25 Aug 2014 20:28:48 GMT

HI Mathew,

Not sure if this method would be the way to go, but you can try connecting JIRA to MySQL. By doing this you can download the issues, tasks etc data into MySQL and connect to Qlikview to load data and create reports using the OLE DB connection. I haven't tried it.

Connecting JIRA to MySQL - JIRA 6.3 - Atlassian Documentation

Let me know if you get a chance to test this.

Regards,

Janaki Venkitasubramanian

Re: Loading Excel data into QlikView

Wed, 10 Sep 2014 19:34:20 GMT

Hi Herbert,

Did your problem solve with regards to importing data into Qlikview from JIRA?

Regards

Janaki Venkitasubramanian

Re: Loading Excel data into QlikView

Thu, 11 Sep 2014 13:26:45 GMT

My issue was really how to query the sprint markers.

I have odbc/OLEDB connectivity to our JIRA instance - so can see the data, but the schema is tricky to understand and certain things look like they are not logged in the database in a relational way.

i'd be very interested to see anyone else's SQL/QVWs that they have created for that data

thanks,

Matt

Re: Loading Excel data into QlikView

raviityou — Thu, 16 Oct 2014 18:41:55 GMT

i have excel file which is abnormal (Data is not it proper manner)

it has empty cell, marge,... etc

the how to get data from it.

Re: Loading Excel data into QlikView

Fri, 20 Feb 2015 18:08:59 GMT

Hello Janaki,

You may try to use QlikView API Connector for Jira

Best regards,

Roman.