Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

sed or awk command to replace a string pattern with another string based on position of this string

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting sed or awk command to replace a string pattern with another string based on position of this string
# 1  
Old 06-14-2012
sed or awk command to replace a string pattern with another string based on position of this string
here is what i want to achieve... consider a file contains below contents. the file size is large about 60mb

Code:
 
cat dump.sql
 
INSERT INTO `table1` (`id`, `action`, `date`, `descrip`, `lastModified`) VALUES (1,'Change','2011-05-05 00:00:00','Account Updated','2012-02-10 13:00:00'),(2,'dsfsd','2000-04-06 00:00:00','Updated','2011-02-10 13:00:00'),(3,'Change','2011-05-05 00:00:00','Account Updated','2012-02-10 13:00:00')
.
.
.
INSERT INTO `table2` (`id`, `action`, `date`, `lastModified`, `dynamics`) VALUES (1,'Change','2011-05-05 00:00:00','2012-02-10 13:00:00',null),(2,'dsfsd','2000-04-06 00:00:00','2011-02-10 13:00:00',null),(3,'Change','2011-05-05 00:00:00','2012-02-10 13:00:00',123)
.
 
.
.many tables


the above are just sample data... here some table lastModified field can be at end or somewhere in between.... what i want to achieve is replace all corresponding lastModifed parameter with '0000-0000 00:00:00' instead of exisiting values. i can make this by directly updating it in database but i want to modify it directly it in this dump file rather than updating it in database first and then taking the dump. so is this feasible..? the logic should happen like this it should compare from one "INSERT INTO" to next INSERT INTO and between this from find the position of last modified and replace it with 000's.. but a table can have many timestamp parameter. but the command or script should modify only the corresponding lastModified column. any help would be appreciated.
# 2  
Old 06-14-2012
I'm not sure if I understood your problem, but is it this what you're looking for?

Code:
awk -F, -vQ="'" '/^INSERT INTO/&&$8=$13=$18=Q"0000-0000 00:00:00"Q' OFS=, file

Last edited by cabrao; 06-14-2012 at 08:13 AM..
# 3  
Old 06-14-2012
let me explain once again..

Code:
cat dump.sql
 
INSERT INTO `table1` 
(`id`, `action`, `date`, `descrip`, `lastModified`) VALUES
(1,'Change','2011-05-05 00:00:00','Account Updated','2012-02-10 13:00:00'),
(2,'dsfsd','2000-04-06 00:00:00','Updated','2011-02-10 13:00:00'),
(3,'Change','2011-05-05 00:00:00','Account Updated','2012-02-10 13:00:00');
.
.
.
INSERT INTO `table2` (`id`, `action`, `date`, `lastModified`, `dynamics`) VALUES 
(1,'Change','2011-05-05 00:00:00','2012-02-10 13:00:00',null),
(2,'dsfsd','2000-04-06 00:00:00','2011-02-10 13:00:00',null),
(3,'Change','2011-05-05 00:00:00','2012-02-10 13:00:00',123);
.
 
.
.many tables

here consider Table1
lastModified column is at the last field in table. so REPLACE all last field before every closing bracket with this '0000-00-00 00:00:00' with single quotes till you end up with ';' which marks the end of that table query. if lastModified field comes any position in filed sequence as shown in Table2 where the lastModified field comes one position before last. so replace corresponding fields after this till you hit ';' which marks the end of that table.

output should look like below
Code:
cat dump.sql
 
INSERT INTO `table1` 
(`id`, `action`, `date`, `descrip`, `lastModified`) VALUES
(1,'Change','2011-05-05 00:00:00','Account Updated','0000-00-00 00:00:00'),
(2,'dsfsd','2000-04-06 00:00:00','Updated','0000-00-00 00:00:00'),
(3,'Change','2011-05-05 00:00:00','Account Updated','0000-00-00 00:00:00');
.
.
.
INSERT INTO `table2` (`id`, `action`, `date`, `lastModified`, `dynamics`) VALUES 
(1,'Change','2011-05-05 00:00:00','0000-00-00 00:00:00',null),
(2,'dsfsd','2000-04-06 00:00:00','0000-00-00 00:00:00',null),
(3,'Change','2011-05-05 00:00:00','0000-00-00 00:00:00',123);
.
 
