1

I require assistance with this I have a pipe delimited file that is formatted as such

Location|1111|222222|333333|Doe|John|19900723|19900724|19900725

The last 3 fields are Date fields, but I can't seem to format them as such. I need them formatted as 1990/07/23. I'm learning awk so I'd prefer to do it through awk, but I'm open to suggestions.

2

As Kusalananda comments, you can just do some string manipulation:

awk -F'|' -v OFS='|' '
    function format_date(d) { return substr(d,1,4) "/" substr(d,5,2) "/" substr(d,7) }
    { for (i = 7; i <= 9; i++) $i = format_date($i); print }
' file
1
awk -F'|' 'BEGIN {OFS="|"} {print $1,$2,$3,$4,$5,$6,strftime("%Y/%m/%d",$7),strftime("%Y/%m/%d",$8),strftime("%Y/%m/%d",$9)}' inputfile

The key here is strftime(), which will format the date provided in the second parameter using the format string in the first parameter:

$ echo "1552924174" | awk '{print strftime("%Y/%m/%d", $1) }'
2019/03/18

strftime() is supported by GNU awk and by mawk, but not by BSD awk.

  • 1
    Alternatively, update the relevant fields and just print. – Kusalananda Mar 18 at 16:06
  • strftime is great if you have a time_t value, i.e. seconds since 1970 (given Unix), not the values this OP has which are yyyymmdd treated as numbers. – dave_thompson_085 Mar 19 at 11:35
0

I have done by below method

`echo "Location|1111|222222|333333|Doe|John|19900723|19900724|19900725"| awk -F "|" '{$NF=substr($NF,1,4)"/"substr($NF,5,2)"/"substr($NF,7,2);$(NF-1)=substr($(NF-1),1,4)"/"substr($(NF-1),5,2)"/"substr($(NF-1),7,2);$(NF-2)=substr($(NF-2),1,4)"/"substr($(NF-2),5,2)"/"substr($(NF-2),7,2);print $0}'| sed "s/ /`|/g"

output

echo "Location|1111|222222|333333|Doe|John|19900723|19900724|19900725"| awk -F "|" '{$NF=substr($NF,1,4)"/"substr($NF,5,2)"/"substr($NF,7,2);$(NF-1)=substr($(NF-1),1,4)"/"substr($(NF-1),5,2)"/"substr($(NF-1),7,2);$(NF-2)=substr($(NF-2),1,4)"/"substr($(NF-2),5,2)"/"substr($(NF-2),7,2);print $0}'| sed "s/ /|/g"


Location|1111|222222|333333|Doe|John|1990/07/23|1990/07/24|1990/07/25
  • 1
    Rather than using sed to change all spaces in the output of awk into pipes, you could simply set OFS="|" in the awk code. That way you don't run the risk of accidentally creating new fields due to spaces in the input data. – Kusalananda Mar 19 at 9:31
0

You can do it using Perl as shewn:

Input:

$ cat file
Location|1111|222222|333333|Doe|John|19900723|19900724|19900725

$ perl -lpe 's#\|\K([0-9]{8})(?=(?:(?:\|[0-9]{8}){0,2})$)#join "/", unpack "A4A2A2", $1#ge' file
Location|1111|222222|333333|Doe|John|1990/07/23|1990/07/24|1990/07/25

Explanation:

  • -l sets RS = ORS = "\n"
  • -p reads input file in records with the record separator set above. Also, prints the current record to stdout before reading in the next one.
  • -e applies the Perl code that follows this option to every record ($_) read in.
  • The regex finds out 8-digit numbers those that are preceded by a pipe and followed by at the most two neighbors of the same variety as itself before it sees the end of line. They are stored captured in $1.
  • Now each captured $1 is unpacked according to the A4A2A2 pattern and these patterns are then joined together with a slash.

Alternate:

$ perl -F'[|]' -pale '$_ = join "/", unpack "A4A2A2" for @F[-3..-1]; $_ = join "|", @F'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.