It's all about the answers!

Ask a question
QuestionsTagsUsersBadges
Follow this question
Question details

×42,787

question asked: Jun 30 '10, 10:12 a.m.
question was seen: 7,579 times
last updated: Oct 16 '13, 3:39 a.m.

Related questions

FAQ - How this Forum works

date format in csv export

1
Marco Bormann (161●19●10) | asked Jun 30 '10, 10:12 a.m.
Is there a way to determine the format of timestamp-typed columns in csv exports? The only influence I found is are the regional settings on the server. Is there any way to determine the output?

Accepted answer

1
permanent link
Ryan Davidson (115●1●6●11) | answered Oct 18 '12, 2:16 p.m.

Answer to my own question...

For Eclipse you need to edit the Eclipse.ini file in the same folder as your Eclipse.exe. You need to add the following 2 lines under the '-vm' flag:

-Duser.language=en

-Duser.region=GB

I had tried this, but was using -Duser.region=UK which didn't work.

with the GB the date outputs to screen as d Mmm yyyy (instead of 'Mmm d, yyyy') and exports to CSV as yyyy-mm-dd which is recognised by Excel as a date. Happy Days.

Ralph Schoon selected this answer as the correct answer

3 other answers

Most liked answers ↑|Newest answers|Oldest answers

1
permanent link
Marco Bormann (161●19●10) | answered Jun 30 '10, 10:41 a.m.
I found a solution that works: using the Internal Value for output in the output wizards sets the date format to YYYY-MM-DDtHH:MM:SS:MMMh
1
permanent link
Ryan Davidson (115●1●6●11) | answered Oct 18 '12, 8:58 a.m.
edited Oct 18 '12, 9:02 a.m.

Can I re-open this topic...

I have recently changed to a Win 7 machine running Eclipse Client v3.0.1.1.  My work item queries now produce dates in format 'Mmm d, yyyy' (e.g. Oct 11, 2012...).  Exporting to CSV yields the date in the same format... this is not recognised as a date by Excel and thus interpreted as a string... highly annoying.

I've tried fiddling with Locales via the eclipse.ini file to no avail. (Changing language from en_US to en_UK worked to solve this problem in the Browser client).

As such I'm pretty much unable to perform meaningful exports from my Eclipse Client on this machine where I have to depend on the date columns.

My Regional settings are ignored - the 'Mmm d, yyyy' format is not evident anywhere in my system config.

Connecting to the same project area via Eclipse from a different machine yields output date formats that are recognisable by Excel (generally yyyy-mm-dd formats).

Can anyone advise how I can configure the Eclipse Client to determine the format that dates display on my Work Items and in my exports?

(P.S. The above solution does not work for me... Excel doesn't recognise YYYY-MM-DDtHH:MM:SS:MMMh either due to the 't' in the middle and 'h' at the end).

Thanks

RD

1
permanent link
Rob Logie (338●2●50●41) | answered Oct 16 '13, 12:43 a.m.
edited Oct 16 '13, 12:46 a.m.
This nasty excel formula might give you what you need ....
It will convert strings like "Oct 13, 2013 11:09 AM" or "Oct 3, 2013 8:05 AM" into an excel time stamp.  It could be modified to support other text date formats that excel does not support out of the box.

In this example the text date value from the CSV file is in cell G2 and the formula converting to a excel date/time value is in cell H2. The formula find the relevant strings by looking relative to known fixed characters. This is to try and take into account the variable length of some of the strings like day in month etc.

The formula is...

=DATEVALUE(MID(G2,FIND(" ",G2)+1,FIND(",",G2)-FIND(" ",G2)-1)&" "&LEFT(G2,3))+TIMEVALUE(MID(G2,FIND("~",SUBSTITUTE(UPPER(G2)," ","~",3))+1,FIND(":",G2)+2-FIND("~",SUBSTITUTE(UPPER(G2)," ","~",3)))&" "&RIGHT(G2,2))

It pulls the date and time out of the text field then converts it into formats that the excel datevalue and timevalue functions can understand

Just reformat the field as the appropriate date/time format to see excel display the date / time correctly
.
To help understand it, it can be broken down a bit ..

Month =  =LEFT(G2,3)

Day = =MID(G2,FIND(" ",G2)+1,FIND(",",G2)-FIND(" ",G2)-1)

Year =  =MID(G2,FIND(",",G2)+2,4)

Time =  =MID(G2,FIND("~",SUBSTITUTE(UPPER(G2)," ","~",3))+1,FIND(":",G2)+2-FIND("~",SUBSTITUTE(UPPER(G2)," ","~",3)))

AM/PM =   =RIGHT(G2,2)

These functions extract the relevant bits of the date/time out of the string in the csv export. Time was the most difficult bit to extract. I wish excel had regular expression support !!. The strings are then concatenated together in the final excel function to allow excel to convert it into a excel date/time stamp.

If anyone has a simpler excel formula please post it !
Comments
Ryan Davidson commented Oct 16 '13, 3:39 a.m.

Thanks Rob... a handy formula to handle that date format.

My intent was to avoid the need to code a formula, however. Far easier to change the Eclipse Client settings to result in a date format that can be auto-interpreted by Excel and will thus work on every export without the need to re-introduce the formula.

That being said I like the thought that's gone in to it. (I'm a bit of an Excel fan by the way... :-) )

Your answer

Register or to post your answer.


Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.