10-08-2007
(sed) parsing insert statement column that crosses multiple lines
I have a file with a set of insert statements some of which have a single column value that crosses multiple lines causing the statement to fail in sql*plue. Can someone help me with a sed script to replace the new lines with chr(10)?
here is an example:
insert into mytable(id, field1, field2)
values ('1', 'myvarchar', 'my varchar
that has carriage returns, and is making
my script die');
insert into mytable(id, field1, field2)
values ('2', 'myvarchar2', 'my varchar
that has carriage returns, and is making
my script die. This one is even trickier because
it contains a quote that I didn't expect.
');
Note that the value for field2 (third column) crosses multiple lines. The first case I would need to replace each new line with:
' || chr(10)
and the start of the following line with:
||'
resulting in:
insert into mytable(id, field1, field2)
values ('1', 'myvarchar', 'my varchar '||chr(10)||'that has carriage '|| chr(10) ||'returns, and is making '|| chr(10) ||' my script die');
In the second case I would need to do the same as the first case with the following addtions.
1. I need to handle the single quote by using '''
2. because the last line doesn't have any text I can replace the last carriage return with
'||chr(10)
resulting in:
insert into mytable(id, field1, field2)
values ('2', 'myvarchar2', 'my varchar '||chr(10)|| 'that has carriage returns, and is making '||chr(10)||'my script die. This one is even trickier because '||chr(10)||'it contains a quote that I didn't expect.');
This has really stumped me and I am hoping there are some guru's out there that can help me out. So far I have come up with this script that prints the lines that I want to process:
/,.*'[^';]*$/p
The way I read it is search for a "," followed by 0 or more characters (I wanted just whitespace but \s doesn't work) followed by a single quote and 0 or more characters but not ' ; before the end of the line. Put simply, a single quote optional characters but not the end quote nor end statement before the end of the line.
So I expect that this finds the start of patterns like in the case #1 now I need to find the end of the pattern and process the chunk like I mentioned above.
Anyway, I'd really appreciate some help. T.I.A.
JJ
10 More Discussions You Might Find Interesting
1. Shell Programming and Scripting
Hi all,
I've got some problems with editing a big configuration file .. its about 2k lines long.. anyway what I need is to place certain text to certain line number.. lets say I need to place "Something" on line 980 .. "something" else on line number 1500 and so on without tempering the rest of... (12 Replies)
Discussion started by: c0mrade
12 Replies
2. Shell Programming and Scripting
I'm attempting to insert multiple lines before a line matching a given search pattern. These lines are generated in a separate function and can either be piped in as stdout or read from a temporary file.
I've been able to insert the lines from a file after the pattern using:
sed -i '/pattern/... (2 Replies)
Discussion started by: zksailor534
2 Replies
3. Shell Programming and Scripting
Hi all
I've been working on a bash script parsing through debug/trace files and extracting all lines that relate to some search string. So far, it works pretty well. However, I am challenged by one requirement that is still open.
What I want to do:
1) parse through a file and identify all... (3 Replies)
Discussion started by: reminder
3 Replies
4. Shell Programming and Scripting
I'm trying to use sed to insert data at a specific column, let's say my data looks like this:
0553 1828
0552 1829
0550 1829
0549 1830
0548 1831
what I want is this:
timein 0553 timeout 1828
timein 0552 timeout 1829
timein 0550 timeout 1829
timein 0549 timeout 1830
timein 0548... (5 Replies)
Discussion started by: mswartz
5 Replies
5. Shell Programming and Scripting
I have done this sed command to insert one line after a specific string is found:
sed '/patternstring/ a\
new line string' file1
But how do I insert two lines? This is not possible:
sed '/patternstring/ a\
new line string \a
new line string 2' file1 (2 Replies)
Discussion started by: locoroco
2 Replies
6. Shell Programming and Scripting
Hi,
Input- a file comtaining a procedure or function with various statements in that one of the statement would be
insert into table1 (a,b,c) values (1,2,3)
ourput required
true if insert statement is using column name (a,b,c) else false.
Please suggest (8 Replies)
Discussion started by: manasa_vs
8 Replies
7. Shell Programming and Scripting
Hi,
I want to insert the text 'Unknown' in 2 specific columns in a csv file (actually | separated) if the column is blank. Its always the same columns.
I have tried using sed: sed "s/||/|Unknown|/g"
but there are occasion where other fields are blank and they need to be left blank. This... (4 Replies)
Discussion started by: ksexton
4 Replies
8. Shell Programming and Scripting
I have a text file with records of the form:
A X1 Y1 X2 Y2 X3 Y3
where A is character length 10, Xi is character length 4 and Yi is numeric length 10.
I want to parse the line, and output records like:
A X1 Y1
A X2 Y2
A X3 Y3
etc
Can anyone please give me an idea of how to do this. ... (4 Replies)
Discussion started by: wvdeijk
4 Replies
9. Shell Programming and Scripting
Hi folks,
I have a scenario to convert the update statements into insert statements using shell script (awk, sed...) or in database using regex.
I have a bunch of update statements with all columns in a file which I need to convert into insert statements.
UPDATE TABLE_A SET COL1=1 WHERE... (0 Replies)
Discussion started by: dev123
0 Replies
10. Shell Programming and Scripting
Hello, I am trying to insert a section of text between lines in another text file.
The new lines to be inserted are:
abcd.efgh.zzzz=blah
abcd.efgh.xxxx=blah
Where N = 0 to 2
Original File:
abcd.efgh.wwxx=aaaaa
abcd.efgh.yyzz=bbbbb
abcd.efgh.wwxx=aaaaa
abcd.efgh.yyzz=bbbbb... (3 Replies)
Discussion started by: tsu3000
3 Replies
LEARN ABOUT FREEBSD
column
COLUMN(1) BSD General Commands Manual COLUMN(1)
NAME
column -- columnate lists
SYNOPSIS
column [-tx] [-c columns] [-s sep] [file ...]
DESCRIPTION
The column utility formats its input into multiple columns. Rows are filled before columns. Input is taken from file operands, or, by
default, from the standard input. Empty lines are ignored.
The options are as follows:
-c Output is formatted for a display columns wide.
-s Specify a set of characters to be used to delimit columns for the -t option.
-t Determine the number of columns the input contains and create a table. Columns are delimited with whitespace, by default, or with
the characters supplied using the -s option. Useful for pretty-printing displays.
-x Fill columns before filling rows.
ENVIRONMENT
The COLUMNS, LANG, LC_ALL and LC_CTYPE environment variables affect the execution of column as described in environ(7).
EXIT STATUS
The column utility exits 0 on success, and >0 if an error occurs.
EXAMPLES
(printf "PERM LINKS OWNER GROUP SIZE MONTH DAY " ;
printf "HH:MM/YEAR NAME
" ;
ls -l | sed 1d) | column -t
SEE ALSO
colrm(1), ls(1), paste(1), sort(1)
HISTORY
The column command appeared in 4.3BSD-Reno.
BUGS
Input lines are limited to LINE_MAX (2048) bytes in length.
BSD
July 29, 2004 BSD