read file: pickup every text between the pattern including start and end pattern and create new file for each pattern set

Question

I have a file called file.txt on Linux, containing:

sqlplus -s insert into table;  
commit;  
!  
sqlplus -s insert into table;  
commit;  
!  
sqlplus -s insert into table;  
commit;  
!
  
.  
.   

I want to create multiple files as shown below:

File1.txt:-

sqlplus -s insert into table;  
commit;  
!   

File2.txt

sqlplus -s insert into table;  
commit;  
!   

Answer 1

Using awk

$ awk '/^sqlplus/ {close(sql);sql="file"++c".txt"} {print > sql}' input_file
$ head *
==> file1.txt <==
sqlplus -s insert into table;
commit;
!

==> file2.txt <==
sqlplus -s insert into table;
commit;
!

==> file3.txt <==
sqlplus -s insert into table;
commit;
!

.
.

Answer 2

Typical job for awk:

START_PATTERN='^sqlplus -s' END_PATTERN='^!$' awk '
  !file && $0 ~ ENVIRON["START_PATTERN"] {
    file = sprintf("file%03d.txt", ++n)
  }
  file {
    print > file
    if ($0 ~ ENVIRON["END_PATTERN"]) {
      file = ""
      close(file)
    }
  }' < your-file

That approach allows lines to match both start and end patterns (not possible in your case).

Answer 3

If it is enough to make one file for every three lines of the original input file, you can do (with GNU awk):

awk 'NR%3==1{a++} {print > "file"a".txt"}' file.txt 

Running this on your data produces:

$ ls file?.txt 
file1.txt  file2.txt  file3.txt

Answer 4

A much simpler solution with csplit

csplit infile -z -f 'file' '/^sqlplus/' '{*}'


╰─ → $ cat file00
sqlplus -s insert into table;
commit;
!
╰─ → $ cat file01
sqlplus -s insert into table;
commit;
!
╰─ → $ cat file02
sqlplus -s insert into table;
commit;
!

-f is used to set a prefix and -z to suppress empty files.