Loading Excel data into QlikView

[#1]

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

[markmccoid]

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

[#3]

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

[#4]

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?

[#5]

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.

[#6]

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

[#7]

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

[#8]

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

[#9]

Hi Herbert,

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

Regards

Janaki Venkitasubramanian

[#10]

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