Append text on lines NOT matching pattern

Question

INSERT INTO `db`.`table`
VALUES (
 39741633
 49302045
 0
 44
  '{"CustomerName":"S","CustomerMobile":"8","CustomerEmail":"","VersionId":"5","CityId":"2","CampaignId":"1","InquirySourceId":"3","Eagerness":"-1","ApplicationId":"2","BranchId":"3","AssignedDealerId":"2","DMSInqNo":"45"}'
  NULL
  0
  '2021-11-09 19:11:50'
  NULL
  1
  29
  NULL
);
INSERT INTO `db`.`table`
VALUES (
 39741635
 49970365
 0
 30
  '{"CustomerName":"A","CustomerEmail":"[email protected]","CustomerMobile":"9","VersionId":"6","InquirySourceId":"1","Eagerness":"-1","IsCorporate":"z","CampaignId":"8","BranchId":"3","ApplicationId":"1","Location":{"City":{"CityId":"1"},"Area":{"AreaId":"4"}},"CouponCode":null,"CwOfferId":"0","AssignedDealerId":"0","PinCode":""}'
  NULL
  0
  '2021-11-09 19:11:51'
  NULL
  1
  29
  NULL
);

I have these insert statements extracted out of binlogs. They were not exactly like this, I have changed them a little, but I am stuck. I need to convert them into actual insert statements. If you see I need to add commas at end of each line after the second line (INSERT INTO db.table VALUES (), for each insert statement. That means add commas after two lines till 11 lines, then again start after INSERT INTO and repeat.

I tried to google and found this

sed '/INSERT/{n;n;n;n;n;s/$/,/}' teststring.txt --> this will put comma on fifth line after insert. The problem is if I run it again for the sixth line

sed '/INSERT/{n;n;n;n;n;n;s/$/,/}' teststring.txt , then it replaces the earlier comma placed by the first SED command

This is how the file should look.

INSERT INTO `db`.`table`
VALUES (
 39741633,
 49302045,
 0,
 44,
 '{"CustomerName":"S","CustomerMobile":"8","CustomerEmail":"","VersionId":"5","CityId":"2","CampaignId":"1","InquirySourceId":"3","Eagerness":"-1","ApplicationId":"2","BranchId":"3","AssignedDealerId":"2","DMSInqNo":"45"}',
  NULL,
  0,
  '2021-11-09 19:11:50',
  NULL,
  1,
  29,
  NULL
);
INSERT INTO `db`.`table`
VALUES (
 39741635,
 49970365,
 0,
 30,
  '{"CustomerName":"A","CustomerEmail":"[email protected]","CustomerMobile":"9","VersionId":"6","InquirySourceId":"1","Eagerness":"-1","IsCorporate":"z","CampaignId":"8","BranchId":"3","ApplicationId":"1","Location":{"City":{"CityId":"1"},"Area":{"AreaId":"4"}},"CouponCode":null,"CwOfferId":"0","AssignedDealerId":"0","PinCode":""}',
  NULL,
  0,
  '2021-11-09 19:11:51',
  NULL,
  1,
  29,
  NULL
);

How can I achieve this?

Answer 1

You could match all lines NOT containing some string, and append the comma:

sed '/.*VALUES.*\|.*INSERT.*\|);$/! s/$/,/' your_file

The way it's working is as follows:

• I match any lines containing either VALUES, INSERT or ending with );.
• This is done by chaining together the different strings with \|.
• These are then 'negated' them with the ! (so only lines NOT containing these strings are actually matched).
• I then append a comma to those lines.

Once you've confirmed it does what you want, just add the -i to do the substitution in-place:

sed -i '/.*VALUES.*\|.*INSERT.*\|);$/! s/$/,/' your_file

Edit

