How to retain date formatting during CSV export

Question

Is there a way to retain the date formatting when exporting to CSV? I have a GWT application. My date data for example is 03-01-2012 and 02-2012. After exporting it to CSV successfully, when I open the CSV file, those dates now look like 3/1/2012 and 2/1/2012 respectively. Is there a way to control how they will look like in the CSV? I debugged and followed the code right before they get exported and the dates are correct. I'd appreciate any help.

Solution 1

For all I know, it's impossible to control how dates are displayed in Excel when using CSV (it can only be controlled by the Excel user's settings). We had a similar problem (with phone numbers), and the only workaround that works reliably, is to use an Excel format. We use Apache POI to write .xls files ^[*], where we can set the cell type to text.

At least for phone numbers, this is necessary - otherwise Excel discards leading 0s, and treats them as numbers (making it impossible to distinguish between national and international phone numbers). For dates however, maybe it's worth considering, if it's better to simply let the user decide how the dates are displayed?

^{[*] We also offer to download .csv files with a warning that they're not ideal when opened with Excel.}

Solution 2

You should check out java.text.SimpleDateFormat

Solution 3

you can't (as far as i know) pass meta information in a CSV file. So to see the date the way you send it, you can pass it in one of the following ways (the data value is between the commas):

, 03-01-2012, ,="03-01-2012",