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
  • Can you show an example of “more than one timestamp in the same column “? – Jeff Schaller Oct 2 '18 at 23:10

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
  • Thank you. The sed variant worked for me. – Yashovan N Oct 3 '18 at 20:58

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

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


-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

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

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.