116

I need to remove the first 42 lines of a 2GB SQL dump.

I know I can view the first lines using:

head -n 44 dump.sql

But is there anyway to edit or remove them?

Kohjah Breese
  • 2,931

8 Answers8

166

If you want to just view the lines from the 43rd on you can use

tail -n +43 dump.sql

The + sign is important - without it, tail will print the last 43 lines instead. Alternatively with 'sed'

sed 1,42d dump.sql

If you want to really delete the first 42 lines from the original file then you can make sed make the change inplace with the -i option

sed -i 1,42d dump.sql
Binyamin
  • 103
steeldriver
  • 143,099
  • 1
    Awesome answer, fantastic use of tail. I found many times something new to learn from your answers. thanks. – sourav c. Jan 24 '14 at 19:20
  • 1
    Oh man tail -n +43 is a game changer! I've been using an awkward invocation of sed to the same effect. – pfctdayelise Jun 20 '17 at 03:16
  • 17
    What if you have no space left on device? sed -i 1,50000000d 17GigFile creates a temp file sedXYZ that consumes many more gigabytes. Is there an approach without temp files? – juanmf Oct 09 '18 at 20:48
  • What's the difference between tail -n +43 and head -n 44 as mentioned in the question? – Hashim Aziz Aug 26 '19 at 20:00
  • @juanmf You might try to do this with a gui tool (I've done it using Mousepad, but the file of interest was "only" ~700MB. Takes awhile for the file to load, though... – Digger Oct 02 '19 at 21:54
38

This seems to be the easiest:

sed '1,42d' test.sql > test2.sql

Remove lines 1-42 from test.sql and save as test2.sql

Kohjah Breese
  • 2,931
15

try this,

tail -n +43 dump.sql > dump_new.sql

ptantiku
  • 339
  • 1
  • 4
9

You can use Vim in Ex mode:

ex -s -c '1d42|x' dump.sql

  1. 1 move to first line

  2. 42 select 42 lines

  3. d delete

  4. x save and close

Zombo
  • 1
  • 4
    Does it create a temp file? Is it possible to do this when space left on device is less than file size? – juanmf Nov 14 '18 at 06:40
  • 4
    @juanmf All these solutions require a temporary file. It is only possible to remove data from the end of a file without using a temporary file. – PerlDuck Nov 14 '18 at 15:21
  • 3
    @PerlDuck could you please elaborate on why (or link ressource) "It is only possible to remove data from the end of a file without using a temporary file" ? – shrimpdrake Dec 09 '19 at 14:12
  • It'a a matter of how common filesystems works, not a matter of applications. Filesystems implementation normally includes callable functions to trunk a file; that's to say, eliminate the content of a file starting from a point in the file. This is accomplished deleting the list of blocks allocated to that file after that point and adjusting the file length in the file metadata. – gianfrus Dec 13 '24 at 20:43
  • But a function to change the starting block of a file to another would be almost useless: such a function could only set the starting block that includes the new begin of the file; it doesn’t exist a concept of “file starting offset”. – gianfrus Dec 13 '24 at 20:43
  • Indeed, the action is not impossible: one have to open the file in random access mode, read the file block-by-block from the new starting point overwriting at the same time the blocks at the begin of the file. But you have to code it in C, or something similar. – gianfrus Dec 13 '24 at 20:44
2

Because of sed discrepancies across Linux and Mac, I resolved to use tail -n +43 dump.sql > new.sql format.

Akash Agarwal
  • 260
  • Won't work becuase > dump.sql is interpreted by the shell before tail ... dump.sql reads the file, i.e. the file is flushed before tail opens it, hence the file is completely empty after that command. – PerlDuck Dec 09 '19 at 16:29
  • 1
    @PerlDuck I must have probably used another file name at the time of writing this. This should work. Thanks for pointing it out! – Akash Agarwal Dec 09 '19 at 23:47
1

Just to add this. If you're on a mac you need to add the backup extension. Answer from this post.

sed -i '.bak' 1,42d dump.sql
Jerinaw
  • 291
0

Sorry, I can't give you actual code right now. However, try looking at something along the lines of

tail -n arcv(`wc -l`) -44

What this should do (once properly formatted) is count the number of lines in the file (wc -l), subtract 44 from it (-44) and then print out everything starting with the 45th line in the file.

Hope this helps and good luck.

user.dz
  • 49,295
kb2bcg
  • 9
  • This is not quite optimal,calling wc -l on the file, you process it twice, whereas sed or tail process it only once. – yo' Jan 24 '14 at 18:36
0

Try this,

head -n 42 dump.sql > tmp; cat dump.sql | grep -vxf tmp > dump.sql.new; rm tmp

or,

a=$(cat dump.sql| wc -l); tail -n "$((a-42))" dump.sql > dump.sql.new
sourav c.
  • 46,280