awk - replace number greater than 17 digits in a column with -

Question

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

Answer 1

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

Answer 2

You can use awk as follows:

echo "+1234|2|12|1|1|1537820114232192380|0  +1234|2|12|1|1|1537820113262689150|0" | awk '{gsub("[0-9]{18,}", "-")}1'
  +1234|2|12|1|1|-|0  +1234|2|12|1|1|-|0

You can use sed as follows:

  echo "+1234|2|12|1|1|1537820114232192380|0  +1234|2|12|1|1|1537820113262689150|0" | sed -r 's/[0-9]{18,}/-/g'
  +1234|2|12|1|1|-|0  +1234|2|12|1|1|-|0

Answer 3

If, for whatever reason, you would like to avoid using either regular expressions or tools besides awk, you can opt to use awk conditionals.

echo "+1234|2|12|1|1|1537820114232192380|0  +1234|2|12|1|1|1537820113262689150|0" | awk -F'|' 'OFS="|" { for (i = 1; i <= NF; i++) { if (length($i) > 17) { $i = "-"} } print; }'

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

Explanation:

-F'|'                            # Set input field-separator to bar
'OFS="|"                         # Set output field-separator to bar
{ for (i = 1; i <= NF; i++) {    # Loop through the fields
if (length($i) > 17) { $i = "-"} # Set a field with length over 17 to "-"
} print; }'                      # Print output of all fields after this process

Answer 4

I would go with this variant using awk (and sed to replace the trailing ORS with newline), which checks for strings of length 17 chars or more.

awk -vRS='[|\n]' -vORS='|' 'length($0)>=17{$0="-"}1' | sed 's/|$/\n/'

To only filter out numbers with more than 17 digits, do:

awk -vRS='[|\n]' -vORS='|' 'log($0)/log(2)>=17{$0="-"}1' | sed 's/|$/\n/'

There are also tricks to avoid sed altogether and use a single awk process, such as here: https://stackoverflow.com/questions/34684958/make-the-record-seperator-in-awk-not-apply-after-the-last-record

This way we use awk's record splitting and filtering capabilities, and we can have more precise control of the filter, as opposed to a regular expression.

Validation test:

$ awk -vRS='[|\n]' -vORS='|' 'length($0)>=17{$0="-"}1' <<< '+1234|2|12|1|1|1537820114232192380|0  +1234|2|12|1|1|1537820113262689150|0' | sed 's/|$/\n/'
+1234|2|12|1|1|-|0  +1234|2|12|1|1|-|0

$ awk -vRS='[|\n]' -vORS='|' 'log($0)/log(2)>=17{$0="-"}1' <<< '+1234|2|12|1|1|1537820114232192380|0  +1234|2|12|1|1|1537820113262689150|0' | sed 's/|$/\n/'
+1234|2|12|1|1|-|0  +1234|2|12|1|1|-|0