Table of Contents
Using Get-Content
Cmdlet
The Get-Content
cmdlet can be used in the following ways to get the number of lines in the specified .csv
file:
Get-Content
cmdlet with.Length
propertyGet-Content
cmdlet with.Count
propertyGet-Content
cmdlet with.ReadCount
propertyGet-Content
cmdlet withMeasure-Object
cmdlet and its-Line
parameter
Before moving towards the ways mentioned above, let’s look at the content of our countries.csv
; you can use your .csv
file. Although content is optional here, it helps to know the number of lines we have.
Content of countries.csv
File:
Get-Content
with the .Length
property to retrieve the count of lines in a CSV file in PowerShell.
1 2 3 |
(Get-Content .\countries.csv).Length |
1 2 3 |
6 |
The Get-Content was used to retrieve the content of the specified file, which is countries.csv
in our case. We used the .
notation because countries.csv
was located in the current directory, but we could pass the path as E:\Test\countries.csv if the .csv
file was in a different directory.
With this method, we read the content of countries.csv
(including header) into an array using Get-Content
and then accessed the .Length
property of that array to get a count of lines in the countries.csv
file. This method is preferred when we want to get the number of lines of a complete .csv
file; however, it may consume a lot of memory and be slow for larger files because it will load the entire CSV file into memory.
Use Get-Content
with the .Count
property to get the number of lines in a specified CSV file in PowerShell.
1 2 3 |
(Get-Content .\countries.csv).Count |
1 2 3 |
6 |
This code is similar to the previous example and counted the header of the countries.csv
file, but we used the .Count
property to get the total number of lines in countries.csv
. Similar to the previous method, it will also load the entire file into memory; it might be slow for large files and consume a lot of memory. Remember, the .Count
and .Length
are the same properties and give the same result.
Use Get-Content
with the Measure-Object
cmdlet and its -Line
parameter to get the number of lines in a CSV file.
1 2 3 |
(Get-Content .\countries.csv) | Measure-Object -Line |
1 2 3 4 5 |
Lines ----- 6 |
This is another way to get the number of lines in a CSV file in PowerShell. In this method, we used a pipeline (|
) to forward the content read by the Get-Content
cmdlet to the Measure-Object
. Here, we used the -Line
parameter with the Measure-Object
, which tells us to count the number of lines in the received input.
Now, what is Measure-Object
? This cmdlet calculates property values of an object’s specific type, it performs based on the given parameters in the command. For instance, we used the -Line
parameter to get the number of lines but -Word
, -Character
, and -Property
is used to retrieve words, characters, and property name, respectively.
We can use Measure-Object
to count objects or objects having a specific property that we will specify in the command. For example, if you are working with numeric values, we can also use this cmdlet to calculate Sum, Minimum/Maximum, Standard Deviation, and average.
NOTE: The
Get-Content
cmdlet with.Length
&.Count
properties andMeasure-Object -Line
will not work correctly if the CSV file has blank lines at the end.
Use Get-Content
with the .ReadCount
property to get the number of lines in a given CSV file.
1 2 3 4 |
$count = (Get-Content .\countries.csv).ReadCount Write-Host "The number of lines that are read: " $count |
1 2 3 |
The number of lines that are read: 1 2 3 4 5 6 |
This approach also included the CSV file’s header while counting the number of lines. Using .ReadCount
is useful when we want to know the line number that was read; here, the last number will show the total number of lines in the countries.csv
file.
The above script used the Get-Content
cmdlet to read the countries.csv
‘s contents into an object and then accessed the .ReadCount
property of the object to get the number of lines.
The .ReadCount
property returned the number of lines read from the file. This method is faster than the previous methods when dealing with large files because it does not load the entire file into memory.
Additionally, this method does not have the issue of counting blank lines at the end of the countries.csv
(if any) because it only returns the number of lines that have been read from the file.
Using Import-Csv
Cmdlet
The Import-Csv
cmdlet can be used in the following ways to get the number of lines in the specified .csv
file:
Import-Csv
cmdlet withMeasure-Object
cmdletImport-Csv
cmdlet withMeasure Object
andSelect-Object
cmdletImport-Csv
cmdlet with.Count
property
Using Import-Csv
is helpful because it counts the lines excluding the header of a CSV file. Let’s learn them one by one below.
Use the Import-Csv
cmdlet with the Measure-Object
cmdlet to get the number of lines in the CSV file in PowerShell.
1 2 3 |
Import-Csv .\countries.csv | Measure-Object |
1 2 3 |
Count: 5 |
Here, we used Import-Csv
, which converts the countries.csv
file into an array of objects, and pipes it with Measure-Object
(we have learned it in the previous section), which will display the number of lines excluding the header. We can use the following solution if we only want to get a count.
Use the Import-Csv
cmdlet with Measure Object
and Select-Object
cmdlets to get the number of lines in the CSV file in PowerShell.
1 2 3 |
Import-Csv .\countries.csv | Measure-Object | Select-Object -Expand Count |
1 2 3 |
5 |
It is similar to the previous command, producing the same results. Still, we used the Select-Object
cmdlet with the - Expand
parameter, allowing us to expand the object’s property returned by the Measure-Object
and return the Count
property’s value as shown in the above output.
Use the Import-Csv
cmdlet with the .Count
property to get the number of lines in the CSV file in PowerShell.
1 2 3 |
(Import-Csv .\countries.csv).Count |
1 2 3 |
5 |
The above command is doing the same, showing the number of lines in the specified CSV file, but we used the .Count
property to get that count.
NOTE: All of the above approaches using the Import-Csv
cmdlet will load the entire file into memory, so it might be slow for large files and consume a lot of memory. Also, it will not work correctly if the CSV file has blank lines at the end.
Until this point, we learned various ways to get a count of records in a CSV file, but what if the project requirements change a bit and demand to count the number of lines of multiple CSV files from the current directory and sub-directories? Let’s see the following section for that.
Further reading:
Using Get-ChildItem
with Foreach-Object
Use Get-ChildItem
with Foreach-Object
to get the number of lines in multiple CSV files from the current directory and sub-directories in PowerShell.
1 2 3 4 5 6 7 8 |
Get-ChildItem "." -re -in "*.csv" | Foreach-Object { $fileStats = Get-Content $_.FullName | Measure-Object -line $linesInFile = $fileStats.Lines -1 Write-Host "$_,$linesInFile" } |
1 2 3 |
E:\Test\countries.csv,5 |
The above script used the Get-ChildItem
cmdlet to get all the CSV files in the current directory and its subdirectories, specified by the -Recurse
parameter (alias is -re
) and the -Include
parameter (alias is -in
) was used to include only files with .csv
extension. The Foreach-Object
cmdlet was then used to iterate over each file in the collection.
For each file, the Get-Content
cmdlet was used to read the file’s contents and pipe them to the Measure-Object
cmdlet with the -Line
parameter. The -Line
parameter tells Measure-Object
to count the number of lines in the input.
The output of Measure-Object
is stored in the variable $fileStats
, and the number of lines is stored in the variable $linesInFile
after subtracting 1
to exclude the header line.
Finally, the Write-Host
cmdlet was used to write the current file name and the number of lines to the host. It is useful when you want to know the number of lines in all the CSV files in a directory and its subdirectories. For example, we can use the following command only to get a count.
1 2 3 4 5 6 |
Get-ChildItem . -Filter “*.csv” -Name | ForEach-Object{ (Get-Content $_).Length - 1 } |
1 2 3 |
5 |
This command is similar to the previous one except for three differences; we used the -Filter
parameter to include only those files ending with the .csv
extension, the -Name
parameter to return only the names of the files and not the full path, while .Length
was used to get the number of lines.
That’s all about how to get number of lines in CSV file in PowerShell.