[PLUG] Re-formatting Date in .csv Field

[Sam Hart]

On Tue, Jan 25, 2011 at 1:09 PM, Rich Shepard <rshepard at appl-ecosys.com> 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.

Super hackish awk that I did in five minutes, but it seems to work... maybe...

awk '{split ($1, a, ","); split (a[4], b, "/"); printf
"%s,%s,%s,%s-%s-%s,%s,%s,%s,%s\n", a[1], a[2], a[3], b[3], b[1], b[2],
a[5], a[6], a[7], a[8]; }'

Give it a try, refine it (because it's fugly), your mileage may vary,
yadda yadda...

                                      ---Sam