So, I’m trying to import my Perl-generated file as a CSV into Open Office. Apparently, if the data coming into a cell is of the form “D.D.D” where “Ds” are digits, it obviously and absolutely must be a date, and it converts the incoming cell to that format.
Well, no. I wanted it to be (for example) literally “1.3.5.” Really. No kidding. It’s not 01/03/05. But it won’t let me do it.
I don’t want to have to manually go in and change the format for each cell where this happens, and even if I did, there’s no obvious way to do it and retain the original info without manually retyping the number with a single quote in front. Is there an Open Office guru out there?
BTW, I really appreciate the help with the Perl problem. It was invaluable (which means, it was very useful, but I don’t know how to pay for it, or what it was worth to those providing it).
You might try the text-to-columns feature if there are no tabs in the file. Open the data file in a text editor, then copy and paste the data into the spreadsheet. Select what you just pasted (should be one column), and go to the Data menu, and select “Text to Columns…” Select commas as your delimiter.
You should see a little preview grid at the bottom. You can force the format of any column by selecting it and setting the format in the dropdown just above the grid. Set the troublesome column(s) to text.
Note that this is the same workaround needed if you are using Excel. I would be *thrilled* to learn that Open Office had a better way of doing this, since this is one of Excel’s most aggravating behaviors.
The solution is to prepend each cell value with single quote, like this:
‘1.3.5,’11.4.2008
This is the standard spreadsheet convention for insisting that a cell value is a string, and not a number or in this case a date.
Unfortunately WordPress educated the single quotes in the example. They should both be forward quotes, not back-ticks.
Thanks Jim,
I’ve struggled with this problem for years.
–F
You’re very welcome, Fred. It’s always nice to be thanked.
When you offer helpful advice, you get thanks. Don’t expect it for advice packaged up as trollish snark.
Right, since the original question already mentioned the single quotes, what I meant to say that is that basically the single quotes have to be there, so the Perl script should add them.
When you offer helpful advice, you get thanks.
If you mean only to speak for yourself, I won’t question the point.
But as a general principle of human nature, it is certainly not always true. There are people who are absolutely unable to thank “the enemy”. Nor for that matter to apologize to real or perceived enemies. Again, not to name names, but there have even been presidents who were like this.