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);
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 );.
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.
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.
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)
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)
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)
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)
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)
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)
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)
