Linux - Replace column value

Question

I have a huge file with many lines and columns. This file is using |~| as column separator.

I would like to replace the value .000000 on the 17 Column for nothing (in this case, erase this value in this specific column).

I tried use sed, but it is replacing the value in all columns. I don't know how specify the column 17.

sed -i 's/.000000//g' filename.txt

How the file is:

20201101 10:24:52.000000|~||~||~|SYSTEM|~||~||~||~|00001|~||~|000326|~||~||~||~||~||~|20201101 10:24:51.000140|~|**20201101 10:24:51.000000**|~|20201101 10:24:52.000912|~|

How I'm expecting the command result:

20201101 10:24:52.000000|~||~||~|SYSTEM|~||~||~||~|00001|~||~|000326|~||~||~||~||~||~|20201101 10:24:51.000140|~|**20201101 10:24:51**|~|20201101 10:24:52.000912|~|

Note: the column I want to change the value, have a different Date and Time for each line. So I just need to specify the column and the value I want to replace, not entire value on the column.

Need to delete last 7 characters on specific column on all lines that the file have value .000000

Thanks.

James Brown · Accepted Answer · 2020-11-03 04:56:53Z

3

In awk:

$ awk 'BEGIN{FS="\\|~\\|";OFS="|~|"}{sub(/\.000000/,"",$17)}1' file

Partial output:

...|~|**20201101 10:24:51**|~|...

Pretty-printed:

$ awk '
BEGIN {
    FS="\\|~\\|"                  # or "[|]~[|]"
    OFS="|~|"
}
{
    sub(/\.000000/,"",$17)        # due to the *s in sample data, else /.{7}$/
}1' file                          # output

sub($17,1,17) instead of sub() would work, too.

edited Nov 3, 2020 at 4:56

answered Nov 3, 2020 at 4:17

James Brown

37.7k8 gold badges52 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

anubhava Over a year ago

++ this is most portable answer

potong · Accepted Answer · 2020-11-03 12:16:44Z

1

This might work for you (GNU sed):

sed 's/^\(\([^|]*|~|\)\{16\}[^|]*\)\.000000\([^|]*|~|\)/\1\3/' file

Match 16 fields and the first part of the 17th field, followed by .000000 and the last part of the 17th field followed by |~| and replace it by 16 fields and the first part of the 17th field followed by the last part of the 17th field and|~|.

edited Nov 3, 2020 at 12:16

answered Nov 3, 2020 at 1:05

potong

59.3k6 gold badges55 silver badges92 bronze badges

2 Comments

potong Over a year ago

@DavidC.Rankin yes, it appears the original data example was amended. I have likewise.

David C. Rankin Over a year ago

That's the ticket, confirmed. So it was the addition of a third backreference that did the trick... Nicely done!

souser · Accepted Answer · 2020-11-03 00:16:02Z

0

Is this what you are looking for ?

awk -F '\\|~\\|'  '{print $17}' file | sed -e 's/.000000//'

answered Nov 3, 2020 at 0:16

souser

6,1326 gold badges41 silver badges57 bronze badges

Comments

FGirardi · Accepted Answer · 2020-11-03 22:32:29Z

0

awk command, worked fine, but in my case, there is some lines that I don't need to remove because don't have this extra characters (milliseconds), so this command will work to export the data to another new file.

$ awk 'BEGIN{FS="\\|~\\|";OFS="|~|"}{sub(/\.000000/,"",$17)}1' file

sed command worked perfect. It replaced all records I don't want in my file just for the column I need.

sed 's/^\(\([^|]*|~|\)\{16\}[^|]*\)\.000000\([^|]*|~|\)/\1\3/' file

Thank you all.

answered Nov 3, 2020 at 22:32

FGirardi

31 bronze badge

Collectives™ on Stack Overflow

Linux - Replace column value

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related