[PLUG] Reformating date fields in text files

[TomasK]

This is how I would do it with gensub in awk:

echo "col1,01/13/2022,col3" | \
awk -v FS=, -v OFS=, '{newDate=gensub(/\//,"-","g",$2); $2=newDate;
print; }'
col1,01-13-2022,col3

It works by replacing all / in col2 with -

If I would need to reorder/reformat the date string by split and print:
echo "col1,01/13/2022,col3" | \
awk -v FS=, -v OFS=, '{
  if (split($2,dateEl,"/")) {
    newDate=sprintf("%02d-%02d-%04d",dateEl[1],dateEl[2],dateEl[3]);
    $2=newDate;
  };
print; }'
col1,01-13-2022,col3

It works by splitting the col2 into dateEl array and if successful,
printing it reformatted as mm-dd-yyyy

Best, Tomas

On Fri, 2022-01-14 at 13:22 -0800, Russell Senior wrote:
> Have you read the manpage where it discusses the split function?
> Maybe
> it's time to re-read that.
> 
> On Fri, Jan 14, 2022 at 1:13 PM Rich Shepard <rshepard at appl-ecosys.co
> m> wrote:
> > 
> > I often download data as .csv files. The dates are usually formated
> > as
> > mm/dd/yyyy. I want to convert them to the format yyyy-mm-dd. I've
> > used awk
> > in the last instance (with the split() function) but didn't make
> > note of
> > exactly how it was working so I could modify it for different
> > source date
> > formats. I didn't get it working today on a new file, even after
> > reading
> > about the split() function.
> > 
> > I also read about the match() function as that was used in a stack
> > exchange
> > thread. I couldn't get it to work, either.
> > 
> > All the threads I found on my web search have original dates
> > formatted
> > mmddyyyy and needed to have that string separated with dashes or
> > forward
> > slashes separating month, day, and year. I keep dates in ISO 8601
> > format
> > (yyyy-mm-dd) and would like a way to do this with sed, awk, python,
> > or perl.
> > I need it commented so I can adjust it for the number of fields in
> > each line
> > and the position of the date field.
> > 
> > An example of the current data file:
> > BB Wood Products LLC|2017-094|Scappoose|Notice of Civil Penalty
> > Assessment||7/31/2017|8400.00
> > The date to be modified is in the sixth field.
> > 
> > I'm looking to learn how to do this conversion that's adaptable to
> > all data
> > files needed adjustment.
> > 
> > TIA,
> > 
> > Rich