3

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

4 Answers 4

3

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

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
0
1

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
0
0

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

You must log in to answer this question.

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