This post is a quick run-through of using a PowerShell script to count rows within many CSV files, with a quick check of the data afterwards. You might find this useful for verifying data using row counts, if importing multiple CSV files into a database.
Count Rows within CSV Files
First, open PowerShell ISE on your machine.
Have a quick look at your directory.
Now, I’ll count the rows for each file using this script.
This can be run against network paths by entering ‘\\server-name\share\’ instead of ‘C:\myfolder\’ as I’m doing.
We can copy that information out for later, paste into Excel and run a quick Text to Columns choosing comma delimiter.
! – I need a script that’ll export this straight into the CSV file; initial attempt failed.
Checking for Data Issues
We can see above that one of the files above has a count of -1. This indicates that the file has no data, so we can throw it in the bin.
Next up, I’m going to check for a common issue I’ve seen happen – exported CSV files containing “(12345 rows affected)” at the end of it.
Now let’s have a look at that file…
Looks like someone (me) has exported data from SQL Server without adding SET NOCOUNT ON. We’ll need to remove that as highlighted, and update the row counts for that file to 34.
This is an area I hope to expand on in future, but for now, that’s it.