strange behavior with csv import of time values

Alex Möckel September 29, 2017

Hi,

I try to import my issues with csv import on Jira 7.3.5. It nearly works, but time fields do not work. If I import the values of the first column, I get in db the values of the second column:

     60        0
180 0
3.600 0
28.800 0
57.600 0
86.400 28.800
172.800 57.600

Can anybody please explain, how this should work?

 

Thanks

Alex

1 answer

0 votes
Andy Heinzer
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
October 11, 2017

Hi Alex,

I am confused as to the type of field here.  Jira has Date/Time custom fields that can be created, but to the best of my knowledge, native Jira does not have a specific field called just Time.  

It could be there is a plugin in your Jira instance that might provide this kind of field.  I am just curious to learn more about this specific field type first.  If we can do that, then perhaps we would have a better understanding of why this data might not be importing in the way you expect. 

Also what kind of units are represented by this data?  Are these units of time milliseconds, seconds, minutes, etc? 

Also just for clarification, is the period in your values meant to represent a separation of thousands, or is this a decimal?   I realize that different regions use that differently, I suspect that your 3.600 is meant to be three thousand and six hundred, and not three and six tenths.

Regards,
Andy

Alex Möckel October 16, 2017

Hi Andrew,

you are right, the period means  a separation of thousands, these are german delimiters. The field, I'd like to import, is this one (snippet from import-config-file):

"ursprüngliche Schätzung" : {
"jira.field" : "timeoriginalestimate"
},

The unit of this jira.field is seconds. With the jira frontend it works in that way.

Regards,
Alex

Andy Heinzer
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
October 16, 2017

Hi Alex,

Given the nature of the values, I suspect that maybe the CSV file is not formatted in the correct method.  Would you be able to share a snippet of the CSV file for us here?  I would be interested to try to reproduce this behavior.

If you would rather not post this data publicly, please let me know.  In turn I can create a support request where we can look more closely at this issue.

Regards,

Andy Heinzer
Atlassian Support

Alex Möckel October 18, 2017

Hi Andrew,

I can give you some data. First my configuration file:

{
"config.version" : "2.0",
"config.project.from.csv" : "false",
"config.encoding" : "windows-1252",
"config.email.suffix" : "@",
"config.field.mappings" : {
"Vorgangstyp" : {
"jira.field" : "issuetype"
},
"Startdatum" : {
"existing.custom.field" : "11752"
},
"ursprüngliche Schätzung" : {
"jira.field" : "timeoriginalestimate"
},
"Autor" : {
"jira.field" : "reporter"
},
"Bearbeiter" : {
"jira.field" : "assignee"
},
"Priorität" : {
"jira.field" : "priority"
},
"System" : {
"existing.custom.field" : "11753"
},
"Zusammenfassung" : {
"jira.field" : "summary"
},
"Fälligkeitsdatum" : {
"jira.field" : "duedate"
},
"Epic Link" : {
"existing.custom.field" : "11056"
}
},
"config.value.mappings" : { },
"config.delimiter" : ";",
"config.project" : {
"project.type" : null,
"project.key" : "AAE",
"project.description" : null,
"project.url" : null,
"project.name" : "AAE",
"project.lead" : "u1257"
},
"config.date.format" : "dd.MM.yyyy"
}

and these are my data:

Projekt;Vorgangstyp;Zusammenfassung;Priorität;Fälligkeitsdatum;Bearbeiter;Autor;ursprüngliche Schätzung;System;Startdatum;Epic Link
AAE;Aufgabe;Test Zeitimport 1 Minute (000060);mittel;31.10.2017;u1257;u1257;000060;IT-Allgemein;01.10.2017;AEOrg
AAE;Aufgabe;Test Zeitimport 1 Minute (000180);mittel;31.10.2017;u1257;u1257;000180;IT-Allgemein;01.10.2017;AEOrg
AAE;Aufgabe;Test Zeitimport 1 Stunde (003600);mittel;31.10.2017;u1257;u1257;003600;IT-Allgemein;01.10.2017;AEOrg
AAE;Aufgabe;Test Zeitimport 8 Stunden (028800);mittel;31.10.2017;u1257;u1257;028800;IT-Allgemein;01.10.2017;AEOrg
AAE;Aufgabe;Test Zeitimport 2 Tage-16h (057600);mittel;31.10.2017;u1257;u1257;057600;IT-Allgemein;01.10.2017;AEOrg
AAE;Aufgabe;Test Zeitimport 1 Tag -24h (086400);mittel;31.10.2017;u1257;u1257;086400;IT-Allgemein;01.10.2017;AEOrg
AAE;Aufgabe;Test Zeitimport 2 Tage-48h (172800);mittel;31.10.2017;u1257;u1257;172800;IT-Allgemein;01.10.2017;AEOrg

The leading 0 are my laste test-step, if there are leading 0 or not has no influence to the result.

Regards
Alex

Andy Heinzer
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
October 19, 2017

Hi Alex,

Thanks for all this information.  However, so far, I have not been able to reproduce the behavior you mentioned originally.  In my setup, I was able to import these 7 issues successfully and in my import the Estimated Time (or ursprüngliche Schätzung) what correctly imported to each of these.

I should note that in my system, when I first tried to import these issues using the system encoding of windows-1252 (as defined in your config file), I got a number of unexpected errors where Jira could not import certain fields.  However it still appears to have imported the issues with the time estimations. 

The errors I saw during the import appear to be a limitation of that specific encoding does not correctly represent characters that contain umlauts.   But I think that error is actually expected.  Jira typically is expecting the character encoding to be in a UTF-8 format, whenever possible.  This tends to avoid such problems between parsing this data from a text file and in turn how this then gets stored to a database. 

With Jira we also have to contend with the database type and its specific encoding and the table collations in use, as well as the character encoding in Java.  In my test I used a Postgres 9.4.x database that was using a C encoding.  I was also able to confirm my Java JVM for Jira was using UTF-8 for encoding.

I would be interested to see if perhaps JIRA Application internationalisation and encoding troubleshooting might be helpful in making sure that both your Java JVM and your Jira database are setup to use character encodings that Jira supports.   I would recommend setting the startup parameter of "-Dsun.jnu.encoding=UTF-8" as that article describes. 

And I would also be interested to learn more about your specific environment to see if perhaps your database for Jira is setup with an unsupported collation.  If you are an admin in Jira you can navigate to Cog Icon -> System -> System Info to see most of this information  or /secure/admin/ViewSystemInfo.jspa

The database encodings/collations are expected to be different between Postgres/mysql/MS SQL/Oracle database types, but it can be very easy to setup a Jira instance with one of these unsupported collation/encoding types and not see problems with data in Jira right away.

Regards,
Andy

Suggest an answer

Log in or Sign up to answer