How to convert in specific column in csv in shell script linux

Question

i have a file output.csv like this :

No | Timestamp | Table | agg | result | percentage | status | year | month | day
2022-09-30 00:00:00|2022-09-30 00:00:00|Source|count|287.472.321|-4.83%|Normal|2022|202209|20220930
2022-09-30 00:00:00|2022-09-30 00:00:00|Source|count|498.512.903|-4.05%|Normal|2022|202209|20220930
2022-09-30 00:00:00|2022-09-30 00:00:00|Source|count|19.089.222|-19.4%|Normal|2022|202209|20220930
2022-09-30 00:00:00|2022-09-30 00:00:00|Source|count|29.014.921|-2.47%|Normal|2022|202209|20220930
2022-09-30 00:00:00|2022-09-30 00:00:00|Source|count|148.770.415|-0.14%|Normal|2022|202209|20220930

all i wanna do is converting "." to blankspace in 5th column without affect on 6th column like this :

2022-09-30 00:00:00|2022-09-30 00:00:00|Source|count|287472321|-4.83%|Normal|2022|202209|20220930
2022-09-30 00:00:00|2022-09-30 00:00:00|Source|count|498512903|-4.05%|Normal|2022|202209|20220930
2022-09-30 00:00:00|2022-09-30 00:00:00|Source|count|19089222|-19.4%|Normal|2022|202209|20220930
2022-09-30 00:00:00|2022-09-30 00:00:00|Source|count|29014921|-2.47%|Normal|2022|202209|20220930
2022-09-30 00:00:00|2022-09-30 00:00:00|Source|count|148770415|-0.14%|Normal|2022|202209|20220930

i've tried using cat output.csv | grep -v -i "No " | sed 's/\.//g' > output3.csv But it will convert all columns that have "." .

So how to converting "." to blankspace in 5th column without affect on 6th column with still csv format output?

Thank you for the help!

stackoverflow.com/questions/29091138/… stackoverflow.com/questions/35074171/… — KamilCuk
– KamilCuk, Commented Oct 6, 2022 at 7:28

M. Nejat Aydin · Accepted Answer · 2022-10-06 13:53:23Z

1

It is easier to do this in awk:

awk 'BEGIN{ FS=OFS="|" } { gsub("[.]","",$5) } 1' file

But if you insist on using sed:

sed -e ':a' -e 's/^\(\([^|]*|\)\{4\}[^|]*\)\./\1/' -e 'ta' file

edited Oct 6, 2022 at 13:53

answered Oct 6, 2022 at 7:53

M. Nejat Aydin

10.3k1 gold badge10 silver badges22 bronze badges

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

4 Comments

cankcimen Over a year ago

can i ask you to detailing the script? i dont understand at : - BEGIN{ FS=OFS="|" } - "1" at the end Thank you

M. Nejat Aydin Over a year ago

@cankcimen FS=OFS="|" sets both input and output field separators to |. 1 at the end prints the $0.

cankcimen Over a year ago

Ah i see, thank you. Can i ask 1 more thing, where can i learn more about this "sed" and "awk" function?

M. Nejat Aydin Over a year ago

@cankcimenc Search for "GNU Awk User's Guide" and "GNU sed manual"

potong · Accepted Answer · 2022-10-06 12:45:53Z

0

This might work for you (GNU sed and shell):

sed 'y/|/\n/;s/.*/echo "&"|sed "5s#\\.##g"/e;y/\n/|/' file

In essence, turn each line into a file and run a second invocation of sed where each address represents a column in the original line.

Translate |'s to newlines.

Write a mini sed invocation each time the pattern space is presented, that removes all .'s from the 5th line (each line in the invocation represents a column in the original line).

Translate newlines back to |'s.

edited Oct 6, 2022 at 12:45

answered Oct 6, 2022 at 12:18

potong

59.3k6 gold badges55 silver badges92 bronze badges

Collectives™ on Stack Overflow

How to convert in specific column in csv in shell script linux

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related