Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Need to merge lines based on pattern

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Need to merge lines based on pattern
# 1  
Old 05-06-2013
Need to merge lines based on pattern
Hi,
I have a requirement to merge multiple lines based on search pattern. The search criteria is : it will search for CONSTRAINT and when it found CONSTRAINT, it will merge all lines to 1 line till it founds blank line.

For Example:
Code:
CREATE TABLE "AMS_DISTRIBUTOR_XREF"
   (    
    "SOURCE" VARCHAR2(3) NOT NULL ENABLE,
    "USER_CHANGED" VARCHAR2(30) NOT NULL ENABLE,
    "DATE_CHANGED" DATE NOT NULL ENABLE,
     CONSTRAINT "PK_AMS_DISTRIBUTOR_XREF" PRIMARY KEY ("DISTRIBUTOR_ID
     ", ROLE_CODE
, "PARTICIPANT_ID")

  ) TABLESPACE "TBLSP01" ;
  
CREATE TABLE AMS_LTV_STATUS"
   (    "INST_ID" NUMBER(10,0) NOT NULL ENABLE,
    "MPAN" NUMBER(13,0) NOT NULL ENABLE,
    "LTV_START_DATE" DATE,
     CONSTRAINT "AMS_LTV_STATUS_PK" PRIMARY KEY ("INST_ID")
 
 )   TABLESPACE "TBLSP02";
 
CREATE TABLE "CNT_PRO_PARTY_BANK_ACC_DETAILS"
   (    "BANK_ACCOUNT_NO" VARCHAR2(18),
    "BANK_DETAILS_ID" VARCHAR2(4) NOT NULL ENABLE,
    "ACCOUNT_HOLDER_NAME" VARCHAR2(60),
    "OWNER" VARCHAR2(20),
     CONSTRAINT "CNT_PRO_BANK_DTLS_PK" PRIMARY KEY ("MSP_PARTY_ID", "BANK_DETAILS_I
D")
  
  ) TABLESPACE "TBLSP02" ;

The o/p should be:
----------------------
Code:
CREATE TABLE "AMS_DISTRIBUTOR_XREF"
   (   
    "SOURCE" VARCHAR2(3) NOT NULL ENABLE,
    "USER_CHANGED" VARCHAR2(30) NOT NULL ENABLE,
    "DATE_CHANGED" DATE NOT NULL ENABLE,
     CONSTRAINT "PK_AMS_DISTRIBUTOR_XREF" PRIMARY KEY ("DISTRIBUTOR_ID", ROLE_CODE, "PARTICIPANT_ID")
  ) TABLESPACE "TBLSP01" ;
 
CREATE TABLE AMS_LTV_STATUS"
   (    "INST_ID" NUMBER(10,0) NOT NULL ENABLE,
    "MPAN" NUMBER(13,0) NOT NULL ENABLE,
    "LTV_START_DATE" DATE,
     CONSTRAINT "AMS_LTV_STATUS_PK" PRIMARY KEY ("INST_ID") 
 )   TABLESPACE "TBLSP02";
 
CREATE TABLE "CNT_PRO_PARTY_BANK_ACC_DETAILS"
   (    "BANK_ACCOUNT_NO" VARCHAR2(18),
    "BANK_DETAILS_ID" VARCHAR2(4) NOT NULL ENABLE,
    "ACCOUNT_HOLDER_NAME" VARCHAR2(60),
    "OWNER" VARCHAR2(20),
     CONSTRAINT "CNT_PRO_BANK_DTLS_PK" PRIMARY KEY ("MSP_PARTY_ID", "BANK_DETAILS_ID")
  ) TABLESPACE "TBLSP02" ;

I am trying the below command but its not working for all scenarios.
Code:
sed -e '/CONSTRAINT*/N' -e 's/\n//' sample.txt

Thanks in advance.

Cheers,
SatyaSmilie
Last edited by Franklin52; 05-06-2013 at 08:44 AM.. Reason: Please use code tags
# 2  
Old 05-06-2013
An approach using awk program:
Code:
awk '/CONSTRAINT/{ORS=FS}/^[ \t]*$/{ORS=RS}1' sample.txt

# 3  
Old 05-06-2013
Code:
$ sed "/CONSTRAINT/,/^ *$/{H;/^ *$/{x;s/\n//gp;};d;}" file
CREATE TABLE "AMS_DISTRIBUTOR_XREF"
   (
    "SOURCE" VARCHAR2(3) NOT NULL ENABLE,
    "USER_CHANGED" VARCHAR2(30) NOT NULL ENABLE,
    "DATE_CHANGED" DATE NOT NULL ENABLE,
     CONSTRAINT "PK_AMS_DISTRIBUTOR_XREF" PRIMARY KEY ("DISTRIBUTOR_ID     ", ROLE_CODE, "PARTICIPANT_ID")
  ) TABLESPACE "TBLSP01" ;

CREATE TABLE AMS_LTV_STATUS"
   (    "INST_ID" NUMBER(10,0) NOT NULL ENABLE,
    "MPAN" NUMBER(13,0) NOT NULL ENABLE,
    "LTV_START_DATE" DATE,
     CONSTRAINT "AMS_LTV_STATUS_PK" PRIMARY KEY ("INST_ID")
 )   TABLESPACE "TBLSP02";

CREATE TABLE "CNT_PRO_PARTY_BANK_ACC_DETAILS"
   (    "BANK_ACCOUNT_NO" VARCHAR2(18),
    "BANK_DETAILS_ID" VARCHAR2(4) NOT NULL ENABLE,
    "ACCOUNT_HOLDER_NAME" VARCHAR2(60),
    "OWNER" VARCHAR2(20),
      CONSTRAINT "CNT_PRO_BANK_DTLS_PK" PRIMARY KEY ("MSP_PARTY_ID", "BANK_DETAILS_ID")
  ) TABLESPACE "TBLSP02" ;

# 4  
Old 05-06-2013
None of the example above gives correct output compare to example.
OP like this:
Code:
     CONSTRAINT "PK_AMS_DISTRIBUTOR_XREF" PRIMARY KEY ("DISTRIBUTOR_ID", ROLE_CODE, "PARTICIPANT_ID")

not this:
Code:
     CONSTRAINT "PK_AMS_DISTRIBUTOR_XREF" PRIMARY KEY ("DISTRIBUTOR_ID     ", ROLE_CODE, "PARTICIPANT_ID")

Edit: This works better but miss the #1 tab.
Code:
awk '/CONSTRAINT/{ORS="";a=1} !NF{ORS=RS;a=0} a{$1=$1}1'

CREATE TABLE "AMS_DISTRIBUTOR_XREF"
   (
    "SOURCE" VARCHAR2(3) NOT NULL ENABLE,
    "USER_CHANGED" VARCHAR2(30) NOT NULL ENABLE,
    "DATE_CHANGED" DATE NOT NULL ENABLE,
CONSTRAINT "PK_AMS_DISTRIBUTOR_XREF" PRIMARY KEY ("DISTRIBUTOR_ID", ROLE_CODE, "PARTICIPANT_ID")
  ) TABLESPACE "TBLSP01" ;

Edit2: Got it working
Code:
awk 'a{b=1} /CONSTRAINT/{ORS="";a=1} !NF{ORS=RS;a=0;b=0} b{$1=$1}1'

CREATE TABLE "AMS_DISTRIBUTOR_XREF"
   (
    "SOURCE" VARCHAR2(3) NOT NULL ENABLE,
    "USER_CHANGED" VARCHAR2(30) NOT NULL ENABLE,
    "DATE_CHANGED" DATE NOT NULL ENABLE,
     CONSTRAINT "PK_AMS_DISTRIBUTOR_XREF" PRIMARY KEY ("DISTRIBUTOR_ID", ROLE_CODE, "PARTICIPANT_ID")
) TABLESPACE "TBLSP01" ;

Last edited by Jotne; 05-06-2013 at 05:04 PM..
# 5  
Old 05-06-2013
Jotne, that is a good catch Smilie

Here is modified code:
Code:
awk '/CONSTRAINT/{ORS=""}/^[ \t]*$/{ORS=RS}{gsub(/[ ]*"\,/,"\",")}1' file

# 6  
Old 05-06-2013
Is there are reason for you to use /^[ \t]*$/, search for lines containing only spaces or tabs.
Since default filed separators are tab and space, i do like to use !NF, number of fields=0.

PS, you do not need a single space in square brackets, this works fine gsub(/ *"\,/,"\",").
Last edited by Jotne; 05-06-2013 at 05:55 PM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Merge lines based on match

I am trying to merge two lines to one based on some matching condition. The file is as follows: Matches filter: 'request ', timestamp, <HTTPFlow request=<GET: Matches filter: 'request ', timestamp, <HTTPFlow request=<GET: Matches filter: 'request ', timestamp, <HTTPFlow ... (8 Replies)
Discussion started by: jamie_123
8 Replies

2. Shell Programming and Scripting

Merge mutiple lines into one based on if the first word is some particular value

Hi, trying to knock something together to create one line entries based on whether the first word on each line matches a particular value. eg. Link,"Name=""Something\something"","Timeout=""1800""", "Target=""\\thing\thing\thing""","State=""ONLINE""",something,... (0 Replies)
Discussion started by: adamdb
0 Replies

3. Shell Programming and Scripting

How to merge lines based off of text?

Hello Everyone, I have two files, similar to the following: File 1: 8010 ITEM01 CODE1 FLAG1 filler filler 7020 OBJECT CODE2 FLAG2 filler 6010 THING1 CODE4 FLAG4 6011 ITEM20 CODE7 FLAG7 File 2 contains: 6020 ITEM01 CODEA FLAGA filler filler filler 7000 OBJECT CODEB... (2 Replies)
Discussion started by: jl487
2 Replies

4. Shell Programming and Scripting

Merge file lines based off of keyword

Hello Everyone, I have two files I created in a format similar to the ones found below (character position is important): File 1: 21 Cat Y N S Y Y N N FOUR LEGS TAIL WHISKERS 30 Dog N N 1 Y Y N N FOUR LEGS TAIL 33 Fish Y N 1 Y Y N N FINS 43 CAR Y N S Y Y N N WHEELS DOORS... (7 Replies)
Discussion started by: jl487
7 Replies

5. Shell Programming and Scripting

Merge lines if pattern matches in ksh

I have a file like this. Pls help me to solve this . (I should look for only Message : 111 and need to print the start time to end time Need to ignore other type of messages. Ex: if first message is 111 and second message is 000 or anything else then ignore the 2nd one and print start time of the... (1 Reply)
Discussion started by: mnjx
1 Replies

6. Shell Programming and Scripting

merge same pattern lines together

Hi people... I normally find with out any problem the solutions I need just by searching. But for this I'm not having any joy or jsut failing to adapt what I'ev found to work. I have applciation report that doesn't allow for manipulation at creation so I want to do some post modifcation... (2 Replies)
Discussion started by: nhatch
2 Replies

7. Shell Programming and Scripting

Merge lines from one file if pattern matches

I have one comma separated file (a.txt) with two or more records all matching except for the last column. I would like to merge all matching lines into one and consolidate the last column, separated by ":". Does anyone know of a way to do this easily? I've searched the forum but most talked... (6 Replies)
Discussion started by: giannicello
6 Replies

8. Shell Programming and Scripting

Merge lines in text file based on pattern

Hello, I have searched forum trying to find a solution to my problem, but could not find anything or I did not understand the examples.... I should say, I am very inexperienced with text processing. I have a text file with approx 60k lines in it. I need to merge lines based on the number... (8 Replies)
Discussion started by: Bertik
8 Replies

9. Shell Programming and Scripting

Merge two file data together based on specific pattern match

My input: File_1: 2000_t g1110.b1 abb.1 2001_t g1111.b1 abb.2 abb.2 g1112.b1 abb.3 2002_t . . File_2: 2000_t Ali england 135 abb.1 Zoe british 150 2001_t Ali england 305 g1111.b1 Lucy russia 126 (6 Replies)
Discussion started by: patrick87
6 Replies

10. Shell Programming and Scripting

Merge lines in Flat file based on first 5 characters

Hi I have the fixed width flat file having the following data 12345aaaaaaaaaabbbbbbbbbb 12365sssssssssscccccccccc 12365sssss 12367ddddddddddvvvvvvvvvv 12367 vvvvv Here the first column is length 5 second is length 10 third is length 10 if the second or third column exceeds... (3 Replies)
Discussion started by: Brado
3 Replies
Login or Register to Ask a Question