27

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.

43

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' ...)
  • 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? – pmaipmui Jun 11 '15 at 18:34
  • How to delete 3rd line from the last using shell commands? – pmaipmui Jun 11 '15 at 18:46
  • @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. – user43791 Jun 11 '15 at 19:04
  • @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 Jun 12 '15 at 21:38
8

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.

  • gnu tail also supports the extended tail -n+<num> syntax meaning "start from line <num>" – UloPe Jul 1 at 15:00
4

I'm not going to answer how to delete a number of lines. I'm going to attack the problem this way:

grep -v '#SQL>' Element_query >outfile

Instead of counting lines, it eliminates the SQL commands by recognizing the prompts. This solution can then be generalized for other output files of SQL sessions with more commands than just two.

  • 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 Jun 11 '15 at 20:09
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.

3

There are several ways to remove leading and trailing line(s) from a file.

You can use awk as it handles both pattern matching and line counting,

#you need to know length to skip last line, assume we have 100 lines
awk 'NR>1 && NR<100 {print $0};' < inputfile
#or
awk '/SQL/ {next}; {print $0;};' < inputfile |awk 'NR>1&& NR<10 {print $0};'

You can use grep -v to exclude lines you don't want by pattern, and you can match multiple patterns using the -E option,

grep -v -E "SQL>" < inputfile > outputfile

You can use head and tail to trim specific counts of lines,

lines=$((`wc -l < inputfile`-2)) #how many lines in file, less 2
head -n-1 < inputfile | head -n$lines > outputfile
#or
tail -n+2 < inputfile | head -n$lines > outputfile

You could use vi/vim, and delete the first and last line(s),

vi inputfile
:1
dd
:$
dd
:w! outputfile
:x

you could use a perl script, skip the first line, save each line, print when you get a next line,

#left as exercise for the reader :-)
  • 1
    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. – mikeserv Jun 13 '15 at 5:17
3

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.

3

You can select the lines between a range in awk (this assumes you know how many lines there are):

awk 'NR>1 && NR < 3' file

Or in Perl:

perl -ne 'print if $.>1 && $.<3' file

If you don't know how many lines there are, you can calculate it on the fly using grep (note that this will not count blank lines, use grep -c '' file to count them as well):

awk -vm="$(grep -c . file2.txt)" 'NR>1 && NR<m' file2.txt
2

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.

  • This solution is incorrect as it prints the first line. – xhienne Mar 29 '17 at 0:28
  • 1
    @xhienne Sorry, it was a mistake. I wrote 1 instead of 2 as the parameter of "tail". Now it works, thanks! :) – Gabrer Mar 29 '17 at 11:37
1

Using awk:

< inputfile awk 'NR>1 {print r}; {r=$0}' > outputfile
  • < inputfile: redirects the content of inputfile to awk's stdin
  • > outputfile: redirects the content of awk's stdout to outputfile
  • NR>1: executes the following actions only if the number of the record being processed is greater than 1
  • {print r}: prints the content of the variable r
  • {r=$0}: assigns the content of the record being processed to the variable r

So at the first execution of the awk script, the first block of actions is not executed, while the second block of actions is executed and the content of the record is assigned to the variable r; at the second execution, the first block of actions is executed, and the content of the variable r is printed (so the previous record is printed); this has the effect of printing each processed lines but the first one and the last one.

  • You are not excluding the first line. At NR==2, you print the first line of input which stored in r. – xhienne Mar 29 '17 at 0:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.