Visit Our UNIX and Linux User Community


Joining broken lines with awk or perl


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Joining broken lines with awk or perl
# 1  
Old 04-23-2013
Joining broken lines with awk or perl

Hi,
I have a huge file with sql broken statements like:


Code:
 
PP3697HB @@@@0
<<<<<<Record has been deleted as per PP3697HB>>>>>>
FROM sys.xtab_ref rc,sys.xtab_sys f,sys.domp ur WHE
RE rc.milf = ur.milf  AND rc.molf = f.molf AND ur.dept = 'SWIT'AND ur
.department = 'IND' AND share = '2' AND ur.status = 'DONE' AND f.s
tatus = 'TRUE' AND rc.OPERATOR = '=' AND rc.VALUE = '261366'AND rc.r
unet IN (SELECT milf FROM sys.domp WHERE change = 'OVO' A
ND IND = 75);

I need all these broken lines to be recombined to a single line.

The line should look like:

Code:
 
PP3697HB @@@@0<<<<<<Record has been deleted as per PP3697HB>>>>>>FROM sys.xtab_ref rc,sys.xtab_sys f,sys.domp ur WHERE rc.milf = ur.milf  AND rc.molf = f.molf AND ur.dept = 'SWIT'AND ur.department = 'IND' AND share = '2' AND ur.status = 'DONE' AND f.status = 'TRUE' AND rc.OPERATOR = '=' AND rc.VALUE = '261366'AND rc.runet IN (SELECT milf FROM sys.domp WHERE change = 'OVO' AND IND = 75);

How can I achieve this in perl/awk.

We can say that the start of the line must be '^PP(.*)' and the end of sql statement must be '(.*);$'

Let me know if you have difficulty understand the problem and I will try to explain again.

Last edited by som.nitk; 04-23-2013 at 06:37 AM.. Reason: More info
# 2  
Old 04-23-2013
Code:
perl -ne 'chomp; (/^PP/../;$/) && print' file

# 3  
Old 04-23-2013
Code:
$ cat sql.txt
PP3697HB @@@@0
<<<<<<Record has been deleted as per PP3697HB>>>>>>
FROM sys.xtab_ref rc,sys.xtab_sys f,sys.domp ur WHE
RE rc.milf = ur.milf  AND rc.molf = f.molf AND ur.dept = 'SWIT'AND ur
.department = 'IND' AND share = '2' AND ur.status = 'DONE' AND f.s
tatus = 'TRUE' AND rc.OPERATOR = '=' AND rc.VALUE = '261366'AND rc.r
unet IN (SELECT milf FROM sys.domp WHERE change = 'OVO' A
ND IND = 75);
PP3697HB @@@@1
<<<<<<Record has been deleted as per PP3697HC>>>>>>
FROM sys.xtab_ref rc,sys.xtab_sys f,sys.domp ur WHE
RE rc.milf = ur.milf  AND rc.molf = f.molf AND ur.dept = 'SWIT'AND ur
.department = 'IND' AND share = '2' AND ur.status = 'DONE' AND f.s
tatus = 'TRUE' AND rc.OPERATOR = '=' AND rc.VALUE = '261366'AND rc.r
unet IN (SELECT milf FROM sys.domp WHERE change = 'OVO' A
ND IND = 76);

Code:
$ sed -n '/^PP/ { p; n; p; n; :k N; /;$/ { s/\n//g; p; d; }; bk }' sql.txt
PP3697HB @@@@0
<<<<<<Record has been deleted as per PP3697HB>>>>>>
FROM sys.xtab_ref rc,sys.xtab_sys f,sys.domp ur WHERE rc.milf = ur.milf  AND rc.molf = f.molf AND ur.dept = 'SWIT'AND ur.department = 'IND' AND share = '2' AND ur.status = 'DONE' AND f.status = 'TRUE' AND rc.OPERATOR = '=' AND rc.VALUE = '261366'AND rc.runet IN (SELECT milf FROM sys.domp WHERE change = 'OVO' AND IND = 75);
PP3697HB @@@@1
<<<<<<Record has been deleted as per PP3697HC>>>>>>
FROM sys.xtab_ref rc,sys.xtab_sys f,sys.domp ur WHERE rc.milf = ur.milf  AND rc.molf = f.molf AND ur.dept = 'SWIT'AND ur.department = 'IND' AND share = '2' AND ur.status = 'DONE' AND f.status = 'TRUE' AND rc.OPERATOR = '=' AND rc.VALUE = '261366'AND rc.runet IN (SELECT milf FROM sys.domp WHERE change = 'OVO' AND IND = 76);

