Moving columns around

This is a mysql night mare that I cant seem to wrap my head around. Any shell based answers is appreciated as I dont know Perl and all I would do would be blindly copy & paste !

FILE CONTENTS -
1389685 INSERT INTO Opera_ShirtCatlog(col1,col2) VALUES (1,'TEST1'),(2,'TEST2');

1389675 INSERT INTO Opera_ShirtCatlog_Wom(col1, col2, col3) VALUES (9, 'Siz12, FormFit', 'Test');

DESIRED OUTPUT -

INSERT INTO Opera_ShirtCatlog(col1,col2,FIRSTCOL) VALUES (1, 'TEST', 1389685);
INSERT INTO Opera_ShirtCatlog_Wom(col1, col2, col3, FIRSTCOL) VALUES (9, 'Siz12 FormFit', 'Test', 1389675);

As you can see above, the task at hand is this that I want to move the first column as part of the INSERT statement's LAST COLUMN before the closing paranthesis. I am loosing my mind trying to figure this out as I can use sed to hold the variable and then how am I supposed to substitute this only especially when it has to avoid the first closing bracket as thats table definition ... also if I use awk then how do I do a regex substitution ? Any help is sincerely appreciated.

regards,
Manohar.

It is unclear to me what you mean by "the task at hand is this that I want to move the first column as part of the INSERT statement's LAST COLUMN before the closing paranthesis."

Where does FIRSTCOL come from?

Fpmurphy,

At the outset thanks for posting reply. Sorry for not being clear at the outset.

In the input file, the first column is the value that I need to be moved inside the paranthesis after VALUES. In the two example line I mentioned, for the first line, 1389685 is the value that should be moved into the insert statement after the string VALUES as below -

INPUT LINE 1-
1389685 INSERT INTO Opera_ShirtCatlog(col1,col2) VALUES (1,'TEST1'),(2,'TEST2');

OUTPUT LINE 1- 
INSERT INTO Opera_ShirtCatlog(col1,col2, FIRSTCOL) VALUES (1,'TEST1', 1389685),(2,'TEST2', 1389685);

INPUT LINE 2 -
1389675 INSERT INTO Opera_ShirtCatlog_Wom(col1, col2, col3) VALUES (9, 'Siz12, FormFit', 'Test');

OUTPUT LINE 2 -
INSERT INTO Opera_ShirtCatlog_Wom(col1, col2, col3,FIRSTCOL) VALUES (9, 'Siz12, FormFit', 'Test',1389675);

Try this:

perl -lpe 's/^(\d+) (.*?)(\);)/$2, $1$3/g' file 
INSERT INTO Opera_ShirtCatlog(col1,col2) VALUES (1,'TEST1'),(2,'TEST2', 1389685);

INSERT INTO Opera_ShirtCatlog_Wom(col1, col2, col3) VALUES (9, 'Siz12, FormFit', 'Test', 1389675);

Hope this gets you on the right path.

in2nix4life,

Thank you for extending your arms in helping me. First, I dont understand PERL and second, its not moving that at all...

Any sed / awk based answer is requested from you.

regards,

What OS, shell, type of file containing the data?

The more information provided, the better chance of working towards a satisfactory solution.

Its a regular mysql log file and I am running bash. Also I did happen to mention early in my post that I am looking for awk/sed based solution as I dont understand PERL. Also, This is not the original file itself & after writing couple of filters, this is where it is....

cat NEWOUTPUT | grep 'INSERT' | cut  -f 5- | awk '{print $2=$3=$4=""}1' 

gave me the following -

1389685 INSERT INTO Opera_ShirtCatlog(col1,col2) VALUES (1,'TEST1'),(2,'TEST2');
1389675 INSERT INTO Opera_ShirtCatlog_Wom(col1, col2, col3) VALUES (9, 'Siz12, FormFit', 'Test');

NOw, when I tag your PERL liner, you can see below I got nothing -

cat NEWOUTPUT | grep 'INSERT' | cut  -f 5- | awk '{print $2=$3=$4=""}1' |  perl -lpe 's/^(\d+) (.*?)(\);)/$2, $1$3/g'

