2

I am new to StackOverflow, so just a heads up. Any pointers would be great :)

Anyway, I have a file that I create/use weekly in Excel 2010 that shows me date time stamps down to milliseconds. I then convert this .xslx file to a .csv. When I do, it chops off seconds. I found a solution to this originally by using this format to retain simply the seconds (not ideal, but workable):

yyyy/mm/dd hh:mm:ss.000 (.csv will chop off the .000)

Now my users are complaining about data not sorting based on the milliseconds. When I use this format, it DOES retain those milliseconds, but as soon as I convert a .csv I lose them. In Excel (.xslx), the date format will look like this:

2014/08/19 15:29:17.562

And once saved as a .csv, it looks like this:

  • In the cell: 29:17.6
  • In the formula bar: 8/19/2014 3:29:18 PM

I'd like the .csv to look like the Excel file. Any ideas? Thanks so much in advance!

Also as a frame of reference, this data gets uploaded and then sorted in an iPhone app based on the time, which is why this is important to my users. It is supposed to reflect the order in which they used the data the previous week.

Giacomo1968
  • 25,759
  • 11
  • 71
  • 103
Laurel P
  • 23
  • 1
  • 5

2 Answers2

2

I am assuming you want your .csv file values to look like this:

2014/08/19 15:29:17.562

So set the custom number format on your cells in Excel to this:

yyyy/mm/dd h:mm:ss.000

Then, when you save as .csv, your values will keep this format

Jeff Anderson
  • 799
  • 7
  • 18
  • 1
    Hi there, Thanks for the response. That is how I currently format my cells already. Once I save and close the .csv file, though, it chops off the .000 so it does not retain that formatting. It ends up looking like how I described above. – Laurel P Sep 04 '14 at 19:17
  • Not sure... I pasted 2014/08/19 15:29:17.562 into a cell, right click - format, in the number tab selected the Custom category, then under Type: I pasted yyyy/mm/dd h:mm:ss.000. Saved it as .csv and opened it in notepad, all the characters were there. – Jeff Anderson Sep 04 '14 at 21:01
  • Ah, ok. So there is miscommunication here. I mean, when I open the .csv in Excel it throws out the formatting. I'm not a developer, so I'm not sure how the database ACTUALLY uploads my .csv, but I'm fairly certain it's recognizing it as an Excel file and opens it, so once you reopen the .csv in Excel, all the formatting is gone. – Laurel P Sep 05 '14 at 19:44
  • You may find this post useful, http://stackoverflow.com/questions/804118/best-timestamp-format-for-csv-excel – Jeff Anderson Sep 05 '14 at 20:23
  • Yes, I read over that post. I guess I'll just have to really devote some time and try to figure out a new way. I'll probably end up having to split milliseconds out into a separate column or something. – Laurel P Sep 05 '14 at 21:53
1

I ran into a similar issue exporting data with millisecond timestamps into excel where it would appear to truncate the milliseconds but you could see them again by changing the display format. I didn't want to make users manually change display formats every time but still wanted to keep the data in csv format so I found that the following solution appears to work in my case:

Export your data in quotes with a space in front of the date.

An example line output from C#:

currLine= "\" "+ timestamps[i].ToString("HH:mm:ss.fff") +"\"" ;

This gives you the full timestamp that will show up like 13:45:01.234 in your excel sheet which will persist through saving and re-opening the csv file within excel.

Just be aware that if you want to do time arithmetic in excel you'll need to pass the cell values through the TRIM() function to allow it to get automatically coerced into an excel timestamp type for addition/subtraction.

For example if you have an end time in A10 and start time in A1, the function =A10 - A1 wouldn't work (gives you #VALUE error) but =TRIM(A10) - TRIM(A1) works fine if you format your output cell properly.

nvuono
  • 3,323
  • 26
  • 27