Awk – replace number greater than 17 digits in a column with –

awkvariable substitution

I have a CSV file containing timestamp values in UTC which I need to replace with -. There may be more than one timestamp in the same column, can you please let me know how do I do that?

For example, this is one column in a CSV file:

+1234|2|12|1|1|1537820114232192380|0  +1234|2|12|1|1|1537820113262689150|0

The output should look like:

+1234|2|12|1|1|-|0  +1234|2|12|1|1|-|0

Best Answer

Since that is inside a file, it is faster to use sed:

sed -i 's/[0-9]\{18,\}/-/g' file

Understand that the -i option will change your file. If you want to see what it does before committing remove the -i.

Note that in BSD, the -i should have a parameter, so use: -i ''.

Awk could also do it:

<file awk '{gsub("[0-9]{18,}", "-")}1'  >newfile

Related Solutions

Replace all values in one column to 1

awk '{print $1, $2, "1"}' inputfile

AWK – How to Separate Lines with Same Content in a Column into Different Files

This will work efficiently using POSIX sort and any awk in any shell on every UNIX box:

$ sort -t, -k4,4 test.csv |
    awk -F, '$4!=prev{close(out); out="File"$4; prev=$4} {print > out}'

$ head -n 20 File*
==> FileA <==
1,2,3,A,5

==> FileB <==
1,2,3,B,5
1,2,3,B,5

==> FileD <==
1,2,3,D,5

==> FileE <==
1,2,3,E,5

==> FileZ <==
1,2,3,Z,5

Some things to note:

some awks need putting parens around an expression on the right side of output redirection, and
some awks fail if you don't close output files as they go and so trying to retain too many open files once they get past a dozen or so output files, and
keeping multiple open output files is very inefficient in all awks that allow it, and
closing output files line by line to account for that will be very inefficient in all awks.

Best Answer

Related Solutions

Replace all values in one column to 1

AWK – How to Separate Lines with Same Content in a Column into Different Files

Related Question