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.

4 Answers 4

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.

2
  • 1
    Alternatively, update the relevant fields and just print.
    – Kusalananda
    Mar 18, 2019 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. Mar 19, 2019 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
  • 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, 2019 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'

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .