Visit Our UNIX and Linux User Community


Shell script: substitute value in text file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Shell script: substitute value in text file
# 1  
Old 07-24-2010
Shell script: substitute value in text file

Hi gurus,

I need help with shell script. I have various INSERT queries which inserts data into database. I want to insert 3rd column data into newline for one particular table. I have very time long txt file everytime and it have various INSERT/UPDATE queries but i have to done with it only one table. Here is input


Code:
INSERT INTO test1 column1,column2 VALUES (column1value,column2value);
INSERT INTO test2 column1,column2,column3 VALUES (column1value,column2value,column3value);
INSERT INTO test3 column1 VALUES (column1value);


I need following as output so when database recieves \n character it will inserts data in new line i believe. I need this with only test2 table. I am not sure how to match pattern because everytime test2 table column2value is unique. I thaught it can be done by sed or awk.


Code:
INSERT INTO test1 column1,column2 VALUES (column1value,column2value);
INSERT INTO test2 column1,column2,column3 VALUES (column1value,column2value,\ncolumn3value);
INSERT INTO test3 column1 VALUES (column1value);

Any help appreciated.
# 2  
Old 07-24-2010
Try something like this:
Code:
awk -F, '/INSERT INTO test2/ {$((NF+5)/2)="\\n" $((NF+5)/2)}1' OFS="," file

# 3  
Old 07-25-2010
You could also use sed:
Code:
sed -i '/INTO test2 / s/,\([^,]*);\)/,\\n\1/' file

Notice the space after 2, which guarantees you will not match 20.
\( ... \) captures text that is copied to the output using \1.
[^,]*); matches zero or more non-comma characters (column3value) followed by );.
# 4  
Old 07-25-2010
Quote:
Originally Posted by KenJackson
You could also use sed:
Code:
sed -i '/INTO test2 / s/,\([^,]*);\)/,\\n\1/' file

Notice the space after 2, which guarantees you will not match 20.
\( ... \) captures text that is copied to the output using \1.
[^,]*); matches zero or more non-comma characters (column3value) followed by );.
This command don't add the "\n" in the 3th column if you have more then 3 columns, but may be sufficient for the purpose of the OP.
# 5  
Old 07-25-2010
Code:
awk  'BEGIN{FS=OFS=","}/INTO test2/{$NF="\\n"$NF}1' urfile

# 6  
Old 07-26-2010
Hello,

Franklin, i tried your command

awk -F, '/INSERT INTO test2/ {$((NF+5)/2)="\\n" $((NF+5)/2)}1' OFS="," inputfile

It outputs perfectly as "\ncolumn3value" but i forgot to mention that column3value is in single quote it results "\ncolumn3value" while i want it to be as '\ncolumn3value' within single quotes.

Origninal i have to try it with UPDATE query and output should be as follows \n character need to be in single quotes:

[code]
UPDATE test2 set c = Concat(chat, 'testing '),s_count = '0', b_count = '0',t_lines = LENGTH(c) - LENGTH(REPLACE(c, '\n ', '')), c_end = now() where id = '504';
[\code]

Franklin, can you please also explain query.

Thank you for your help.

---------- Post updated at 07:54 AM ---------- Previous update was at 05:09 AM ----------

this is the input.

Code:
UPDATE test2 set c = Concat(chat, 'testing '),s_count = '0', b_count = '0',t_lines = LENGTH(c) - LENGTH(REPLACE(c, '\n ', '')), c_end = now() where id = '504';

and output should be

Code:
UPDATE test2 set c = Concat(chat, 'testing '),s_count = '0', b_count = '0',t_lines = LENGTH(c) - LENGTH(REPLACE(c, '\n ', '')), c_end = now() where id = '504';



---------- Post updated at 07:57 AM ---------- Previous update was at 07:54 AM ----------

Ah.. sorry for the mistake in iput file.
this is the input.

Code:
UPDATE test2 set c = Concat(chat, 'testing '),s_count = '0', b_count = '0',t_lines = LENGTH(c) - LENGTH(REPLACE(c, ' ', '')), c_end = now() where id = '504';

and output should be