.
.many tables

# 4  
Old 06-14-2012
Hi


Code:
$ cat file
INSERT INTO `table1` (`id`, `action`, `date`, `descrip`, `lastModified`) VALUES
(1,'Change','2011-05-05 00:00:00','Account Updated','2012-02-10 13:00:00'),
(2,'dsfsd','2000-04-06 00:00:00','Updated','2011-02-10 13:00:00'),
(3,'Change','2011-05-05 00:00:00','Account Updated','2012-02-10 13:00:00');
INSERT INTO `table2` (`id`, `action`, `date`, `lastModified`, `dynamics`) VALUES
(1,'Change','2011-05-05 00:00:00','2012-02-10 13:00:00',null),
(2,'dsfsd','2000-04-06 00:00:00','2011-02-10 13:00:00',null),
(3,'Change','2011-05-05 00:00:00','2012-02-10 13:00:00',123);

Output:

Code:
$ awk -F, '/INSERT/{for(i=1;i<=NF;i++){if ($i ~ /lastModified/){x=i;print;getline;break;}}}{y="0000-0000 00:00:00";sub(/[-0-9: ]+/,y,$x);}1'  OFS=, file
INSERT INTO `table1` (`id`, `action`, `date`, `descrip`, `lastModified`) VALUES
(1,'Change','2011-05-05 00:00:00','Account Updated','0000-0000 00:00:00'),
(2,'dsfsd','2000-04-06 00:00:00','Updated','0000-0000 00:00:00'),
(3,'Change','2011-05-05 00:00:00','Account Updated','0000-0000 00:00:00');
INSERT INTO `table2` (`id`, `action`, `date`, `lastModified`, `dynamics`) VALUES
(1,'Change','2011-05-05 00:00:00','0000-0000 00:00:00',null),
(2,'dsfsd','2000-04-06 00:00:00','0000-0000 00:00:00',null),
(3,'Change','2011-05-05 00:00:00','0000-0000 00:00:00',123);

Guru.
This User Gave Thanks to guruprasadpr For This Post:
vivek d r
# 5  
Old 06-14-2012
totally awesome... :-) a million thanks guruprasad...
if you have time can you please explain how this command works?

---------- Post updated at 05:20 PM ---------- Previous update was at 05:16 PM ----------

