Archive for November, 2006

Keeping date format in OpenOffice spreadsheet when saving to CSV

Found this one on the OO mailing list archive. I wanted to dupe it here in case that page ever disappears. The original post is located here:

The parent poster and I share the same issue. Formatting a CSV file’s date field(s) to fit MySQL’s preferred date format (YYYY-MM-DD). This sounds easy enough, but when you save to a CSV it always defaults to the “standard” format of MM/DD/YY. Quoth Mr. Barrie Backhurst:

MySQL’s country of origin is Sweden and.setting this as language in cell
styles works.

Open the stylist(F11), right click the default cell style and select
New. It should open a dialog with the Organize tab visible. Enter a
suitable name(mysqldate) in place of “untitled”. Switch to the Number
tab and set Category = Date, Language = Swedish and Format = YYYY-MM-DD.
Click OK to save the new cell style.

Now select the cells with the dates that need formatting and double left
click on the new mysqldate cell style. The selected cells should adopt
the mysql format and retain it when exported to CSV.

Worked like a charm Mr. Backhurst. Thanks!