As pointed out by @they in the comments below, this command will also place a comma on the last line of each INSERT statement (I'm not sure if this is a problem for you):

INSERT INTO `db`.`table`
VALUES (
 39741633,
  .
  .
  .
  29,
  NULL,  <--- unecessary comma here
);

Answer 2

sed -i '/INSERT INTO/{n;n;s/$/,/}' teststring.txt 

I missed the -i option, this SED command added a comma at the end of the second line after INSERT INTO. I am able to do what I wanted to, but I had to run that command repeatedly for 10 lines by increasing n; in each command so the next command for the third line would be

`sed -i '/INSERT INTO/{n;n;n;s/$/,/}' teststring.txt`.

Here there are three n; and in the first command there were two n;.

I would appreciate if any one has a better way of doing this :-).

Answer 3

Whenever we find a line starting with the string VALUES, append the next line to the editing buffer in sed. Repeat this until the buffer ends with );. Then replace all newlines that are not flanked by a parenthesis on any side with a comma.

/^VALUES/ {
    # Loop until the buffer ends with ");".
    # The N command reads the next line and appends
    # it to the buffer, with a newline character as delimiter.
    :again
    N
    /);$/ !b again

    # Replace all newlines with commas,
    # but only if the newline is not immediately
    # next to a parenthesis.
    s/\([^(]\)\n\([^)]\)/\1,\2/g
}

This could be used as a separate sed script:

sed -f thescript file

... or directly on the command line:

sed -e '/^VALUES/ {' \
    -e ':again' \
    -e 'N; /);$/ !b again' \
    -e 's/\([^(]\)\n\([^)]\)/\1,\2/g; }' file

Given the document in the question, this would generate syntactically correct SQL looking like this:

INSERT INTO `db`.`table`
VALUES (
 39741633, 49302045, 0, 44,  '{"CustomerName":"S","CustomerMobile":"8","CustomerEmail":"","VersionId":"5","CityId":"2","CampaignId":"1","InquirySourceId":"3","Eagerness":"-1","ApplicationId":"2","BranchId":"3","AssignedDealerId":"2","DMSInqNo":"45"}',  NULL,  0,  '2021-11-09 19:11:50',  NULL,  1,  29,  NULL
);
INSERT INTO `db`.`table`
VALUES (
 39741635, 49970365, 0, 30,  '{"CustomerName":"A","CustomerEmail":"[email protected]","CustomerMobile":"9","VersionId":"6","InquirySourceId":"1","Eagerness":"-1","IsCorporate":"z","CampaignId":"8","BranchId":"3","ApplicationId":"1","Location":{"City":{"CityId":"1"},"Area":{"AreaId":"4"}},"CouponCode":null,"CwOfferId":"0","AssignedDealerId":"0","PinCode":""}',  NULL,  0,  '2021-11-09 19:11:51',  NULL,  1,  29,  NULL
);

If you want the exact output that you have in the question, replace the last substitution with

s/\([^(]\)\(\n\)\([^)]\)/\1,\2\3/g

Instead of replacing the newlines with commas, this inserts a comma before each newline.

Answer 4

Since we have a consistent number of values (rows) to insert, we can count them and append a comma to all but the last:

awk 'i>0 {i--; $0 = $0 ","} /^VALUES/ {i=11} 1' datafile.txt

There are actually three statements in this awk program. You could rewrite it like this, spread across several lines. (All lines in the program are applied in order to each line of input.)

awk '
    i>0 { i--; $0 = $0 "," }    # If we are counting, decrement the counter and append comma
    /^VALUES/ { i=11 }          # "VALUES" resets the counter for 12 values/rows
    1                           # Print the current line
' datafile.txt

Sample output (first stanza)

INSERT INTO `db`.`table`
VALUES (
 39741633,
 49302045,
 0,
 44,
  '{"CustomerName":"S","CustomerMobile":"8","CustomerEmail":"","VersionId":"5","CityId":"2","CampaignId":"1","InquirySourceId":"3","Eagerness":"-1","ApplicationId":"2","BranchId":"3","AssignedDealerId":"2","DMSInqNo":"45"}',
  NULL,
  0,
  '2021-11-09 19:11:50',
  NULL,
  1,
  29,
  NULL
);

Answer 5

You can use a two-line pattern space, and append commas to all lines that don't have INSERT or ); And owing to 2 lines in the pattern space, the line around INSERT and ); too are not appended to.

sed -e '
  $!N;/INSERT\|);$/!{s/\n/,&/;P;D;}
' file