0

I have mysql dump file in the below format and I want to replace the 15th column value by matching given values with first two columns.

INSERT INTO TABLE VALUES 
 ('20','ramesh',,,,,,,,,,,,,'1',,,),
 ('30','raghav',,,,,,,,,,,,,'1',,,),
 ('40','balaji',,,,,,,,,,,,,'1',,,);

Expected output:

INSERT INTO TABLE VALUES 
 ('20','ramesh',,,,,,,,,,,,,'0',,,),
 ('30','raghav',,,,,,,,,,,,,'1',,,),
 ('40','balaji',,,,,,,,,,,,,'1',,,);

Here sed should match given empId and empName as '20' and 'ramesh' and replace 15th column of value '1' to '0'. In between columns 3,8 and 9 have json or xml string values

I tried with below sed capture group but it is not working.

sed -i -e "s|(\('20\)',\('ramesh'\),\(.*\),\(.*\),\(.*\),\(.*\),\(.*\),\(.*\),\(.*\),\(.*\),\(.*\),\(.*\),\(.*\),\(.*\),'1'|(\1,\2,\3,\4,\5,\6,\7,\8,\9,\10,\11,\12,\13,\14,'0'|" employees.sql

Any help is appreciated.

Ed Morton
  • 188,023
  • 17
  • 78
  • 185
Abirami Ramkumar
  • 25
  • 3
  • Thanks for showing your efforts, please do post expected output in your question also to make it more clear, thank you. – RavinderSingh13 Apr 27 '23 at 10:50
  • If in real life "In between columns 3,8 and 9 have json or xml string values" then in the example in your question those columns should have json or xml string values, especially if they include quotes and commas. Please [edit] your examples to show truly representative sample input/output. – Ed Morton Apr 27 '23 at 11:49
  • does the json/xml data contain single quotes, or linefeeds that break the data across multiple lines? – markp-fuso Apr 27 '23 at 11:56
  • please update the question with an explanation of what you mean by *`is not working`* ... you get an error (and if so, what is the full error)? you get no output? you get the wrong output (and if so, what is the output you get)? no changes are made to the data? something else? – markp-fuso Apr 27 '23 at 11:57
  • sed supports only 9 capture groups, you can reference only `\1` through `\9`. – danadam Apr 27 '23 at 11:59

3 Answers3

1

Using GNU awk for FPAT and the 3rd arg to match():

$ cat tst.awk
BEGIN {
    OFS = ","
    FPAT = "([^" OFS "]*)|('([^']|'')*')"
}
match($0,/(^\s*\()(.*)(\).*)/,a) {
    $0 = a[2]
    if ( ($1 == "'20'") && ($2 =="'ramesh'") ) {
        $15 = "'0'"
    }
    $0 = a[1] $0 a[3]
}
{ print }


$ awk -f tst.awk file
INSERT INTO TABLE VALUES
 ('20','ramesh',,,,,,,,,,,,,'0',,,),
 ('30','raghav',,,,,,,,,,,,,'1',,,),
 ('40','balaji',,,,,,,,,,,,,'1',,,);

If you want to run the awk script using awk 'script' instead of awk -f scriptfile as above then just change every ' in the script to \047.

The above assumes your CSV doesn't contain newlines. If it does then see What's the most robust way to efficiently parse CSV using awk? for how to parse it using awk.

Ed Morton
  • 188,023
  • 17
  • 78
  • 185
1

This might work for you (GNU sed):

sed -E 's/(^\s*\(('\'')20\2,\2ramesh\2,{13}\2)1\2/\10\2/' file

Using pattern matching and back references, replace the 1 in the 15th field of a line with 0, where the first two fields match 20 and ramesh.

N.B. The second back reference is a ' character which is also matched the LHS of the regexp.

potong
  • 55,640
  • 6
  • 51
  • 83
0

The following only accepts text you provide (may not be generic).

awk '
BEGIN{FS=OFS=","; q="\047"};
$1 ~ q"20"q && $2 ~ q"ramesh"q{$15=q"0"q}; 1
' file
dam
  • 75
  • 3