Unable to use Jira csv import for date with May and October

[Moez SAID]

Hello,

When I use the dd/MMM/yy format in the import configuration file, all dates are recognized by JIRA except those of May and October
Example 01/May/21, JIRA shows this error message "Unable to parse date: 01/May/20"
The same problem occurs with the month October
Please note that the import works fine if I replace "May" or "Oct" with any other month. Exemple (01/Apr/21)
I need to import dates in this format (dd/MMM/yy) because my import file (it's an export from another JIRA instance) contains dates of the same format in the user comments columns as well as in the attachments columns, and I have to adapt the date fields (created, updated, Due, ...) to this format to avoid updates after the creation import.
Do you have any idea about this problem?

Thanks in advance!

Best regards

[Drew Kerrigan]

I've seen the import work with any datetime format using the SimpleDateFormat: https://docs.oracle.com/javase/1.5.0/docs/api/java/text/SimpleDateFormat.html

However it always says:

"...completed with warnings..."

"Unable to parse datetime..."

The only datetime format that I have seen it accept and not throw any errors with, is this:

M/d/yyyy H:mm:ss

[Radek Dostál]

Excel can be deceiving, because it is trying to know better than the user and change their data to "personalize" their experience.

There isn't any known bug or problem with specific months (there is with 'Target start' and 'Target end', but those are specific Roadmaps fields), the problem is likely somewhere in the sheet.

Are those any special fields by any chance? As long as you are using system fields and date/datetime custom fields, it should work just fine. If the fields are some 3rd party plugin custom field types, then that might be a problem in the plugin itself.

Have you tried to open the sheet and explicitly change the format back and forth? Maybe that Excel keeps different formats in the background that look the same, but in fact are not. Maybe refreshing them will do the trick - it certainly won't hurt.

[Moez SAID]

Hello Radek Dostál,

Thanks for your quick feedback and support!

This problem occurs with issue fields and custom fields (I don't have any date fields provided by a plugin)

I applied your suggestion as follows

I tried to modify explicitly the dates in the csv file
1st test:
I explicitly put "25/Apr/19" in the created, updated, resolved and Due date fields

Created	Updated	Resolved	Due Date
25/Apr/19	25/Apr/19	25/Apr/19	25/Apr/19

-->The dates are imported correctly

2nd test
I modify in the same file the same cells "Apr" by "May

Created	Updated	Resolved	Due Date
25/May/19	25/May/19	25/May/19	25/May/19

Note that, in the mapping phase of the field values I checked that the values are correctly interpreted with the desired format
But when importing I get the following message

Import completed with 3 warnings:

• Unable to parse created date: 25/May/19
• Unable to parse datetime: 25/May/19
• Unable to parse date: 25/May/19

In the imported ticket these fields take the operation date ((import date) as values

Jira v8.13.17

Thanks again!

With best regards.

[Radek Dostál]

Would it be possible for you to send me the sheet data so I can try to import it locally? Of course - not the full sheet, maybe just those 2 rows, one that works, one that doesn't, no extra data or summaries needed, just the dates, I'll fill in the blanks.

I could create it, but I suspect there's something in that sheet.. from what you're saying it sounds that it should work, I did a good amount of date imports in my days but never had a problem with specific months, it was always a cell format.

Generally I just select the whole column minus header and right-click to specify the format for all Date fields, save, and then re-use that format in the mapping. So I don't know, sounds odd, just want to try it for myself if I can reproduce it with your data - might as well spin up 8.13.17 to get the same version.

[Moez SAID]

Hi Radek Dostál,

I don't know how to join the files (only phots accepted). But if you want to reproduce the problem, you can try an import of a simple ticket with data of type dates on the formats, the months and the JIRA version in question.
For the formatting I use a macro to put in unique format dates because the export file contains different formats of dates on the same column. Something that is already weird sur JIRA
The capture below corresponds to an export csv current fields

Thanks!

Best regards

[Radek Dostál]

Okay, let's see what else it could be..

Have you tried opening the file in notepad? That's what Jira will work with, since we know that Excel can be deceiving, csv really is just comma delimited text, so notepad - doing no personalization - should confirm we can rule out the date format. (Source: https://confluence.atlassian.com/jirakb/dates-are-not-properly-imported-from-csv-file-245825977.html)

I know you checked that already, but please try the notepad so we can be absolutely certain that Excel is not pulling our noses after all. That's what I wanted to do first with the file - read it and print all characters to ensure it reads the same in plain text.

Jira also has it's own configurable date formats (Andy covered those here: https://community.atlassian.com/t5/Jira-questions/Wrong-dates-trough-CSV-import/qaq-p/950163), but that wouldn't explain why it's failing with specific cells/months only - it would presumably fail with all of them if this were the case. So I don't think that's the case.

What else comes to mind.. maybe month names conflict due to languages? I don't know the internals here and so I am reaching a little bit, but how does it match for example "Abc" to January - if you were Abcedian living in Abcedia - I assume it must map month names according to some language. Now does it work with Jira default language, or your own profile language. Maybe that could be worth to check:

 - seems that you are using English locally, is your profile in Jira also set to use English?

Because it imports dates in relation to your profile selected time zone, I assume we can discard "default Jira language", but hey, won't hurt to rule it out, never know what you find these days.

That one should be in 'System -> General configuration'; listed as 'Default language'.

Lastly I'd try to change the format to a numerical value, e.g. 'dd/mm/yy' and that way we can confirm that it really specifically has problem with the month names - and not the values or format. And again this you could probably try with just those 2 rows. And as you mentioned you need the same format due to comments - well let's see if anything on this list budges first. Aren't comments already in numerical month format though - at least the KBA's always show them in numerical.

[Moez SAID]

Hi,

I created a csv using the notepad text editor, here are the lines

"

Issue Type;Summary;Status;Reporter;Assignee;Resolution;Created;Updated;Resolved
Story;Story1;To do;username;username;Done;01/Apr/20;01/Jan/20;01/Feb/20
Story;Story2;To do;username;username;Done;01/Oct/20;02/May/20;01/Feb/20
Story;Story3;To do;username;username;Done;01/May/20;03/Oct/20;01/Jan/20
Story;Story4;To do;username;username;Done;01/Jan/20;01/Feb/20;01/Jan/20

"

The result is negative on my side. Jira indicates :

"

1 projects and 4 issues imported successfully!

Import completed with 4 warnings:

• Unable to parse created date: 01/Oct/20
• Unable to parse created date: 02/May/20
• Unable to parse created date: 01/May/20
• Unable to parse created date: 03/Oct/20

"

As you can see, we have the same problem with the two months May and October (it's OK with Jan, Feb and Apr)

Importing these same lines works fine using the month numbers with a dd/mm/yy mask. But this is not the desired format as I initially indicated because the dates in the comments and attachments use the month name dd/MMM/yy
I think I can confirm now that it is not a problem of date formatting but maybe related to the names of the months and precisely May and Oct. (at least according to the tests I made)

Can you confirm by giving it a try on your side please?

Note that on my JIRA profile I use the same default language of JIRA (English (United States) [Default]) and the Time Zone

Thanks!

With best regards.

[Radek Dostál]

I installed fresh 8.13.17 and created a test project.

I took your latest sample you sent and simplified it to avoid having to map couple more things - but I kept the dates as is:

Specifying 'dd/MMM/yy' format, and saving it:

Then I went to External System Import - CSV, and selected my project with the date format:

Next, I selected the field mapping:

And hit next.

It imported successfully all green and healthy like:

2022-03-16 15:07:43,470 INFO - Import started by admin using com.atlassian.jira.plugins.importer.imports.csv.CsvDataBean
2022-03-16 15:07:43,480 INFO - ------------------------------
2022-03-16 15:07:43,480 INFO - Importing: Users
2022-03-16 15:07:43,480 INFO - ------------------------------
2022-03-16 15:07:43,480 INFO - Only new items will be imported
2022-03-16 15:07:43,482 INFO - 1 users associated with import. All of them imported as inactive, this can be changed after import in User Access step.
2022-03-16 15:07:43,482 INFO - ------------------------------
2022-03-16 15:07:43,482 INFO - Finished Importing : Users
2022-03-16 15:07:43,482 INFO - ------------------------------
2022-03-16 15:07:43,483 INFO - 0 users successfully created.
2022-03-16 15:07:43,483 INFO - Retrieving projects...
2022-03-16 15:07:43,483 INFO - Project ExternalProject{id='null', key='PLEASEWORK', externalName='null', name='PLEASEWORK'} exists and found correctly.
2022-03-16 15:07:43,513 INFO - ------------------------------
2022-03-16 15:07:43,513 INFO - Importing: Versions
2022-03-16 15:07:43,513 INFO - ------------------------------
2022-03-16 15:07:43,513 INFO - Only new items will be imported
2022-03-16 15:07:43,514 INFO - ------------------------------
2022-03-16 15:07:43,514 INFO - Finished Importing : Versions
2022-03-16 15:07:43,514 INFO - ------------------------------
2022-03-16 15:07:43,514 INFO - ------------------------------
2022-03-16 15:07:43,514 INFO - Importing: Components
2022-03-16 15:07:43,514 INFO - ------------------------------
2022-03-16 15:07:43,514 INFO - Only new items will be imported
2022-03-16 15:07:43,514 INFO - ------------------------------
2022-03-16 15:07:43,514 INFO - Finished Importing : Components
2022-03-16 15:07:43,514 INFO - ------------------------------
2022-03-16 15:07:43,514 INFO - Retrieving custom fields...
2022-03-16 15:07:43,514 INFO - ------------------------------
2022-03-16 15:07:43,514 INFO - Importing: Issues
2022-03-16 15:07:43,514 INFO - ------------------------------
2022-03-16 15:07:43,514 INFO - Only new items will be imported
2022-03-16 15:07:43,521 INFO - Importing issue: [externalId='autoid-4919546142195173930', summary='Story1']
2022-03-16 15:07:46,035 INFO - Importing issue: [externalId='autoid-382168982274481651', summary='Story2']
2022-03-16 15:07:46,138 INFO - Importing issue: [externalId='autoid-2486989220453749399', summary='Story3']
2022-03-16 15:07:46,274 INFO - Importing issue: [externalId='autoid-6971751266694227683', summary='Story4']
2022-03-16 15:07:46,677 INFO - Reindexing last 4 issues imported ...
2022-03-16 15:07:47,136 INFO - Reindexing took 298 ms.
2022-03-16 15:07:47,136 INFO - 4 issues successfully created
2022-03-16 15:07:47,136 INFO - ------------------------------
2022-03-16 15:07:47,136 INFO - Finished Importing : Issues
2022-03-16 15:07:47,136 INFO - ------------------------------
2022-03-16 15:07:47,136 INFO - ------------------------------
2022-03-16 15:07:47,136 INFO - Importing: Issue Links & Subtasks
2022-03-16 15:07:47,136 INFO - ------------------------------
2022-03-16 15:07:47,137 INFO - Only new items will be imported
2022-03-16 15:07:47,143 INFO - ------------------------------
2022-03-16 15:07:47,143 INFO - Finished Importing : Issue Links & Subtasks
2022-03-16 15:07:47,143 INFO - ------------------------------
2022-03-16 15:07:47,144 INFO - No issues need to be reindexed.

Next I verified this by viewing the issues - all looks correct on issue view, and the stored value is correct also (Story3 as example since it has both May and Oct):

{
"expand": "renderedFields,names,schema,operations,editmeta,changelog,versionedRepresentations",
"id": "10002",
"self": "http://localhost:2990/jira/rest/api/2/issue/10002",
"key": "PLEASEWORK-3",
"fields": {
"updated": "2020-10-03T00:00:00.000+0200",
"created": "2020-05-01T00:00:00.000+0200"
}
}

Since it's fresh install the configuration is nothing special (and same as my account here):

Default language : English (United Kingdom)
Default user time zone : System default: (GMT+01:00) Prague
 // ^ local time, therefore server time since it's localhost

I'm sorry, I don't know why it's not working for you - I don't have any more ideas other than the format or language, and I can't try to fish for something in the source code to make a guess why it fails to parse your data, that would be too folly.

As a workaround, I think you might do the import in 2 different rounds - albeit you would need some extra mapping to do that.

Import 1 - dates with numerical format

Import 2 - comments/attachments/etc. which you otherwise can't really map well

I would say that if you sort your .csv by Summary (ASC), save the .csv, then Jira will import this one by one, top to bottom. Once it is imported, you can find the created issues and 'ORDER BY Summary ASC' - this way you get the exact same order as you imported those issues. Then if you do a .csv export, you can then match this with your original sheet, and:

 - add 'Key' (matched by summary in your new export file, should be row to row)

 - remove Date fields, just keep those dd/MMM/yy specific ones

 - do the import again, this time with key mapping

 - and this should, therefore, create what you couldn't import previously, by updating the issues

(And again, assuming that the import works for those comments/attachments - I think you said it did and it was just the date fields failing?)

It's a pretty terrible workaround, but.. well, last resort. Or perhaps, have you tried asking around if somebody else is able to import it on your instance? Maybe they could get luckier.

In any case - I don't know what else to try without having your exact environment/data (which you generally don't want to share over the internet anyway), all I can really confirm is it should work. I'd probably try the double-import as a workaround in short-term, if time is important to have it done soon, but something really weird is going on with your imports.

[Moez SAID]

Hello Radek Dostál,

I thank you very much for your quality of support and your reactivity!
It's strange that it doesn't work for me, I use the same import file and the same format, and for the same issue field (created for example) all the months work correctly except the MAY and OCT one... tested by two other admins of our team, I even installed the English laungue on my computer, but still the same result
On my side, and as the quantity of data I have to import is huge, I used another format (yyyy-MM-dd HH:mm) and I developed a script to adapt the date to the new format in the import file including comments and attachments; this way all the dates are imported correctly at once
Again, thanks for trying it on the environment in question.

With best regards.