4

I need some suggestion to make the below bash script using sed/awk in a one-liner way or if i can use python instead would be great for doing this search and replace task.

Here I created the search pattern in a file named "input.txt" with first column to search and second column for replace. Then I assign each column value on an array and calling in a for loop using sed utility to search and replace on "file.csv". This change only happens on the 3rd column only.

file.csv

Symbol,Name,Value
*,yy03LN-1,LM-GA-G01
*,yy5310-4,YP-QL-A03
*,yy5310-5,YP-QL-A10

input.txt

LM-GA-G01,LM-GA-G1
YP-QL-A03,YP-QL-A3
YP-QL-A10,YP-QL-A10

Expected results, to remove the 3rd column '0' number

file.csv

Symbol,Name,Value
*,yy03LN-1,LM-GA-G1
*,yy5310-4,YP-QL-A3
*,yy5310-5,YP-QL-A10

I created a shell script based on this condition, and this works better, but i need help similiar a single liner or short script to achieve this task.

#!/bin/bash
post=$(cat file.csv|awk -F "," '{print $NF}'| grep -v Index)
postar=($post)

for (( i=0; i<${#postar[@]}; ++i )); do
grep "${postar[$i]}" input.txt >> filtered.txt
done

left=$(cat filtered.txt|awk -F "," '{print $1}')
leftar=($left)
right=$(cat filtered.txt|awk -F "," '{print $2}')
rightar=($right)


for (( i=0; i<${#leftar[@]}; ++i )); do
sed -i -e 's/'"${leftar[$i]}"'/'"${rightar[$i]}"'/g' file.csv
done

Please note:- the line count of input.txt and file.csv are not same.

Please suggest

Thanks Jay

Improve this question
3
  • 3
    Is there any point to the input.txt file? It seems you just want to remove the zero-padding from integers... Commented Dec 22, 2022 at 12:04
  • input.txt is not mandatory, i just placed to get the search/replace pattern. Not from every integers only string contains A01,``` B03,C02``` and not no action for A10 , B20. Commented Dec 23, 2022 at 10:41
  • If your current example doesn't adequately cover your requirements then fix the example, don't try to describe some different input/output in a comment. Commented Dec 23, 2022 at 16:35

4 Answers 4

Reset to default
6

The following uses the CSV-aware tool Miller (mlr) to remove the zero-padding of all numbers embedded in the value of the Value field:

$ mlr --csv put '$Value = gsub($Value, "([A-Z])0+([1-9])", "\1\2")' file
Symbol,Name,Value
*,yy03LN-1,LM-GA-G1
*,yy5310-4,YP-QL-A3
*,yy5310-5,YP-QL-A10

The Value field is modified using the gsub() function by matching any occurrence of the digit 0 (possibly repeated) following an upper-case letter ([A-Z]), followed by a digit that is not zero ([1-9]). Any such match is replaced by the upper case letter and the non-zero digit, removing the string of zeros that is padding the integer.

The modification can be performed in-place using mlr with its -I option.

Improve this answer
1
  • Thanks for the suggestion, I never knew about this tool mlr . Let me explore this more Commented Dec 23, 2022 at 10:03
5

If your files aren't too large you can use awk :

awk 'BEGIN{FS=OFS=","}
     NR==FNR{a[$1]=$2;next}
     {print $1,$2,($3 in a ? a[$3] : $3)}' input.txt file.csv
  • BEGIN{FS=OFS=","} Set fields separators to ,
  • NR==FNR if the number record is equal to the file number record (if you are in the first file)
  • a[$1]=$2 Store into an array the second field using the first field as the key
  • $3 in a ? a[$3] : $3 if $3 is a key in a print a[$3] else print $3
Improve this answer
4
  • 1
    Thanks for the suggestion. one concern here if there is no keyword match found, this produces a whitespace. Simply change on file.csv from YP-QL-A03 to YP-QL-A3. Because my file.csv consist of more than 500 lines and only few of the lines triggers the match which i specify only on the input.txt file. And the line count of file.csv and input.txt are not same. Commented Dec 23, 2022 at 10:34
  • @user183980 changed it to work without having a key in the array Commented Dec 23, 2022 at 10:41
  • I'm getting an error ``` awk: cmd. line:3: {print $1,$2,($3 in a ? a[$3] : $3} ^ syntax error``` Commented Dec 23, 2022 at 18:47
  • 1
    @user183980 I fixed the answer by changing $3} to $3)}. Commented Dec 24, 2022 at 0:53
3

If this isn't all you need:

$ sed 's/0*\([0-9]*$\)/\1/' file.csv
Symbol,Name,Value
*,yy03LN-1,LM-GA-G1
*,yy5310-4,YP-QL-A3
*,yy5310-5,YP-QL-A10

then edit your question to provide a more truly representative example that includes cases where that doesn't work.

Improve this answer
5
  • Great this is what I'm looking. This is looking only the 3rd column and removing the 0 charactor. but keeping 0 greater than 10. Thanks for the suggestion Commented Dec 23, 2022 at 10:20
  • Could you help explain this part 0*\([0-9]*$\)/\1/' in sed, that would be great for me to understand Commented Dec 23, 2022 at 10:35
  • 1
    Match any number of 0s followed by any number of any digits. Capture the latter and reference it later by \1, thereby removing any preceding 0s. Commented Dec 23, 2022 at 16:14
  • Thanks, but how this search limited to 3rd column, because there are 0's in second column as well. And we never specify sed to look only in specific column right? please advise Commented Dec 23, 2022 at 18:53
  • 1
    The $ anchors the regexp to the end of the line which means it can only match in the 3rd (last) column because the regexp is entirely digits, it'd need to include a , to be able to match any part of an earlier column. Commented Dec 24, 2022 at 0:51
2

if there is more than removing 0, you can dynamically generate sed program.

 awk -F, '{printf "s/%s/%s/\n",$1,$2}' input.txt

where

  • -F, tell awk to use , as separator,
  • printf "s/%s/%s/\n",$1,$2 will generate substitution

this give with your input

s/LM-GA-G01/LM-GA-G1/
s/YP-QL-A03/YP-QL-A3/
s/YP-QL-A10/YP-QL-A10/

now feed it to sed

  awk -F, '{printf "s/%s/%s/\n",$1,$2}' input.txt | sed -i -f - file.csv

where

  • -i use in place edition
  • -f - use sed instructions from stdin (the awk generated part)
Improve this answer
6
  • One of the few instances in which cross-language meta-programming isn't evil. :-) Commented Dec 23, 2022 at 0:05
  • Thanks for the answer this got worked for my use case. And this feeding method is awesome i will use this for my other use-cases Commented Dec 23, 2022 at 10:39
  • @DavidFoerster evil may be too far but there's no point using awk to generate a sed script to do a replacement when awk could easily just do the replacement itself instead. Commented Dec 23, 2022 at 16:38
  • 1
    @user183980 regarding i will use this for my other use-cases - don't do that, I'm not sure if I'd go quite as far as to call this approach an anti-pattern, but it's convoluted and fragile at best compared to simply doing the whole replacement in 1 call to awk. You don't need sed when you're using awk. Commented Dec 23, 2022 at 16:41
  • 2
    as Note by @EdMorton this method is perfomed by professional stunt, don't do this at home. Commented Dec 24, 2022 at 8:19

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.