Acquire / filesystem · All operations

Count the rows in files

A variant of List files and folders

Return the number of rows in one or more text files without loading them as a table.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Count the rows of text files without loading them as a table

SQL
COPY (SELECT ('a' || chr(10) || 'b' || chr(10) || chr(10) || 'd' || chr(10))::BLOB) TO '<your_scratch_dir>/rows_a.txt' (FORMAT blob);
COPY (SELECT ('x' || chr(10) || 'y')::BLOB) TO '<your_scratch_dir>/rows_b.txt' (FORMAT blob);
COPY (SELECT ''::BLOB) TO '<your_scratch_dir>/rows_c.txt' (FORMAT blob);
-- a line is a newline, plus one for a last line that has none
CREATE TEMP TABLE result AS
SELECT replace(filename, '<your_scratch_dir>/', '') AS file,
       length(content) - length(replace(content, chr(10), '')) + CASE WHEN length(content) > 0 AND NOT content LIKE '%' || chr(10) THEN 1 ELSE 0 END AS line_count
FROM read_text('<your_scratch_dir>/rows_*.txt');
SELECT * FROM result ORDER BY file;
SELECT CASE WHEN ((SELECT list(line_count ORDER BY file) FROM result) = [4, 2, 0] AND (SELECT sum(line_count) FROM result) = 6) THEN 'as expected' ELSE error('the three files did not count as 4, 2 and 0 lines, 6 in all') END AS verdict;

Replace <your_scratch_dir> with a directory of your own that you can write to before running the statement.

  • The statement writes rows_a.txt, rows_b.txt and rows_c.txt itself, in <your_scratch_dir>.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the three files did not count as 4, 2 and 0 lines, 6 in all”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Apache Hop (1)
Pentaho Data Integration (1)
Talend (1)
  • tFileRowCountread 2026-09-27

    Count the rows of a file by reading it row by row

Background

Apache Hop's Get files Row Count transform counts the number of rows in a file or set of files.[1] The Unix wc command, short for word count, reads standard input or a list of files and can report each file's newline count.[2] When wc is given a list of files, it reports statistics for each file and a total.[2]

When to use it

In Apache Hop's Get files Row Count transform, the count returns the number of separators in the file unless Perform smart count is selected.[1] Apache Hop's Perform smart count option makes an extra pass to try to return the actual number of lines, for files whose row separator is the same as the column value delimiter or that contain blank lines.[1] If a newline is treated as a separator rather than a terminator, there is no newline after the last line of a file.[3] The Portable Operating System Interface (POSIX) standard considers newlines to be line terminators, and where newlines are terminators, a final line not ending in a newline may be considered an incomplete text line.[3][4] Check whether a file's last line ends in a newline before treating a newline count as its row count. (Meridian's view)

Request for Comments (RFC) 4180 documents the comma-separated values (CSV) format that seems to be followed by most implementations.[5] In RFC 4180, the last record in a CSV file may or may not have an ending line break.[5] In RFC 4180, an optional header line may appear as the first line of a CSV file, with the same format as the record lines.[5] In RFC 4180, fields containing line breaks should be enclosed in double quotes, and RFC 4180's own example shows one record spread over two lines.[5]

The method

In the output of wc, the first column is the count of newlines in each file.[2] The command wc -l prints a file's line count, and wc -c prints its byte count.[2] The wc command appeared in Version 1 Unix and has been part of the X/Open Portability Guide since issue 2 of 1987.[2] Apache Hop's Get files Row Count lets the row separator be a carriage return, a line feed, a tab or a custom character.[1]

Sources

  1. [1]Get files Row Countdocumentation
  2. [2]wc (Unix) - Wikipediasecondary source
  3. [3]Newline - Wikipediasecondary source
  4. [4]POSIX - Wikipediasecondary source
  5. [5]Common Format and MIME Type for Comma-Separated Values (CSV) Filesstandard