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

command-line powershell csv count powershell-1.0
jrara picture jrara · Jul 28, 2011 · Viewed 102.7k times · Source

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.

Answer

Ten98 picture Ten98 · Dec 21, 2012

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.

Related questions

How can I pass an argument to a PowerShell script?

There's a PowerShell script named itunesForward.ps1 that makes iTunes fast forward 30 seconds: $iTunes = New-Object -ComObject iTunes.Application if ($iTunes.playerstate -eq 1) { $iTunes.PlayerPosition = $iTunes.PlayerPosition + 30 } It is executed with a prompt line command: powershell.exe itunesForward.ps1 Is it …

Read More
Running CMD command in PowerShell

I am having a bunch of issues with getting a PowerShell command to run. All it is doing is running a command that would be run in a CMD prompt window. Here is the command: "C:\Program Files (x86)\Microsoft …

Read More
How to pass command-line arguments to a PowerShell ps1 file

For years, I have used the cmd/DOS/Windows shell and passed command-line arguments to batch files. For example, I have a file, zuzu.bat and in it, I access %1, %2, etc. Now, I want to do the same when I …

Read More