CSV import from excel - matching custom fields

[Jan Šimek]

Hi everyone,

since we are moving our ticket database to JIRA Workmanagement (JIRA service management did not worked for us), I have a task to move all tickets to JIRA. 

1) I need to prepare the Excel file for CSV and changed the header names according to fields in JIRA.

At this step I have quite an issue, because I am unsure and was not able to find any information regarding how JIRA works with matching dates from excel to lets say Custom Field - SELECT LIST SINGLE CHOICE or DATE TIME PICKER or LABELS.

What I am unsure about is, that if for example I will have in excel sheet "ISSUE" if JIRA will be able to match it and select it in SELECT LIST SINGLE CHOICE Custom field or not? Or what will she do? Same with DATE TIME PICKER and LABELS?

I understand it should import values from Excel, but I am unsure in which format or what will happen.

Can you please help me on this?

Thank you

[Trudy Claspill · Accepted solution]

Hello @Jan Šimek 

Have you reviewed this document?

https://support.atlassian.com/jira-cloud-administration/docs/import-data-from-a-csv-file/

[Jan Šimek]

Pretty good, thanks!

[Jan Šimek]

@Trudy Claspill I have tried to upload the CSV file as per the documentation in the link, but I probably have something wrong somewhere, because everytime I tried to make field mapping it shows me only one field with all info inside.

Do you have any clue why is it happening and what is wrong maybe?

Thank you

[Trudy Claspill]

From the image you provided it appears that you are using a semi-colon ";" rather than a comma "," between the fields. If that is the case you need to change the delimiter that the import process is looking for.

[Jan Šimek]

@Trudy Claspill Oh Thanks, that saved me, I have finally been able to progress, unfortunately now I have issue with date pattern.

In my excel file I have this date pattern:

05.11.21 09:53

Unfortunately if I am choosing pattern ason screenshot it always says:

Unable to parse Date.

It upload date, but it does it as for example July 10, 21 10:55

Any idea what should I change for it to be in format 01.05.21 12:55?

Because I have tried already few times few options, but nothing worked for me.

[Trudy Claspill]

Try

dd.MM.yy HH:mm

[Jan Šimek]

Nope, still the same.  It does it even with the "default form" 

[Jan Šimek]

@Trudy Claspill I have tried to lookup for any other issue or question regarding this and it seems nobody ever wondered why it does not work. But according to https://docs.oracle.com/javase/1.5.0/docs/api/java/text/SimpleDateFormat.html#number it does not seems possible. Even though I am positive it works, because our programmer in IT department is using Java and it works for him.

[Jan Šimek]

@Trudy Claspill Ok I have found the issue. CSV was in format yyyy and not yy, so I changed it and it was absolutely the same.

The issue is that the system has some set date/time format and you have to change it in Global Configuration/Advanced settings as well as in Look and Feel under Date/Time Formats.

[Trudy Claspill]

I'm glad you found a solution.

I was able to use the format I suggested above within the CSV import process without changing the date/time format settings for my Jira instance.

[Jan Šimek]

Hmm how did you manage to do it? :-) I wonder.

[Trudy Claspill]

These are the date/time format settings for my instance.

[Jan Šimek]

Yeah I believe that is by default, I have the same on my side. So how did you managed to import the CSV with dd.MM.yyyy HH:mm?

[Trudy Claspill]

The date format I entered in the Import wizard was:

dd.MM.yy HH:mm

The date format for my dates in the CSV file was:

05.11.22 09:53

I also tried this and it worked.

dd.MM.yyyy HH:mm

05.11.2022 09:53

[Jan Šimek]

JIRA is getting weird, because we have decided to use the default system date pattern, but even though CSV file is done correctly and uploaded, JIRA changes the date to 2 days back and 3 minutes back. No idea how did she achieve that, but I have no clue what is happening, do you have any tips?

[Trudy Claspill]

I do not have any idea why that would be happening.

I recommend that you have a Jira Admin open a case directly with Atlassian Support to investigate this more.

https://support.atlassian.com/contact/#/

[Jan Šimek]

I did not find any solution for that as well, but I have changed format again to the previous one and it loaded up with what I need, RN that is sufficient for me enough.

The problem, that I am having is now due to numbering of the tickets, because before I had them all together so the number are now separate in two projects. Meaning before I had TDNOC-1, TDNOC-2 etc, now I have in one project TDNOC-1 which is named by system TDNOC-1, but then I have for example TDNOC-3, which system named TDNOC-2 etc, so it is a mess.

Do you have any clue how to upload the tickets according to their name?

Thanks

[Trudy Claspill]

I am not sure what problem you are trying to solve with your last question.

The "name" you are referencing appears to be the issue key. Issue Keys are made of the Project Key (TDNOC) and a sequential number that is assigned automatically by the Jira system as each new issue is created in the project.

Generally speaking you cannot control the number portion of that key. The next available number within that project is used automatically, based on the highest number used so far in the project.