[PLUG] Re-formatting Date in .csv Field

Fred James fredjame at fredjame.cnc.net
Tue Jan 25 21:54:52 UTC 2011


Rich Shepard wrote:
>    I've an interesting situation with a data file and I'm uncertain how best
> to resolve it. The file started life as an Excel spreadsheet (13,500 rows)
> and I imported it to OO.o, then used File->Save as to write it as a .csv
> file for ultimate import into a database. I need to change the date format.
>
>    Dates are written as [m]m/dd/yyyy and as a numeric field, not as a text
> field surrounded by quotation marks. I'd like the dates to be written in the
> more standard YYYY-MM-DD format. OO.o will let me view the dates in
> different formats but the underlying format is unchanged.
>
>    Here's an example row:
>
> 27132,"96-A001256","BC-0.5",5/21/1996,"pH",8.19,,"True"
>
>    I want the date to read "1996-05-21" instead of 5/21/1996. This
> column/field has three sub-fields, unlike the previous parsing need that had
> the month, day, and year as separate text fields which are easily handled by
> gawk.
>
>    Please offer suggestions on how to convert all 13.5-thousand rows to the
> preferred date format.
>
> Rich
>   
Rich Shepard
Having written the *.csv file, one could use AWK/GAWK, first using the 
comma as the field separator (FS) and then splitting (split) the the 
fourth field with the slash as the field separator.  Finally, one would 
use printf to format the line.  Remember that the FS must be included in 
the format string as it will not have been saved as part of a field.
    printf("%d,%s,%s,%4d/%2d/%2d,%s,%s,,%s\n", .......)
or something of the sort.
Does that help?  Do you need help with that?  Reply off list if needs be.
Regards
Fred James




More information about the PLUG mailing list