is the command 'number of column' specific..? cause just now i tried with a big file and it dint work :-(

the above wont work for

Code:
INSERT INTO `Table44` (`id`, `action`, `date`, `description`, `modifiedId`, `object`, `performedById`, `schemaDifference_blob_reserved`, `schemaDifference_reserved`, `sourceIPAddress`, `zoneId`, `lastModified`) VALUES 
(1,'Change','2011-05-05 00:00:00','User Account Updated',3,'Account',1,NULL,NULL,'',1,'2012-06-09 13:56:24');

# 6  
Old 06-14-2012
When I ran the same command for the file specified, it is coming properly.


Code:
$ awk -F, '/INSERT/{for(i=1;i<=NF;i++){if ($i ~ /lastModified/){x=i;print;getline;break;}}}{y="0000-0000 00:00:00";sub(/[-0-9: ]+/,y,$x);}1'  OFS=, file
INSERT INTO `Table44` (`id`, `action`, `date`, `description`, `modifiedId`, `object`, `performedById`, `schemaDifference_blob_reserved`, `schemaDifference_reserved`, `sourceIPAddress`, `zoneId`, `lastModified`) VALUES
(1,'Change','2011-05-05 00:00:00','User Account Updated',3,'Account',1,NULL,NULL,'',1,'0000-0000 00:00:00');


Make sure your file should be in the format as you had specified in the sample earlier like:
Code:
INSERT INTO . ............ VALUES
(.....................),
(.....................);

not all in the same line.
# 7  
Old 06-14-2012
oh.... hmmmm the file is very huge with thousands of records.... all of them are in single line.... :-( what to do now...
i gave the sample file to match the columns and show the format accordingly.. the original lines are as shown in my first post in the thread

is there any way so that i could put a next line after every ") Values" and "),(" strings in whole file...
or is there any way to modify the command which you gave?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Replace string of a file with a string of another file for matches using grep,sed,awk

I have a file comp.pkglist which mention package version and release . In 'version change' and 'release change' line there are two versions 'old' and 'new' Version Change: --> Release Change: --> cat comp.pkglist Package list: nss-util-devel-3.28.4-1.el6_9.x86_64 Version Change: 3.28.4 -->... (1 Reply)
Discussion started by: Paras Pandey
1 Replies

2. Shell Programming and Scripting

Replace string in XML file with awk/sed with string from another

Sorry for the long/weird title but I'm stuck on a problem I have. I have this XML file: </member> <member> <name>TransactionID</name> <value><string>123456789123456</string></value> </member> <member> <name>Number</name> ... (9 Replies)
Discussion started by: cozzin
9 Replies

3. Shell Programming and Scripting

Search for a string at a particular position and replace with blank based on position

Hi, I have a file with multiple lines(fixed width dat file). I want to search for '02' in the positions 45-46 and if available, in that lines, I need to replace value in position 359 with blank. As I am new to unix, I am not able to figure out how to do this. Can you please help me to achieve... (9 Replies)
Discussion started by: Pradhikshan
9 Replies

4. Shell Programming and Scripting

Using sed to replace a string in a specific position

I asked this before, but my problem got more complicated. Heres what I am trying to do: I'm trying to replace a string at a certain location with another string. Heres the file I'm trying to change: \E I want to replace the escape code at the 3rd line, 2nd column with this escape code... (3 Replies)
Discussion started by: tinman47
3 Replies

5. Shell Programming and Scripting

replace (sed?) a string in file with multiple lines (string) from variable

Can someone tell me how I can do this? e.g: a=$(echo -e wert trewt ertert ertert ertert erttert erterte rterter tertertert ert) How do i replace the STRING with $a? I try this: sed -i 's/STRING/'"$a"'/g' filename.ext but this don' t work (2 Replies)
Discussion started by: jforce
2 Replies

6. UNIX for Dummies Questions & Answers

Search a string in the file and then replace another string after that position

Hi I am looking for a particular string in a file.If the string exists, then I want to replace another string with some other text.Once replaced, search for the same text after that character position in the file. :wall: E.g: Actual File content: Hello Name: Nitin Raj Welcome to Unix... (4 Replies)
Discussion started by: dashing201
4 Replies

7. Shell Programming and Scripting

Using sed to replace a string in file with a string in a variable that contains spaces

Hi, i call my shell like: my_shell "my project name" my script: #!/bin/bash -vx projectname=$1 sed s/'PROJECT_NAME ='/'PROJECT_NAME = '$projectname/ <test_config_doxy >temp cp temp test_config_doxy the following error occurres: sed s/'PROJECT_NAME ... (2 Replies)
Discussion started by: vivelafete
2 Replies

8. UNIX for Dummies Questions & Answers

Search for a string and replace the searched string in the same position in samefile

Hi All, My requisite is to search for the string "0108"(which is the year and has come in the wrong year format) in a particular column say 4th column in a tab delimited file and then replace it with 2008(the correct year format) in the same position where 0108 was found in the same file..The... (27 Replies)
Discussion started by: ganesh_248
27 Replies

9. Shell Programming and Scripting

Search for a string and replace the searched string in the same position

Hi All, My requisite is to search for the string "0108"(which is the year and has come in the wrong year format) in a particular column say 4th column in a tab delimited file and then replace it with 2008(the correct year format) in the same position where 0108 was found..The issue is the last... (15 Replies)
Discussion started by: ganesh_248
15 Replies

10. Shell Programming and Scripting

Find the position of a string and replace with another string

Hi, I have a file named "Test_2008_01_21" The file contains a string "manual" that occurs many times in the file How can i find the positions of the string "manual" in the file Ex: if the string " manual " occurs three times in the file. i want to replace the second occurance of string... (6 Replies)
Discussion started by: bab123
6 Replies
Login or Register to Ask a Question