How do I delete the first n lines and last line of a file using shell commands?

Question

I have a file named Element_query containing the result of a query :

SQL> select count (*) from element;

[Output of the query which I want to keep in my file]

SQL> spool off;

I want to delete 1st line and last line using shell command.

Solution 1

Using GNU sed:

sed -i '1d;$d' Element_query

How it works :

• -i option edit the file itself. You could also remove that option and redirect the output to a new file or another command if you want.
• 1d deletes the first line (1 to only act on the first line, d to delete it)
• $d deletes the last line ($ to only act on the last line, d to delete it)

Going further :

• You can also delete a range. For example, 1,5d would delete the first 5 lines.
• You can also delete every line that begins with SQL> using the statement /^SQL> /d
• You could delete every blank line with /^$/d
• Finally, you can combine any of the statement by separating them with a semi-colon (statement1;statement2;satement3;...) or by specifying them separately on the command line (-e 'statement1' -e 'statement 2' ...)

Solution 2

head; head

{   head -n[num] >/dev/null
    head -n[num]
}  <infile >outfile

With the above you can specify the first number of lines to strip off of the head of the output w/ the first head command, and the number of lines to write to outfile with the second. It will also typically do this faster than sed - especially when input is large - despite requiring two invocations. Where sed definitely should be preferred though, is in the case that <infile is not a regular, lseekable file - because this will typically not work as intended in that case, but sed can handle all output modifications in a single, scripted process.

With a GNU head you can use the - negative form for [num] in the second command as well. In which case the following command will strip first and last lines from input:

{   head -n1 >/dev/null
    head -n-1
}  <infile >outfile

OR with a POSIX sed:

Say, for example, I was reading an input of 20 lines and I wanted to strip the first 3 and the last 7. If I resolved to do so w/ sed, I would do it with a tail buffer. I would first add together three and seven for a total strip count of ten and then do:

seq 20 | sed -ne:n -e '3d;N;1,10bn' -eP\;D

That is an example which strips the first 3 and last 7 lines from input. The idea is that you can buffer as many lines as you wish to strip from the tail of input in the pattern space on a stack but only Print the first of these for every line pulled in.

• On lines 1,10 sed Prints nothing because for each of those it is stacking input in pattern space line-by-line in a branch loop.
• On the 3rd line all of sed's stack is deleted - and so the first 3 lines are stripped from output in one fell swoop.
• When sed reaches the $last line of input and attempts to pull in the Next it hits EOF and stops processing entirely. But at that time pattern space contains all of lines 14,20 - none of which have yet been Printed, and never are.
• On every other line sed Prints only up to the first occurring \newline in pattern space, and Deletes same before beginning a new cycle with what remains - or the next 6 lines of input. The 7th line is appended again to the stack with the Next command in the new cycle.

And so, of seq's output (which is 20 sequentially numbered lines), sed only prints:

4
5
6
7
8
9
10
11
12
13

This gets to be problematic when the number of lines you desire to strip from the tail of input is large - because sed's performance is directly proportional to the size of its pattern space. Still, though, it is a viable solution in many cases - and POSIX specs a sed pattern space to handle at least 4kb before busting.

Solution 3

Try this solution:

tail -n +2 name_of_file | head -n-1

Customization

You can easily adapt it to delete the n first lines changing the +2 of tail;
or to delete the last n lines changing the -1 of head.

Solution 4

ed is 'the standard text editor' and should be available on systems that don't have GNU sed. It was originally designed as a text editor, but it is well-suited to scripting.

printf '%s\n' 1d '$d' w q | ed Element_query

1d deletes the first line of the file, $d (quoted so that the shell doesn't think it's a variable) deletes the last line, w writes the file and q quits ed. printf is here used to format the commands for ed -- each is required to be followed by a newline; there are of course other ways to accomplish this.

Solution 5

You'd be far better served by cutting away the SQL commands. You can do this in two ways:

1. If you are absolutely sure that the sequence "SQL>" does not occur anywhere else in the output,

   grep -v -F 'SQL> ' < infile > outfile
   

2. If you aren't as sure,

   grep -v '^SQL> .*;$' < infile > outfile
   

The second version is slower but more accurate: it will ignore lines exactly beginning with "SQL> " and ending in semicolon, which seem to describe the lines you want eliminated.

However, it would be better not to put that extra output in the file to begin with. Most SQL systems have some way of doing that. I'm not too conversant with Oracle, but maybe this answer might be helpful.

View more solutions

Comments

[Admin]

You're probably best off fixing this inside SQL*Plus; rather than generating a file and then trying to trim the stuff you don't want, you can just tell SQL*Plus not to generate that stuff to begin with. One approach is described in the "Creating a Flat File" section at docs.oracle.com/cd/A84870_01/doc/sqlplus.816/a75664/ch44.htm‌​; another approach is described at stackoverflow.com/q/2299375/978917.

[Nainita]

If its 3rd line to delete... then i have to use 3d in place of 1d? if its 3rd line from last to delete... then what will be the command?

[Nainita]

How to delete 3rd line from the last using shell commands?

[user43791]

@Nainita You can specify a range (1,3d will delete the first three lines) but it's a little more difficult for the end. Depending on what you want, you could be better off using this : sed -i '/^SQL> /d' Element_query to delete lines that begins with SQL> no matter where it is in the file.

[mikeserv]

I like it. I don't know much about SQL - but is there no chance of the prompts occurring at the head of its output lines otherwise?

[mikeserv]

@Nainita - see my answer here for arbitrary tail counts - it offers two solutions for stripping count lines as relative to the end of the file. One is a sed one-liner - which will work for stripping arbitrary line counts from the head and tail of a file, Better though, as long as input is a regular file, is just to group a single input across two head processes - it is the fastest way to do this usually.

[mikeserv]

For the heads you don't actually need the pipe, and, in fact it's better not to use it at all if you can get away with it. When you do head | head - while the two processes can run concurrently, they're also both processing practically all of the same data redundantly. If you do instead { head >dump; head >save; } <in you only skip by offset - the first reads 10 lines out to >dump and the second reads the next 10 lines out to >save.

[xhienne]

You are not excluding the first line. At NR==2, you print the first line of input which stored in r.

[xhienne]

This solution is incorrect as it prints the first line.

[Gabrer]

@xhienne Sorry, it was a mistake. I wrote 1 instead of 2 as the parameter of "tail". Now it works, thanks! :)

[UloPe]

gnu tail also supports the extended tail -n+<num> syntax meaning "start from line <num>"

[David Thomas]

I used sed -i '1d' table-backup.sql to delete the first line of the sql text file

[J Bourne]

is there any limit for the line number, as I tried with some 2.6GB text file, its not removing all the lines, sed -i '1,7020934d;$d' file.log