This post contains a script that will help count rows within multiple CSV files using PowerShell. If you have a directory containing CSV files, this demo will walk you through how to count the rows of all CSV files within a folder.

Counting rows within CSV files may be useful for verifying data from source to destination. For example, if you’re importing CSV files to a SQL Server database, you may want to verify all rows were imported by counting the number of rows in each CSV file and comparing it with the table row counts in SQL.

# Count Rows in Multiple CSV Files PowerShell
# Check for Other Data Issues in CSV Files (Additional-Optional)

Count Rows within CSV Files

First, open PowerShell ISE so we have a place to paste the PS script.

Copy and paste the PowerShell script below into the PowerShell ISE window.

Amend the directory (c:\myfolder) to the folder containing your CSV files, then Save/Run the PowerShell Script.

# Count rows in CSV files PowerShell
#  > set directory to folder containing csv files

Get-ChildItem "C:\myfolder\" -re -in "*.csv" |
Foreach-Object { 
    $fileStats = Get-Content $_.FullName | Measure-Object -line
    $linesInFile = $fileStats.Lines -1
    Write-Host "$_,$linesInFile" 
}

We can copy this output information into Excel, but if doing so run Text to Columns and choose delimiter to format it correctly.

Verify Data / Check for Issues

We can see in the screenshot above, one of the files has a row count of -1. This indicates that the file has no data, so we can throw ignore the CSV File. That’s one data issue out of the way.

Next up, I’m going to check for an issue whereby the CSV files contain “(12345 rows affected)” at the end of it. This is a common issue you may find when Exporting SQL Server Query Results to CSV.

This is our example data issue. You wouldn’t want to attempt to import this CSV to SQL Server, as that gap in the row will create some bad data in the table.

We can run the PowerShell below to identify files with the string/phrase “rows affected“, checking all CSV files within a folder.

# find string in csv files powershell
Get-ChildItem "c:\myfolder\" -Recurse | Select-String -Pattern "rows affected" | Group Path | Select Name

The script shows that one file has this “rows affected” string. We can manually remove this from the CSV file. To stop the “rows affected” MS SQL Server issue, we should export the data from SQL ensuring SET NOCOUNT ON has been added to the SQL Results to File query.