PowerShell: how to count number of rows in csv file?

Question

How can I count the number of rows in a csv file using powershell? I tried something like

Get-Content -length "C:\Directory\file.csv"

or

(Get-Content).length "C:\Directory\file.csv"

but these result an error.

score 57 · Answer 1 · edited Jul 09 '14 at 21:38

57

Get-Content and Measure-Object are fine for small files, but both are super inefficient with memory. I had real problems with large files.

When counting rows in a 1GB file using either method, Powershell gobbled up all available memory on the server (8GB), then started paging to disk. I left it over an hour, but it was still paging to disk so I killed it.

The best method I found for large files is to use IO.StreamReader to load the file from disk and count each row using a variable. This keeps memory usage down to a very reasonable 25MB and is much, much quicker, taking around 30 seconds to count rows in a 1GB file or a couple of minutes for a 6GB file. It never eats up unreasonable amounts of RAM, no matter how large your file is:

[int]$LinesInFile = 0
$reader = New-Object IO.StreamReader 'c:\filename.csv'
 while($reader.ReadLine() -ne $null){ $LinesInFile++ }

The above snippet can be inserted wherever you would use get-content or measure-object, simply refer to the $LinesInFile variable to get the row count of the file.

edited Jul 09 '14 at 21:38

Community

1
1

answered Dec 21 '12 at 14:48

Ten98

732
1
6
8

Faster than any other solutions shown here. Less than 5 seconds to sort out a 500mb csv file. – alextc Jul 15 '14 at 23:47
6

You also should dispose reader ($reader.Dispose()) after use or you risk holding file opened until you close your PS session. – Oleh Nechytailo Aug 11 '16 at 11:06
1

If using powershell add "write-output $LinesInFile" at end of block above to get the value on screen. – Matt M May 14 '18 at 15:36
just add `$LinesInFile` at the end and you will see the number. – Nicholas Humphrey Apr 25 '19 at 16:40
Great if i need to get count for lists of file in a folder.. how do i do that? – Linga Oct 06 '20 at 15:24
best solution! 10 gb file! – Ajay Tom George Jan 20 '21 at 16:14
Note that it's better practice to specify `while ($null -ne $reader.ReadLine()) { ... }` due to how comparison operators can sometimes work like filters. https://rencore.com/blog/powershell-null-comparison/ – Bacon Bits Feb 16 '21 at 20:35
@Linga Search for powershell scripts that process lists of files in a loop, insert my snippet inside the loop and specify the file name as a variable inside your loop. – Geoff Griswald Feb 17 '21 at 21:20

score 40 · Accepted Answer · edited Feb 26 '14 at 06:31

40

Pipe it to the Measure-Object cmdlet

Import-Csv C:\Directory\file.csv | Measure-Object

edited Feb 26 '14 at 06:31

ДМИТРИЙ МАЛИКОВ

20,616
10
76
127

answered Jul 28 '11 at 08:11

Shay Levy

114,369
30
175
198

3

Thanks, this seems to work, but it is terribly slow compared e.g. to GNU Unix utils wc.exe. – jrara Jul 28 '11 at 08:42
3

That is because wc.exe would be the equivalent of (Get-Content).Length which while it is much faster than Import-CSV, it is also a potentially incorrect solution as pointed out by stej since it would not account for rows with multiline fields. – EBGreen Jul 28 '11 at 13:48
3

(took ~1 minute for a 100 MB file) – Franck Dernoncourt Aug 01 '14 at 20:00

score 10 · Answer 3 · edited Feb 27 '12 at 05:03

10

Generally (csv or not)

@(Get-Content c:\file.csv).Length

~~If the file has only one line, then, it will fail.~~ (You need the @ prefix...otherwise if the file has one line, it will only count the number of characters in that line.

Get-Content c:\file.csv | Measure-Object -line

But both will fail if any record takes more than one row. Then better import csv and measure:

Import-Csv c:\file.csv | Measure-Object | Select-Object -expand count

edited Feb 27 '12 at 05:03

Dexter Legaspi

2,882
1
30
26

answered Jul 28 '11 at 08:13

stej

27,607
11
68
102

Last one seems to generate an error: Select-Object : Cannot expand property "count" because it has nothing to expand. At line:1 char:64 + Import-Csv C:\Directory\file.csv | Measure-Object | Select-Object <<<< -expand count – jrara Jul 28 '11 at 08:41
Weird, `Measure-Object` should return an object that has a property Count. Try to remove the `|Select-Object ...` and you will see what it returns. – stej Jul 28 '11 at 08:44
You're probably using PowerShell v1. In v1, 'Select-Object -expand propertyName' throws an error when the result is a scalar (one object). Upgrade to v2 and you're good to go. – Shay Levy Jul 28 '11 at 08:44
Thanks, Yes, I'm using version 1.0, it's one of the tags of this question. – jrara Jul 28 '11 at 09:27
2

I check only PowerShell tag. And silently assumed that nobody uses v1.0. Sorry :) – stej Jul 28 '11 at 14:43
Ok, no problem, thank you for your contribution. Btw, I'm using still Win XP, AFAICS, PowerShell 2.0 is not available for Win XP? – jrara Jul 29 '11 at 05:41

score 2 · Answer 4 · edited Mar 17 '19 at 17:55

2

You can simply use unix like comand in powershell.

If you file test.csv Then command to get rowcount is

gc test.csv | Measure-Object

edited Mar 17 '19 at 17:55

slfan

8,665
115
63
77

answered Mar 17 '19 at 17:33

Sandeep Bharani

29
1

score 0 · Answer 5 · answered Nov 05 '13 at 00:22

0

(Import-Csv C:\Directory\file.csv).count is the only accurate one out of these.

I tried all of the other suggestions on a csv with 4781 rows, and all but this one returned 4803.

answered Nov 05 '13 at 00:22

devonuto

155
1
3
11

score -1 · Answer 6 · edited Jun 13 '13 at 10:11

-1

You can try

(Import-Csv C:\Directory\file.csv).count

or

$a=Import-Csv C:\Directory\file.csv
$a.count

edited Jun 13 '13 at 10:11

Nalaka526

10,740
21
78
115

answered Jun 13 '13 at 09:50

julien

1

I get an `OutOfMemoryException` using this approach on a large file. Using `Get-Content | Measure-Object` works with a limited amount of memory.. – oɔɯǝɹ Oct 13 '15 at 08:49

PowerShell: how to count number of rows in csv file?

6 Answers6

Linked