SQL DBA Blog

Tag: PowerShell ForEach

  • PowerShell ForEach Loop Tutorial

    PowerShell ForEach Loop Tutorial

    Looping is a fundamental concept in PowerShell, and in programming in general. It’s needed for various situations where we need to work with one object at a time within an array/collection of objects.

    Microsoft’s documentation on this, about Foreach : Microsoft Documentation, describes Foreach to be for ‘stepping through (iterating) a series of values in a collection of items‘ – always appreciate a good technical description.

    This post contains some basic examples of the ForEach Loop in PowerShell:
    # Basic ForEach Statement
    # ForEach with Get-ChildItem

    Basic ForEach Statement

    We can hardcode anything into this array below, or populate it from somewhere else.

    This is one of the most basic examples as shown in MS Docs, we’re iterating through each letter in the letterArray, writing to the terminal the value foreach loop.

    $letterArray = "a","b","c","d"
foreach ($letter in $letterArray)
{
  Write-Host $letter
}
    PowerShell ForEach

    ForEach with Get-ChildItem

    The PowerShell script below performs a write-to Console for each file in the demoFolder Directory.

    I’m running this twice, the second time navigating out of the demoFolder.

    foreach ($file in Get-ChildItem)
{
    Write-Host $file
    Write-Host $file.length
    Write-Host $file.lastaccesstime
}
    PowerShell ForEach Get-ChildItem

    The above is also showing us the LastAccessTime Property for each file.

  • Count Rows within CSV Files using PowerShell

    Count Rows within CSV Files using PowerShell

    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.

    Open PowerShell ISE Windows

    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" 
}
    Count Rows within CSV Files using PowerShell

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

    CSV File Counts

    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.

    Notepad++ Adventureworks Table Rows

    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
    Find String in CSV Files PowerShell

    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.