Code:
UPDATE test2 set c = Concat(chat, 'testing '),s_count = '0', b_count = '0',t_lines = LENGTH(c) - LENGTH(REPLACE(c, '\n ', '')), c_end = now() where id = '504';

while no other command works for me mentioned by rdcwayx and KenJackson.
# 7  
Old 07-26-2010
The solutions above is not suitable for arbitrary lines, for the line starting with "UPDATE" you could do it straight with sed like this:
Code:
sed "/UPDATE test2/ s/LENGTH(REPLACE(c, ' ', ''))/LENGTH(REPLACE(c, '\\\n ', ''))/" file


Previous Thread | Next Thread
Test Your Knowledge in Computers #1005
Difficulty: Easy
Regarding a circle, one full turn of the circle is equal to 2 radians.
True or False?

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need help to write a shell script to convert text file to excel file.

Hi Everyone, I want your help to write a script which will take text file as input and on the basis of delimiter ":"script will create excel sheet. Example input: IpAdress:InstanceName:Port:ServerName 10.255.255.1:abc:2232:xyz_abc Output should be an excel sheet like below: Column... (8 Replies)
Discussion started by: akabhinav18
8 Replies

2. UNIX for Dummies Questions & Answers

Reading XML file and print the values in the text file using Linux shell script

hi guys, i want help... Reding XML file and print the values into the text file using linux shell script file as per below xml file <sequence> <Filename>aldorzum.doc</Filename> <DivisionCode>US</DivisionCode> <ContentType>Template</ContentType> <ProductCode>VIMZIM</ProductCode> </sequence>... (1 Reply)
Discussion started by: sravanreddy
1 Replies

3. UNIX for Dummies Questions & Answers

Shell script to read lines in a text file and filter user data Shell Programming and Scripting

sxsaaas (3 Replies)
Discussion started by: VikrantD
3 Replies

4. UNIX for Dummies Questions & Answers

Insert text into a file using shell script

Hi, I need to insert "Hello World" text into a file called hai.txt using shell scripting. Kindly help me. For eg: If I open the file hai.txt by giving linux command cat hai.txt, the content of the file should have the text Hello World in it. Thanks (5 Replies)
Discussion started by: karthick nath
5 Replies

5. Shell Programming and Scripting

using awk to substitute data in a column delimited text file

using awk to substitute data in a column delimited text file hello i would like to use awk to do the following calculation from the following snippet. input file C;2390 ;CV BOUILLOTTE 2L 2FACES NERVUREES ;1.00 ;3552612239004;13417 ;25 ;50 ; 12;50000 ; ; ... (3 Replies)
Discussion started by: iindie
3 Replies

6. Shell Programming and Scripting

shell or perl script needed for ldif file to text file conversion

This is the ldf file dn: sdcsmsisdn=1000000049,sdcsDatabase=subscriberCache,dc=example,dc=com objectClass: sdcsSubscriber objectClass: top postalCode: 29600 sdcsServiceLevel: 10 sdcsCustomerType: 14 givenName: Adelia sdcsBlackListAll: FALSE sdcsOwnerType: T-Mobile sn: Actionteam... (1 Reply)
Discussion started by: LinuxFriend
1 Replies

7. Shell Programming and Scripting

Execute unix shell script to text file using the script

Hi all, I am beginner in UNIX...I want to use unix shell script to create text.file...I know how to use using by command...can anybody tell me for the script? Thanks i changed the threads title from "tex file" to "text file", because "tex" would probably be misunderstood as reference to... (4 Replies)
Discussion started by: mastercar
4 Replies

8. UNIX for Dummies Questions & Answers

Shell script to search for text in a file and copy file

Compete noob question.... I need a script to search through a directory and find files containing text string abcde1234 for example and then copy that file with that text string to another directory help please :eek: (9 Replies)
Discussion started by: imeadows
9 Replies

9. Shell Programming and Scripting

how to substitute more than one word in a text file?

well i have this file here: <XML> <pregate xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <system_c>HPREGATE</system_c> <trans_c>HSPG</trans_c> <trans_dt>20060105161333</trans_dt> <user_id_m></user_id_m> <func_c>C</func_c> </pregate> </XML> i want to... (2 Replies)
Discussion started by: forevercalz
2 Replies

Featured Tech Videos