Jira CSV import errors - Unable to parse datetime - how can I fix this?

[Rob Horan]

I am trying to do a CSV import into Jira and have several date fields in my CSV file.  Some seem to have dates and times, others just dates.

I assume this is a date format issue. When I go to import I am only given a single option for date format.  I chose the default: dd/MMM/yy h:mm a

It looks to me like I have day month year, but in all honestly this kind of syntax is not my strong suit.  Based on the information here, is the solution obvious?

Apologies if this seems like a repeat question.  I did search the community for answers, but I don't think I was able to find an exact match to this issue.

Import completed with 9 warnings:

• Unable to parse datetime: 7/21/22 17:58 using the date format pattern: dd/MMM/yy h:mm a
• Unable to parse datetime: 7/19/22 using the date format pattern: dd/MMM/yy h:mm a
• Unable to parse datetime: 6/22/22 14:18 using the date format pattern: dd/MMM/yy h:mm a
• Unable to parse datetime: 6/7/22 using the date format pattern: dd/MMM/yy h:mm a
• Unable to parse datetime: 5/13/22 11:48 using the date format pattern: dd/MMM/yy h:mm a
• Unable to parse datetime: 5/13/22 using the date format pattern: dd/MMM/yy h:mm a
• Unable to parse datetime: 4/13/22 using the date format pattern: dd/MMM/yy h:mm a
• Unable to parse datetime: 4/12/22 using the date format pattern: dd/MMM/yy h:mm a
• Unable to parse datetime: 5/13/22 11:47 using the date format pattern: dd/MMM/yy h:mm a

[Fernando Eugênio da Silva · Accepted solution]

@Rob Horan 

Apparently you only need to adjust the date format.

This is the import format: DD/MMM/YY
This is how you are using it: 9/15/22
And here is how you should use it: 15/Sep/22

Change the month identifier, instead of numbers, use the first 3 letters of the desired month.

[Rob Horan]

@Fernando Eugênio da Silva sorry but I don't understand.

I don't have data using the first 3 letters of the month.  That's not how its formatted.

I have fields formatted the way they are in the error.  I need to import the data as it is.  I don't have it any other way.  Why can't Jira accept the month/day/year format?

I get that I did it wrong, putting in day/month/year, but why now do I need text for months?  I have thousands of entries, I can't just go rewrite them manually.

[Fernando Eugênio da Silva]

Apparently this is the format that is configured in Jira. If you want to change this, you must ask your jira administrator to change the date and time settings so that jira will use the format you are trying to import.

[Rob Horan]

I solved it.  There were two problems.

1. I needed to use the format M/dd/yy h:mm
2. There is a bug (Atlassian calls it a "limitation" but its an import-breaking limitation) that prevents the import of date and datetime fields.  The workaround is bat-guano nuts.  You can see it here: https://jira.atlassian.com/browse/MIG-431

I created my own workaround since the idea of doing a second import was NOT appealing.  (Neither was using VLOOKUP, which I have no experience with)

As an alternative, I opened my CSV in a spreadsheet app and sorted by each date field so I could clump all the values for that field at the top.  Then for each field, I copied values to a text editor (I used VSCode, Notepad ++ will work if you're fortunate enough to have Windows) and recorded a macro in which I suffixed each date with a dummy time and ran it to the end, so that every date had a dummy time, and then pasted back into the spreadsheet.

Do this for every date-only field - sort, copy to the text editor, modify, copy back to the spreadsheet, move on to the following date field - until all the date fields are done, and then restore the original sort order you had if that had any importance and save a new copy of your CSV for import.

Now you'll be able to import into date pickers without error, and the time values will just be ignored.

[Fernando Eugênio da Silva]

@Rob Horan maaan, you're made a great job! hahaha

Sometimes only a workarounds fix our problems.

Congrats!