5

How can I truncate after 1000 lines in a csv file? The goal is to keep only the first 1000 lines in the file and delete all others.

5
  • 2
    stackoverflow.com/questions/19017994/…
    – jtessier72
    Commented Nov 14, 2023 at 14:01
  • 1000 lines or 1000 rows? They are not the same thing, because cells can contain line breaks in CSV. Commented Nov 15, 2023 at 7:46
  • 3
    @aff: Self-answering is not only allowed but encouraged. In fact, there is a text field in the Question form that allows you to write the answer and the question at the same time. Commented Nov 15, 2023 at 17:19
  • 1
    It's OK to Ask and Answer Your Own Questions … So, those who know will refine it and those who don’t know will be informed … In moderation of course.
    – Raffa
    Commented Nov 15, 2023 at 18:05
  • 1
    @aff This is by no means a new trend. New users post things they think are "new posts" all the time and then we go through and close them on review. No new trend, just more observed.
    – Thomas Ward
    Commented Nov 21, 2023 at 23:22

6 Answers 6

13

You can use head -n <n> to print first n lines:

head -n 1000 "file.csv"

To write to a file use a redirection:

head -n 1000 "file.csv" > "some_other_file.csv"

To overwrite the same file, you need to write to a temporary file first and then overwrite the original file ...

head -n 1000 "file.csv" > "file.csv.tmp" && mv "file.csv.tmp" "file.csv"
# or more safe use mktemp
tmpfile=$(mktemp)
head -n 1000 "file.csv" > "$tmpfile" && mv "$tmpfile" "file.csv"

... or use sponge (part of moreutils --> sudo apt install moreutils)

head -n 1000 "file.csv" | sponge "file.csv"

Other (non-portable) options to edit the file in place using GNU versions of sed and awk:

sed -i 1000q "file.csv"
# or
sed -n -i '1,1000p' "file.csv"
# or
sed -i '1001,$d' "file.csv"
# or
awk -i inplace 'NR>1000{exit}1' "file.csv"
7
  • 1
    Note that all sed implementations have the -i option, it's just that some require an extension, and some require a space between the -i and the extension while others require no space. So the sed is almost portable. However, perl's -i operator is entirely portable (and is, I think, where sed got the idea anyway) so you can do perl -pi 'print if $. < 1001' file.
    – terdon
    Commented Nov 14, 2023 at 15:50
  • 1
    Also note that if you're going to use awk -i, you'd better be sure there's no file named inplace or inplace.awk in the current directory: unix.stackexchange.com/a/749646/70524
    – muru
    Commented Nov 14, 2023 at 15:53
  • @terdon You probably meant something with -n instead perl -i -ne 'print if $. < 1001' file
    – Raffa
    Commented Nov 14, 2023 at 16:17
  • Ugh, sorry! No, I meant something with -p but without print, like this: perl -pi 'exit if $. > 1000' file. Thanks, @Raffa.
    – terdon
    Commented Nov 14, 2023 at 17:03
  • @terdon I see … that is even more efficient … But an -e is most likely going to be needed on Ubuntu or else perl might look for a script file instead of a string script I think.
    – Raffa
    Commented Nov 14, 2023 at 17:11
6

In terminal run:

head -n 1000 /path/to/file.csv

To have the result in a separate file, run:

head -n 1000 /path/to/file.csv > /path/to/new_file_with_first_1000_linces.csv
0
3

The POSIX standard utility for editing files is the ed utility, and you can use it like this:

ed file.csv <<-HERE
  ,1000w
  q
HERE

ed file.csv starts the ed utility and opens the file file.csv for editing. ed is intended to be used interactively, so the way it works is that you type commands into ed and ed then executes those commands.

However, we want to use this in a script, not interactively, so we use a Here-Document, which allows us to re-direct stdin as-if we had typed in those commands ourselves.

Commands in ed are typically of the form address one-letter.

The first command in our ed script is ,1000w. The address here is an address range: ,1000 which is the same as 1,1000 meaning "all lines from 1 to 1000". The command is w, which is the write command. The write command takes an optional filename argument, but if you don't supply one, it takes the last used file, which is what we want.

The second command is just the quit command.

So, what this script is telling ed is to w write the first 1000 lines back to the file, then quit. The end result is exactly what you want: truncate the input file to 1000 lines.

But beware! 1000 lines does not mean 1000 rows! A CSV file can contain line breaks within cells, for example, this CSV has two lines but only one row:

"Cell #1","Cell
#2"

If your actual goal is to select the first 1000 rows, then you will need to parse the CSV file with a CSV parser. For example using a Python script like the following:

#!/usr/bin/env python3

import csv

with open("file.csv", newline="") as f:
    reader = csv.reader(f)
    rows = [row for row in reader][:1000]

with open("file.csv", "w") as f:
    writer = csv.writer(f)
    writer.writerows(rows)
2

To keep only the first 1000 lines:

sed '1000q' file.csv

To write to a new file:

sed '1000q' file.csv > new_file.csv
2

Since it’s CSV, use miller:

miller —-csv head -n 1000 file.csv
1

Another approach using awk:

awk 'FNR>1000{exit} 1' input.csv > output.csv

This will print the input lines unchanged (the seemingly stray 1), but once the internal line counter exceeds 1000, it will exit processing.

Assuming you have an awk implementation that understands the -i inplace extension, you can omit the redirection to a temporary output file and have awk modify the input file directly:

awk -i inplace 'FNR>1000{exit} 1' input.csv
2

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .