Due Date CSV

[Corinna Führing]

Hello we have created the Due Date for the CSV Import in following format

DAIENTRY-643,,Automatic Driver Recommendation (ADR),Feature Dependency,Traffic Information,cfuehring,,2015-05-06,E21.0,FL TTS,,

But after Import another Date is shown why.......

[Phill Fox · Accepted solution]

here is a version of your test file but saved with the extension .txt rather than .csv. The JIRA Import does not worry about the extension. This file has the format yyyy-MM-dd.

[Corinna Führing]

i did not get what you told about saving it as txt but i will make a new try opening my here attached csv file with editor windows and than save it...date appears as you saw it too and than i will give Jira yyyy-MM-dd

[Phill Fox]

@Corinna Führing
Ok lets step back and work through this one step at a time.
From your comment above you have a layout as follows.
DAIENTRY-643,,Automatic Driver Recommendation (ADR),Feature Dependency,Traffic Information,cfuehring,,2015-05-06,E21.0,FL TTS,,

So if you run the csv importer and set the date format to be yyyy-MM-dd (note the different case for Months mm means minutes.)

image2016-1-6 0:4:17.png

This should then import your file using that date format.

[Corinna Führing]

I will now try an import with that file again. I checked that if i introduce a Date in the Editor following format appears 10:11 AM 1/6/2016 so I wonder if your format in JIRA will work out–very confusing everything Thanks

[Phill Fox]

Ok so your format is not 2015-05-06 but is 10:11 AM 1/6/2016 so the date format you need to use on the mapping is hh:mm a MM/dd/yyyy assuming that the date in your example is 6th January and not 1st June

[Corinna Führing]

ok ill try agaon i just checked our first solution , import works perfect but agian wron date 2014-12-28 appears...maybe new format is solution thansk

[Corinna Führing]

Unable to parse date: 2015-05-06 ).;

[Phill Fox]

Corinna, Could you please attach the first 5 lines of the file you are trying to import, so that I can confirm the format I have given you matches. As I am now confused as to what date format you are trying to import. Also please indicate which part of the date is month and day.

[Phill Fox]

Hi Corinna Führing

Dates are frequently challenging as there are so many configuration choices you can make. So from your example you were expecting this to be imported as 6th May 2015 or 5th June 2015? And what date did you actually get?

Once you work out the format that is correct for your configuration it will then be possible to upload and correct using the same values.

[Corinna Führing]

Hello thanks. i wanted it to be the 05-05-2015 but I actually got the 28-12-2014

[Corinna Führing]

JJJJ-MM-TT; this is the format of the Date in the Excel file, but later on we exported it to a csv file and it was text and than into Jira

[Phill Fox]

That is strange. I can not spot an obvious correlation between those two dates 28-12-2014 and 05-05-2015. I have just tried an import on my local development instance and that wanted dates in the format YYYY/MM/DD which is year then month then date. This might be worth a try with a single record to see what you get.

[Corinna Führing]

we are sitting in Germany here....but JJJJ-MM-TT hh:mm should do the same ?

[Phill Fox]

Hi @Corinna Führing In principle this is the same date/time stamp but without knowing what configuration settings are on your JIRA instance it is difficult to be able to say it will be treated the same. I would personally create a short import file with a variety of different date formats until I find the one that works for JIRA. You can then match your excel format to that and export.

[Corinna Führing]

uf Ok i will keep on trying..i try with englisch Date Format now...

[Corinna Führing]

Hy i changes my System Format to English (USA) could you give me a hint how to format my Date Cell now ? so it will work ?

[Corinna Führing]

how can this be so xxx dificult i am putting the Jira Date Format now in my Excel shee but still unable to parse the due date..it seems when exporting Excel File into csv File it looses my Date Format. because if I go back standard Format is put again on my Date Cells ....

[Corinna Führing]

i feel when i save my excel file as csv file i can not keep my Date Format...however if I save is as a normal Excel File the new format is recognized and keeps as this

[Phill Fox]

So if we work on the challenges you face. 1. The correct format for your JIRA configuration. 2. The export from Excel. If we focus with 1 to start with. As I suggested earlier take one of your existing CSV files and edit the top 5 or so entries with different date formats and remove all the other records and then import to see which date format is accepted. Then with regard to the Excel export we can tell which format we need to export as.

[Corinna Führing]

Hy thanks i did nothing was working....my look and feel configuration for Complete date is following YYYY-MM-dd HH:mm

[Corinna Führing]

but whatever i am doing, after closing Excel File Date Format or Format is set back to Standard.....c ant keep my Date Format

[Corinna Führing]

now i did it with this file and it worked perectly !!!!!!!but i have no clue anymore how i get this file and what i did..how can i create this files out of my excel sheets ???

[Phill Fox]

Great news that this worked for you and you now have a working approach which confirms imports to JIRA are working. So what we need to do now is work through your export from Excel to CSV to understand what format (or possibly formats) are being exported from Excel. So if you now go into your Excel spreadsheet and save it as CSV then open in Editor you should be able to see the format in your file.