# 4  
Old 04-23-2013
Code:
$
$
$ cat -n broken.sql
     1  PP3697HB @@@@0
     2  <<<<<<Record has been deleted as per PP3697HB>>>>>>
     3  FROM sys.xtab_ref rc,sys.xtab_sys f,sys.domp ur WHE
     4  RE rc.milf = ur.milf  AND rc.molf = f.molf AND ur.dept = 'SWIT'AND ur
     5  .department = 'IND' AND share = '2' AND ur.status = 'DONE' AND f.s
     6  tatus = 'TRUE' AND rc.OPERATOR = '=' AND rc.VALUE = '261366'AND rc.r
     7  unet IN (SELECT milf FROM sys.domp WHERE change = 'OVO' A
     8  ND IND = 75);
     9  PP3697HB @@@@1
    10  <<<<<<Record has been deleted as per PP3697XY>>>>>>
    11  FROM sys.xtab_ref rc,sys.xtab_sys f,sys.domp ur WHE
    12  RE rc.milf = ur.milf  AND rc.molf = f.molf AND ur.dept = 'SWIT'AND ur
    13  .department = 'IND' AND share = '2' AND ur.status = 'DONE' AND f.s
    14  tatus = 'TRUE' AND rc.OPERATOR = '=' AND rc.VALUE = '261367'AND rc.r
    15  unet IN (SELECT milf FROM sys.domp WHERE change = 'OVO' A
    16  ND IND = 85);
$
$
$ perl -lne 'BEGIN {undef $/} while (/^(PP.*?;)$/msg) { ($x=$1) =~ s/\n//g; print $x}' broken.sql
PP3697HB @@@@0<<<<<<Record has been deleted as per PP3697HB>>>>>>FROM sys.xtab_ref rc,sys.xtab_sys f,sys.domp ur WHERE rc.milf = ur.milf  AND rc.molf = f.molf AND ur.dept = 'SWIT'AND ur.department = 'IND' AND share = '2' AND ur.status = 'DONE' AND f.status = 'TRUE' AND rc.OPERATOR = '=' AND rc.VALUE = '261366'AND rc.runet IN (SELECT milf FROM sys.domp WHERE change = 'OVO' AND IND = 75);
PP3697HB @@@@1<<<<<<Record has been deleted as per PP3697XY>>>>>>FROM sys.xtab_ref rc,sys.xtab_sys f,sys.domp ur WHERE rc.milf = ur.milf  AND rc.molf = f.molf AND ur.dept = 'SWIT'AND ur.department = 'IND' AND share = '2' AND ur.status = 'DONE' AND f.status = 'TRUE' AND rc.OPERATOR = '=' AND rc.VALUE = '261367'AND rc.runet IN (SELECT milf FROM sys.domp WHERE change = 'OVO' AND IND = 85);
$
$
$
$

# 5  
Old 04-23-2013
Quote:
Originally Posted by durden_tyler
Code:
$
$
$ cat -n broken.sql
     1  PP3697HB @@@@0
     2  <<<<<<Record has been deleted as per PP3697HB>>>>>>
     3  FROM sys.xtab_ref rc,sys.xtab_sys f,sys.domp ur WHE
     4  RE rc.milf = ur.milf  AND rc.molf = f.molf AND ur.dept = 'SWIT'AND ur
     5  .department = 'IND' AND share = '2' AND ur.status = 'DONE' AND f.s
     6  tatus = 'TRUE' AND rc.OPERATOR = '=' AND rc.VALUE = '261366'AND rc.r
     7  unet IN (SELECT milf FROM sys.domp WHERE change = 'OVO' A
     8  ND IND = 75);
     9  PP3697HB @@@@1
    10  <<<<<<Record has been deleted as per PP3697XY>>>>>>
    11  FROM sys.xtab_ref rc,sys.xtab_sys f,sys.domp ur WHE
    12  RE rc.milf = ur.milf  AND rc.molf = f.molf AND ur.dept = 'SWIT'AND ur
    13  .department = 'IND' AND share = '2' AND ur.status = 'DONE' AND f.s
    14  tatus = 'TRUE' AND rc.OPERATOR = '=' AND rc.VALUE = '261367'AND rc.r
    15  unet IN (SELECT milf FROM sys.domp WHERE change = 'OVO' A
    16  ND IND = 85);
