0

I would require some suggestion on how to replace/modify strings from the second column of csv by searching a list of symbols matches.

Here is my file.csv

[Entry]
Date,20-02-2000
[Input]
SL,Name,Datasize,value
1,tg12.18.l,ch17,12.1
2,yo11`18.p,ch18,13.5
3,qk13_18.q,ch19,22.6
4,ls15-18.r,ch20,23.7
5,ew16'18.s,ch21,24.9

After running sed/awk , below is the expected outcome

[Entry]
Date,20-02-2000
[Input]
SL,Name,Datasize,value
1,tg12_18_l,ch17,12.1
2,yo11_18_p,ch18,13.5
3,qk13_18_q,ch19,22.6
4,ls15-18_r,ch20,23.7
5,ew16_18_s,ch21,24.9

If the search matches - and _ no actions. If the search matches , < ` ' replace needs to done with a underscore sign: _.

I tried with some combinations using awk and this is not working.

 awk -F, -v OFS=, 'sub(/\.\`\>\'/,"_",$2)+1' file.csv

Any ideas appreciated

3
  • 1
    You said , should be replaced but given the file is a comma-separated should not the commas be ignored? Because in this case a , should a indicate a new column or value Nov 29, 2022 at 20:27
  • @EdgarMagallon yes the comma is matched only second column string, the it will be replaced by _ sign.
    – user183980
    Nov 30, 2022 at 7:33
  • Looks like you also want to replace .s with _s. Nov 30, 2022 at 8:26

2 Answers 2

2

Edit: Since OP edited the data. It's better to use awk now instead of sed.

awk 'BEGIN{FS=OFS=","}/^[0-9]+,/{gsub(/[\.<`\x27]/,"_",$2);print;next}1' file.csv
6
  • thanks for your suggestion, i corrected the file.csv file now for the second column. You can see there are two matches coming in the same string on second column and this needs to replaced with _ sign. I tested this but only looking the first symbol and second symbol ignoring. could you please help.
    – user183980
    Nov 30, 2022 at 7:58
  • 1
    @user183980 see edit.
    – cjijcb
    Nov 30, 2022 at 9:38
  • You don't need to escape . inside a bracket expression as it's already literal, you should use \047 instead of \x27 to represent a ' (see awk.freeshell.org/PrintASingleQuote), and you can remove ;print;next without changing the output of the script since the next after the print is just skipping the second print caused by 1. You don't really need to test for leading digits given the OPs sample input either. So, all you really need is awk 'BEGIN{FS=OFS=","} {gsub(/[.<`\047]/,"_",$2)} 1' file.csv.
    – Ed Morton
    Dec 1, 2022 at 0:00
  • @EdMorton "You don't really need to test for leading digits given the OPs sample input either." I think it's safe to have /^[0-9]+,/ because without this, awk may affect these rows, Date,20-02-2000and SL,Name,Datasize,value if the second field of these rows have any of the characters in [.<`\047], like for example, Date,20.02.2000 and SL,<Name>,Datasize,value. < and . would be become _. I totally agree with the rest of what you pointed out. I didn't know much of them until now.
    – cjijcb
    Dec 1, 2022 at 2:33
  • You're right about "if the second field..." but, according to the OPs data, they don't. Similarly if we don't assume the rest of the OPs data looks as they posted then the rows that need to change may not all start with digits, maybe some can start with null or -1 or similar. Maybe the safer test is for NR>4, idk, but IMHO it's safe to assume the OPs real data looks like their posted example until if/when they tell us otherwise.
    – Ed Morton
    Dec 1, 2022 at 14:08
1

With perl:

perl -pi -e 's{^\d+,\K[^,]+}{$& =~ s/[^\w-]/_/gr}e' -- "$file"

Replaces one or more (+) characters other than , ([^,]) that follows the start of the line (^) and one or more (+) \decimal \digits and a , (\K to reset the start of the match), with the same ($&) where any character other than a \word character (alnum or underscore) or - is replaced with a _.

So in effect, we replace characters other than alnum, _ and - in the second field if the first field is entirely made of decimal digits.

To restrict the match to those particular tables in the file, you could make it:

perl -pi -e 's{^\d+,\K[^,]+}{$& =~ s/[^\w-]/_/gr}e if
               /^SL,Name/ .. /^\[/' -- "$file"

Where we only make those substitutions in between a line that starts with SL,Name and the next line that starts with [.

The file is edited in place with -i, -p is for the sed mode where the -expression is evaluated for each line of the file(s) (with the line stored in $_, the default variable s/pattern/replacement/flags works on, equivalent to the pattern space in sed).

3
  • thanks for your suggestion, i tested but this is not getting my expected outcome. Could you check this with my original file.csv please
    – user183980
    Nov 30, 2022 at 8:08
  • 1
    @user183980, my bad, I had missed the fact there was more than one character to replace. See edit. Nov 30, 2022 at 8:18
  • Great this works :)
    – user183980
    Nov 30, 2022 at 8:55

You must log in to answer this question.

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