[Corinna Führing]

ok i am creating now a nex excel file with the import test dates- First question if I fill in due date now...how shall i do.. all the cells are formated Standard up tp now

[Phill Fox]

Ok for simplicity sake I would retain the standard formatting for now and only create 3 or 4 lines of items. Then save as csv. Open the csv in Editor (not Excel) and confirm the format of the date columns. Then load this in to JIRA setting the date format to match what you saw in Editor (NOT what you see in Excel). Confirm these are loaded correctly and then continue with all the other items.

[Corinna Führing]

ok i ll do but i think than i have the same problem as before since i have no txt file than....that s the way i did before i think..i will open with editor you see i the screenshot up here

[Corinna Führing]

06.05.2015 this is how my date now appears in editor i will proceed to import dd.MM.yyyy

[Corinna Führing]

oh my godness it worked finally.........my good but honestly why c ant say highlight in Jira Guide that you should adapt date format tin Jira to editor look ..i the end it s so easy ?!!!Thanks so much for your pacience and Support !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[Phill Fox]

Congratulations on getting it uploaded. Your problem seemed to stem from the reformatting that Excel did when it reopened your CSV files. Along with getting to grips with the different date formats. As we now have this challenge answered could you accept the answers?

[Corinna Führing]

For sure..thanks again..sure you are working for Atlassian University ?? Have a great day !!!!!!Mine got much better now!

[Phill Fox]

HI Corinna, I work for Adaptavist but we do deliver a lot of the Atlassian University training courses. Glad to help make your day better.

[Corinna Führing]

Editor.jpgi am opening it with this editor i have windows excel 2013

[Phill Fox]

I am not familiar with Editor so this may be your text editor but Wordpad will allow you to see the file content without changing any of the display format. I would avoid Excel as it changes your display (as you have seen) and if you save from Excel it will alter your base file as well.

[Corinna Führing]

and my date i want to have finally is six of may of 2015

[Corinna Führing]

i open this file with editor not with excel

[Phill Fox]

Ok in this example you have 2015-05-06 representing 6th may 2015 and so the date pattern for this is yyyy-MM-dd

[Corinna Führing]

ok i ll try

[Corinna Führing]

wait if I open this file now on my comp i see date as 5/6/2015 ??

[Corinna Führing]

in the editor did you see it as 2015-05-06 in the editor ?

[Phill Fox]

The file you supplied showed up as 2015-05-06 in my text editor. Did you open in Excel or a text editor? Excel has a habit of reformatting dates to your "Preferred" format but it does not alter the underpinning date format.

[Corinna Führing]

in my editor it shows up as 5/6/2015 ok but anyhow am i right in understanding: a) i do not care about excel format in cells i just open csv file with editor and look at date format i see there in my lines b) than in import wizard i change the date according for 5/6/2015 (meaning 06.05.2015) i will put MM/dd/yyyy and than it should work....?!

[Phill Fox]

I am beginning to think that your issue is related to the display method of your different tools not matching the underpinning data. So if you can confirm that the format in the csv file you are using is 5/6/2015 then MM/dd/yyyy is the correct format.

[Corinna Führing]

yes but as I said if i press F5 in my editor system gives my following Data for today....11:36 AM 1/6/2016

[Phill Fox]

Your test file that you attached looks like this. Parent ID,Issue ID,Summary,Issue Type,Component,Reporter,Priority,Due Date,FixVersion,Doors Feature List Name,Assignee DAIENTRY-643,,Automatic Driver Recommendation (ADR),Feature Dependency,NavCtrl,cfuehring,,2015-05-06,E21.0,FL TTS,, DAIENTRY-643,,Automatic Driver Recommendation (ADR),Feature Dependency,Map Compilation,cfuehring,,2015-05-06,E21.0,FL TTS,, DAIENTRY-643,,Automatic Driver Recommendation (ADR),Feature Dependency,Route Guidance,cfuehring,,2015-05-06,E21.0,FL TTS,, DAIENTRY-643,,Automatic Driver Recommendation (ADR),Feature Dependency,Traffic Information,cfuehring,,2015-05-06,E21.0,FL TTS,, and the date format here is yyyy-MM-dd

[Phill Fox]

What text editor are you using to look at your csv files? Notepad in windows? TextEdit on a Mac? None of these should alter the underpinning format. Note you may need to override the default editor for csv files when you open the file. Try changing the .csv to .txt on your filename to help pick a text rather than csv editor.

[Corinna Führing]

ok i opened the file now again but from this page so i have your look, if i open it with editor it appears what you say if i open it with excel it appears as 5/6/2015 BUT if i open my source file for this attached file here with editor it appears as 5/6/2015 in editor.... so it looks like it changed somehow....no i download the file from this site and import it inn Jira again as you said

[Corinna Führing]

Due Date.jpgby the way: this is how my due date of Parent ID appears , different from System Dates