PowerShell – Get Number of Lines in CSV File

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 property
• Get-Content cmdlet with .Count property
• Get-Content cmdlet with .ReadCount property
• Get-Content cmdlet with Measure-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:

Use Get-Content with the .Length property to retrieve the count of lines in a CSV file in PowerShell.

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.

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.

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 and Measure-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.

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 with Measure-Object cmdlet
• Import-Csv cmdlet with Measure Object and Select-Object cmdlet
• Import-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.

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.

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.

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:

PowerShell- Read File Line by Line

Read more →

Read CSV file in PowerShell

Read more →

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.

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.

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.