1389685 INSERT INTO Opera_ShirtCatlog(col1,col2) VALUES (1,'TEST1'),(2,'TEST2');
1389675 INSERT INTO Opera_ShirtCatlog_Wom(col1, col2, col3) VALUES (9, 'Siz12, FormFit', 'Test');

Please advise !

That's a whole lot going on there...

Try this:

grep INSERT NEWOUTPUT | perl -pe 's/^\n//;s/^(\d+) (.*?)(\);)/$2, $1$3/g'

INSERT INTO Opera_ShirtCatlog(col1,col2) VALUES (1,'TEST1'),(2,'TEST2', 1389685);
INSERT INTO Opera_ShirtCatlog_Wom(col1, col2, col3) VALUES (9, 'Siz12, FormFit', 'Test', 1389675);

in2nix4life,

I just dont like to say this but Its not doing anything.No errors no action output is just the same as ever before ....
:wall:

/Users/ManoharChandran/MOVER 18:03:43 $cat NEWOUTPUT | grep 'INSERT' | cut  -f 5- | awk '{print $2=$3=$4=""}1' |  perl -pe 's/^\n//;s/^(\d+)    (.*?)(\);)/$2, $1$3/g'

1389685 INSERT INTO Opera_ShirtCatlog(col1,col2) VALUES (1,'TEST1'),(2,'TEST2');
1389675 INSERT INTO Opera_ShirtCatlog_Wom(col1, col2, col3) VALUES (9, 'Siz12, FormFit', 'Test');

regards,
Manohar.

---------- Post updated at 07:29 PM ---------- Previous update was at 06:07 PM ----------

Smartfolks,

Can someone please post an answer to my query up here as I have spent pretty much all day but I am back to where I was and the given perl solution is not working. I tried to substitute the value of ") VALUES" with ",LASTSTRING) VALUES" using simple sed replace.

However the Critical part of moving the first string into the string where its pushed as the last value using closing parenthesis is not working for me and I know this for sure that SED should do it but I am not getting it.

Please advise.

regards,
Manohar.

regards,
Manohar.

You give no explanation of why the following is used:

$cat NEWOUTPUT | grep 'INSERT' | cut  -f 5- | awk '{print $2=$3=$4=""}1' 

If the file contains the following as you initially explained:

INPUT LINE 1-
1389685 INSERT INTO Opera_ShirtCatlog(col1,col2) VALUES (1,'TEST1'),(2,'TEST2');

OUTPUT LINE 1- 
INSERT INTO Opera_ShirtCatlog(col1,col2, FIRSTCOL) VALUES (1,'TEST1', 1389685),(2,'TEST2', 1389685);

Why use all of those commands? Of course the solution I gave doesn't work cause you're including a bunch of what seems to be unnecessary commands.

in2nix4life,

Yes, I needed all these commands to get it to the current state where in, all I am left with is, to finally move in the first set of numbers as a last value before closing the paranthesis. I tried to do what I can by pushing the cart as far as I can and thats exactly what I did in here. So, the first set of commands bought the data to this point.

But, If you want to tackle the raw line itself sure ... Here it is.

#### 3601 0089 22360 1389675 INSERT PARTITION_443 Opera_ShirtCatalog INSERT INTO Opera_ShirtCatalog(COL1, COL2) VALUES (1, 'TEST1'), (2,'TEST2');
#### 12001 0093 22362 1389685 INSERT PARTITION_454 Opera_ShirtCatlog_Wom INSERT INTO Opera_ShirtCatlog_Wom(col1, col2, col3) VALUES (9,'Siz12, FormFit', 'Test');

Also, let me re-hash as to what is needed in here.

All I need out of this, is from the insert statement onwards, I need to print the rest of line ie., from INSERT INTO �. till ";" which is the end of the line. The trickiest part in here is, I need to yank the 5th column and move that as the last column inside the values clause. So, what I would necessarily want to see as final output would be as below -

INSERT INTO Opera_ShirtCatalog(COL1, COL2, NEWCOLUMN) VALUES (1, 'TEST1', 1389675), (2,'TEST2', 1389675);
INSERT INTO Opera_ShirtCatlog_Wom(col1, col2, col3, NEWCOLUMN) VALUES (9,'Siz12, FormFit', 'Test', 1389685);

Hope you understand my situation with this and hope I am clear in what I need to do.