2010-01-22

gnumeric generated dates to text

I needed to generate test data. I was using gnumeric for this (makes copy pasting and test replacement easy for columns [same field]).

It was all going well until I needed dates to be not all the same. I could have generated random dates but I didn't need that, just needed dates to not be the same. So in the date column I set the first date to 01/01/1970 (new zealand format, mm/dd/yyyy), set the column to type date and then set the succeeding dates to be =[column][row]+1 (e.g., =A1+1).

The test data needed to be mm/dd/yyyy since that's what jmeter would push into an input field that would be interpreted as a date. But when I'd save to CSV gnumeric kept outputting the dates in ISO format. So I needed to munge the dates.

1. Copy the column
2. Paste-special to an empty column, select Values
3. Select the new column and Ctrl-H (search and replace)
4. set the "Search For" text to ^
5. set the "Replace By" text to '
6. click on the Range checkbox
7. click on Advanced and specify "Search Type" to be "Regular Expression"
8. click on OK.

Saving as CSV now has the dates in the format I specified in the spreadsheet. There might be a way to tell gnumeric to output dates in locale format when saving to CSV, but I couldn't find it. Fortunately, Paste special and regexp search and replace were available, making the workaround possible.