[Gllug] OpenOffice Calc date formats

[Richard Jones]

On Fri, Jun 26, 2009 at 01:45:18PM +0100, Tim Schofield wrote:
> On Fri, 2009-06-26 at 11:35 +0100, lesleyb at herlug.org.uk wrote:
> 
> > I'll need to repeat this operation so I want to find a solution.
> > I could do it nongraphically working solely on the csv file before it gets to
> > Calc but I was wondering if anyone knows a date/time function that would convert 
> > on the fly in Calc?
> 
> I have had a similar problem before and here is my nongraphically perl
> one liner to reformat the date field.
> 
> perl -MDate::Manip -F, -l -a -n -e "@F[0]=&UnixDate(ParseDate(@F[0]),'%
> d/%m/%Y');print join(',', at F)" test.csv 
> 
> 
> @F[0] is because my date was in first column so change both occurrence
> accordingly.
> 
> man Date::Manip for formats other than %d/%m/%Y.
> 
> My csv file did not have field headers and the fields were unquoted, so
> you may need to sed -e 'd1;s/"//g' the file first.

Indeed, CSV files are _not_ mere lines of values separated by commas.
For example, the following is a valid 1x1 CSV file (one row, one
column) with an embedded ASCII NUL byte:

      "hello,"0
world"

You're best to use Text::CSV in Perl, or my own csvtool (available
prepackaged in Debian and Fedora at least) which is very shell-script
friendly:

http://merjis.com/_file/csvtool.txt
http://packages.debian.org/stable/utils/csvtool

Rich.

-- 
Richard Jones
Red Hat
-- 
Gllug mailing list  -  Gllug at gllug.org.uk
http://lists.gllug.org.uk/mailman/listinfo/gllug