$
$
$ perl -lne 'BEGIN {undef $/} while (/^(PP.*?;)$/msg) { ($x=$1) =~ s/\n//g; print $x}' broken.sql
PP3697HB @@@@0<<<<<<Record has been deleted as per PP3697HB>>>>>>FROM sys.xtab_ref rc,sys.xtab_sys f,sys.domp ur WHERE rc.milf = ur.milf  AND rc.molf = f.molf AND ur.dept = 'SWIT'AND ur.department = 'IND' AND share = '2' AND ur.status = 'DONE' AND f.status = 'TRUE' AND rc.OPERATOR = '=' AND rc.VALUE = '261366'AND rc.runet IN (SELECT milf FROM sys.domp WHERE change = 'OVO' AND IND = 75);
PP3697HB @@@@1<<<<<<Record has been deleted as per PP3697XY>>>>>>FROM sys.xtab_ref rc,sys.xtab_sys f,sys.domp ur WHERE rc.milf = ur.milf  AND rc.molf = f.molf AND ur.dept = 'SWIT'AND ur.department = 'IND' AND share = '2' AND ur.status = 'DONE' AND f.status = 'TRUE' AND rc.OPERATOR = '=' AND rc.VALUE = '261367'AND rc.runet IN (SELECT milf FROM sys.domp WHERE change = 'OVO' AND IND = 85);
$
$
$
$

Slurping in the whole file may not work in this case as the OP mentions that the input file is huge.
The following should do the trick (depending on the input) without being memory-intensive:
Code:
perl -ln0x3B -e 'if(/^\n?PP/) { s/\n//g; print $_, $/}' file


Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk joining multiple lines based on field count

Hi Folks, I have a file with fields as follows which has last field in multiple lines. I would like to combine a line which has three fields with single field line for as shown in expected output. Please help. INPUT hname01 windows appnamec1eda_p1, ... (5 Replies)
Discussion started by: shunya
5 Replies

2. Shell Programming and Scripting

Joining broken lines and removing empty lines

Hi - I have req to join broken lines and remove empty lines but should NOT be in one line. It has to be as is line by line. The challenge here is there is no end of line/start of line char. thanks in advance Source:- 2003-04-34024|04-10-2003|Claims|Claim|01-13-2003|Air Bag:Driver;... (7 Replies)
Discussion started by: Jackceasar123
7 Replies

3. Shell Programming and Scripting

Joining lines in different way

Hi all, I'm excited to the part of unix.com forum, and noob to it. I have an query, where I have an file and it contains data like this use thread when posting do no I was expecting the result as use thread thread when when posting posting do do no use thread when thread when... (6 Replies)
Discussion started by: Jose Nirmal
6 Replies

4. Programming

Perl : joining alternate lines

Hi, I need to join every alternate line in a file for eg:input file $ cat abc abc def ghi jkloutput abc def ghi jklcode i wrote for this $ cat add_line.pl #!/usr/bin/perl -w my $count=1; #my $line=undef; my @mem_line; my $i=0; my $x=0; (2 Replies)
Discussion started by: sam05121988
2 Replies

5. Shell Programming and Scripting

Joining broken lines

I have a plain test file with a delimeter ''. In this file some lines are broken into two. The first part of these broken line will have 6 columns and the second part will have 4. These broken lines will be consicutive. I want to join the two consicutive lines which are having 6 fields and 4... (8 Replies)
Discussion started by: ratheeshjulk
8 Replies

6. Shell Programming and Scripting

Joining lines in a text file using AWK or SED

Hi All I'm struggling a bit here :( I need a way of joining lines contained in a text file. I've seen numerous SED and AWK examples and none of them seem to be working for me. The text file has 4 lines: DELL1427 DOC 30189342 79 Now bear with me on this one as I'm actually... (4 Replies)
Discussion started by: huskie69
4 Replies

7. Shell Programming and Scripting

awk joining lines

Hello, I'm trying to write a piece of code in awk, which should be able recognize by some regexps two lines and then join them together (maybe write them without \n would be enough, I don't know).. the thing is that every line in the file i'm working with starts with some number, for example: ... (4 Replies)
Discussion started by: midin
4 Replies

8. Shell Programming and Scripting

pattern matching lines using the date, and then joining the lines

Hi Guys, Was trying to attempt the below using awk and sed, have no luck so far, so any help would be appreciated. Current Text File: The first line has got an "\n", and the second line has got spaces/tabs then the word and "\n" TIME SERVER/CLIENT TEXT... (6 Replies)
Discussion started by: eo29
6 Replies

9. Shell Programming and Scripting

Need help joining lines

Hi All, I need the command to join 2 lines into one. I found lots of threads but none give me the sollution. Probably because unix scripting is one of my best features ;) I got a logfile where line 2 needs to be joined with line 1, lines 4 needs to be joined with line 3 etc If you need... (16 Replies)
Discussion started by: rene21976
16 Replies

10. Shell Programming and Scripting

awk / shell - Fix broken lines and data

Gurus, I am struggling with a issue and thought I could use some of your expertise. Need Help with this I have a flat file that has millions of records 24|john|account ~ info |56| 25|kuo|account ~ journal |58| 27|kim|account ~ journal |59| 28|San|account ~ journal |60|... (3 Replies)
Discussion started by: rimss
3 Replies

Featured Tech Videos