Automating Data Imports and Exports with PowerShell

In today's fast-paced business environment, the ability to efficiently manage data flows between systems isn't just a convenience—it's a necessity. Organizations handle massive volumes of information daily, moving data between databases, applications, cloud services, and legacy systems. Manual data transfer processes are not only time-consuming but also prone to human error, potentially leading to costly mistakes, compliance issues, and operational bottlenecks. The stakes are high: a single data import error could cascade through an organization, affecting everything from customer relationships to financial reporting.

PowerShell emerges as a powerful solution for automating these critical data movement tasks. As Microsoft's task automation framework, PowerShell combines command-line speed with scripting flexibility, offering IT professionals and developers a robust toolkit for handling data imports and exports across diverse platforms. Whether you're transferring CSV files, synchronizing databases, or orchestrating complex ETL (Extract, Transform, Load) processes, PowerShell provides the commands, modules, and extensibility needed to build reliable, repeatable automation workflows.

Throughout this comprehensive exploration, you'll discover practical techniques for automating data imports and exports using PowerShell. We'll examine fundamental concepts, walk through real-world scenarios, explore best practices for error handling and security, and provide actionable code examples you can adapt to your specific needs. By the end, you'll have a thorough understanding of how to leverage PowerShell's capabilities to streamline your data management processes, reduce manual intervention, and build more resilient data pipelines.

Understanding PowerShell's Data Handling Capabilities

PowerShell's architecture makes it exceptionally well-suited for data manipulation tasks. Unlike traditional command-line interfaces that work primarily with text streams, PowerShell operates on objects—structured data with properties and methods. This object-oriented approach means that when you import data from a CSV file, for example, PowerShell doesn't just read text; it creates objects representing each row, with properties corresponding to column headers. This fundamental difference transforms how you interact with data, enabling powerful filtering, transformation, and export operations with minimal code.

The cmdlets (PowerShell's term for commands) designed for data handling are both intuitive and powerful. Commands like Import-Csv, Export-Csv, ConvertFrom-Json, and ConvertTo-Json handle common data formats with ease. For database operations, PowerShell can leverage .NET Framework classes or specialized modules to connect to SQL Server, MySQL, Oracle, and other database systems. The pipeline feature allows you to chain commands together, passing objects from one cmdlet to another, creating elegant data transformation workflows that would require significantly more code in traditional programming languages.

Core Data Import Cmdlets

PowerShell offers several built-in cmdlets specifically designed for importing data from various sources. The most commonly used include:

• Import-Csv: Reads comma-separated value files and converts them into PowerShell objects, automatically using the first row as property names
• Import-Clixml: Imports objects from XML files created by Export-Clixml, preserving complex object structures and type information
• ConvertFrom-Json: Parses JSON-formatted strings and converts them into PowerShell objects, essential for working with web APIs and modern data formats
• Get-Content: Reads raw text from files, useful when you need line-by-line processing or custom parsing logic
• Import-Excel: Available through third-party modules like ImportExcel, enables direct reading from Excel workbooks without requiring Excel installation

Core Data Export Cmdlets

Complementing the import capabilities, PowerShell provides equally robust export functionality:

• Export-Csv: Converts PowerShell objects to CSV format and saves them to files, with options for delimiter customization and encoding
• Export-Clixml: Serializes objects to XML format, maintaining type fidelity and complex object hierarchies
• ConvertTo-Json: Transforms PowerShell objects into JSON format, perfect for API integrations and modern data exchange
• Out-File: Writes output to text files with various encoding options, suitable for logs and custom formatted data
• Export-Excel: Part of third-party modules, creates formatted Excel workbooks with styling, formulas, and multiple worksheets

"The transition from manual data transfers to automated PowerShell scripts reduced our processing time from hours to minutes while virtually eliminating data entry errors."

Working with CSV Files: The Foundation of Data Exchange

CSV (Comma-Separated Values) files remain one of the most universal data exchange formats, supported by virtually every database, spreadsheet application, and business system. PowerShell's CSV handling capabilities make it remarkably straightforward to automate imports and exports involving this format. The Import-Csv cmdlet reads CSV files and automatically creates custom objects where each row becomes an object and each column becomes a property, enabling immediate manipulation using PowerShell's rich filtering and transformation features.

Consider a common scenario: you receive daily sales reports as CSV files that need to be filtered, transformed, and loaded into a database. Without automation, this involves opening files, manually filtering data, reformatting columns, and copying information—a process prone to inconsistency and error. With PowerShell, this entire workflow can be scripted, scheduled, and executed reliably without human intervention.

Basic CSV Import Operations

# Import a CSV file
$data = Import-Csv -Path "C:\Data\SalesReport.csv"

# Display the first few records
$data | Select-Object -First 5

# Filter data based on criteria
$highValueSales = $data | Where-Object { [decimal]$_.Amount -gt 1000 }

# Calculate statistics
$totalSales = ($data | Measure-Object -Property Amount -Sum).Sum
$averageSale = ($data | Measure-Object -Property Amount -Average).Average

# Group and summarize data
$salesByRegion = $data | Group-Object -Property Region | 
    Select-Object Name, Count, @{Name="Total";Expression={
        ($_.Group | Measure-Object -Property Amount -Sum).Sum
    }}

This example demonstrates how PowerShell treats imported CSV data as objects, enabling immediate use of filtering (Where-Object), calculation (Measure-Object), and grouping (Group-Object) operations. The syntax remains consistent and readable, reducing the learning curve for team members who need to maintain these scripts.

Advanced CSV Export Techniques

# Export filtered data to a new CSV
$highValueSales | Export-Csv -Path "C:\Data\HighValueSales.csv" -NoTypeInformation

# Export with custom delimiter
$data | Export-Csv -Path "C:\Data\SalesReport.txt" -Delimiter "|" -NoTypeInformation

# Export with specific encoding for international characters
$data | Export-Csv -Path "C:\Data\SalesReport_UTF8.csv" -Encoding UTF8 -NoTypeInformation

# Create a custom object and export
$summaryData = [PSCustomObject]@{
    ReportDate = Get-Date -Format "yyyy-MM-dd"
    TotalRecords = $data.Count
    TotalSales = $totalSales
    AverageSale = $averageSale
    HighValueCount = $highValueSales.Count
}

$summaryData | Export-Csv -Path "C:\Data\DailySummary.csv" -NoTypeInformation -Append

The -NoTypeInformation parameter prevents PowerShell from adding type metadata to the first line of the CSV, ensuring compatibility with systems that expect standard CSV format. The -Append parameter allows adding records to existing files, useful for building cumulative logs or reports over time.

Database Integration: Direct Data Movement

While file-based data exchange serves many purposes, direct database integration often provides superior performance, transaction control, and data integrity. PowerShell can connect to virtually any database system through various methods: the .NET Framework's ADO.NET classes, specialized PowerShell modules like SqlServer, or ODBC connections. These approaches enable you to execute queries, retrieve result sets, and perform bulk inserts—all within your automation scripts.

Database automation with PowerShell eliminates intermediate file creation, reduces storage requirements, and provides immediate validation of data operations. Instead of exporting from one database to CSV, then importing that CSV into another database, you can establish a direct connection between systems, transforming data in memory as it moves. This approach significantly reduces processing time and eliminates the risk of file-based errors.

SQL Server Data Import

# Install the SqlServer module if not already present
# Install-Module -Name SqlServer -Force -AllowClobber

# Import the module
Import-Module SqlServer

# Define connection parameters
$serverInstance = "SQLServer01\Production"
$database = "SalesDB"
$tableName = "DailySales"

# Import CSV data
$csvData = Import-Csv -Path "C:\Data\SalesReport.csv"

# Create SQL connection
$connectionString = "Server=$serverInstance;Database=$database;Integrated Security=True;"

# Process each record
foreach ($record in $csvData) {
    $query = @"
INSERT INTO $tableName (OrderDate, CustomerID, ProductID, Quantity, Amount, Region)
VALUES ('$($record.OrderDate)', '$($record.CustomerID)', '$($record.ProductID)', 
        $($record.Quantity), $($record.Amount), '$($record.Region)')
"@
    
    Invoke-Sqlcmd -ServerInstance $serverInstance -Database $database -Query $query
}

# Alternative: Bulk insert using .NET classes for better performance
$bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($connectionString)
$bulkCopy.DestinationTableName = $tableName

# Create DataTable from CSV
$dataTable = New-Object System.Data.DataTable
$csvData | ForEach-Object {
    if ($dataTable.Columns.Count -eq 0) {
        $_.PSObject.Properties | ForEach-Object {
            [void]$dataTable.Columns.Add($_.Name)
        }
    }
    $row = $dataTable.NewRow()
    $_.PSObject.Properties | ForEach-Object {
        $row[$_.Name] = $_.Value
    }
    $dataTable.Rows.Add($row)
}

$bulkCopy.WriteToServer($dataTable)
$bulkCopy.Close()

The bulk insert approach using SqlBulkCopy offers dramatically better performance when dealing with large datasets. While individual INSERT statements work well for small batches, bulk operations can insert thousands of records per second, making them essential for enterprise-scale data movement.

Exporting Database Data to Files

# Query database and export to CSV
$query = @"
SELECT 
    OrderDate,
    CustomerID,
    ProductID,
    Quantity,
    Amount,
    Region
FROM DailySales
WHERE OrderDate >= DATEADD(day, -7, GETDATE())
ORDER BY OrderDate DESC
"@

$results = Invoke-Sqlcmd -ServerInstance $serverInstance -Database $database -Query $query

# Export to CSV
$results | Export-Csv -Path "C:\Reports\WeeklySales.csv" -NoTypeInformation

# Export to JSON for API consumption
$results | ConvertTo-Json -Depth 3 | Out-File "C:\Reports\WeeklySales.json"

# Export to Excel with formatting (requires ImportExcel module)
$results | Export-Excel -Path "C:\Reports\WeeklySales.xlsx" `
    -AutoSize `
    -TableName "SalesData" `
    -TableStyle Medium6 `
    -BoldTopRow

"Automating database exports with PowerShell transformed our reporting workflow, enabling stakeholders to access current data without waiting for manual report generation."

Working with JSON and XML: Modern Data Formats

As organizations increasingly adopt web services, REST APIs, and cloud platforms, JSON (JavaScript Object Notation) has become the dominant data exchange format. XML (eXtensible Markup Language), while somewhat older, remains prevalent in enterprise systems, configuration files, and legacy integrations. PowerShell provides native support for both formats, enabling seamless conversion between PowerShell objects and these structured data representations.

JSON's lightweight syntax and direct mapping to programming language data structures make it ideal for API interactions. PowerShell's ConvertFrom-Json and ConvertTo-Json cmdlets handle the serialization and deserialization processes, allowing you to consume web service responses and construct API requests with minimal code. Similarly, XML support through Select-Xml, Export-Clixml, and direct XML parsing enables interaction with SOAP services, configuration files, and document-based data stores.

JSON Data Operations

# Import JSON data from a file
$jsonContent = Get-Content -Path "C:\Data\customers.json" -Raw
$customers = $jsonContent | ConvertFrom-Json

# Access properties
foreach ($customer in $customers) {
    Write-Host "Customer: $($customer.Name), Email: $($customer.Email)"
}

# Filter and transform JSON data
$activeCustomers = $customers | Where-Object { $_.Status -eq "Active" }

# Add calculated properties
$enrichedCustomers = $customers | Select-Object *, 
    @{Name="FullAddress";Expression={"$($_.Street), $($_.City), $($_.State) $($_.Zip)"}},
    @{Name="AccountAge";Expression={(Get-Date) - [DateTime]$_.CreatedDate}}

# Export back to JSON
$enrichedCustomers | ConvertTo-Json -Depth 5 | Out-File "C:\Data\customers_enriched.json"

# Fetch data from REST API
$apiUrl = "https://api.example.com/v1/sales"
$headers = @{
    "Authorization" = "Bearer YOUR_API_TOKEN"
    "Content-Type" = "application/json"
}

$response = Invoke-RestMethod -Uri $apiUrl -Headers $headers -Method Get
$response.data | Export-Csv -Path "C:\Data\api_sales.csv" -NoTypeInformation

The -Depth parameter in ConvertTo-Json controls how many levels of nested objects are serialized. The default depth is 2, which may truncate complex object hierarchies. Increasing this value ensures complete serialization but may impact performance with deeply nested structures.

XML Data Handling

# Load XML file
[xml]$xmlContent = Get-Content -Path "C:\Data\config.xml"

# Access XML elements
$servers = $xmlContent.Configuration.Servers.Server
foreach ($server in $servers) {
    Write-Host "Server: $($server.Name), IP: $($server.IPAddress)"
}

# Modify XML content
$newServer = $xmlContent.CreateElement("Server")
$newServer.SetAttribute("Name", "Server04")
$newServer.SetAttribute("IPAddress", "192.168.1.14")
$xmlContent.Configuration.Servers.AppendChild($newServer)

# Save modified XML
$xmlContent.Save("C:\Data\config_updated.xml")

# Convert custom objects to XML
$data = @(
    [PSCustomObject]@{Name="John"; Age=30; Department="Sales"}
    [PSCustomObject]@{Name="Jane"; Age=28; Department="Marketing"}
)

$data | Export-Clixml -Path "C:\Data\employees.xml"

# Import XML back to objects
$importedData = Import-Clixml -Path "C:\Data\employees.xml"

PowerShell's XML handling leverages the .NET Framework's XML classes, providing full XPath query support and DOM manipulation capabilities. This makes it possible to work with complex XML schemas, namespaces, and transformations directly within your automation scripts.

Excel Automation Without Excel Installation

Excel spreadsheets remain ubiquitous in business environments, often serving as the preferred format for reports, data analysis, and information sharing. Traditionally, automating Excel file creation required installing Microsoft Excel on the automation server and using COM automation—an approach that introduced licensing costs, version dependencies, and potential stability issues. The ImportExcel PowerShell module revolutionized this landscape by enabling direct Excel file creation and manipulation without requiring Excel installation.

This module, available from the PowerShell Gallery, provides cmdlets for reading, writing, and formatting Excel workbooks. You can create multi-sheet workbooks, apply formatting, add charts, and implement formulas—all through PowerShell code. This capability is particularly valuable for automated reporting systems, where standardized Excel reports need to be generated and distributed on schedule.

Installing and Using ImportExcel

# Install the ImportExcel module
Install-Module -Name ImportExcel -Force -AllowClobber

# Import the module
Import-Module ImportExcel

# Simple export to Excel
$data = Import-Csv -Path "C:\Data\SalesReport.csv"
$data | Export-Excel -Path "C:\Reports\Sales.xlsx" -AutoSize -TableName "SalesData"

# Advanced Excel creation with multiple sheets
$salesData = Import-Csv -Path "C:\Data\Sales.csv"
$customerData = Import-Csv -Path "C:\Data\Customers.csv"
$productData = Import-Csv -Path "C:\Data\Products.csv"

# Create workbook with multiple sheets
$excelPath = "C:\Reports\Comprehensive_Report.xlsx"

# Remove existing file if present
if (Test-Path $excelPath) { Remove-Item $excelPath }

# Export each dataset to a separate sheet
$salesData | Export-Excel -Path $excelPath -WorksheetName "Sales" -AutoSize -TableStyle Medium9
$customerData | Export-Excel -Path $excelPath -WorksheetName "Customers" -AutoSize -TableStyle Medium10
$productData | Export-Excel -Path $excelPath -WorksheetName "Products" -AutoSize -TableStyle Medium11

# Add a summary sheet with formatting
$summary = [PSCustomObject]@{
    "Report Generated" = Get-Date -Format "yyyy-MM-dd HH:mm"
    "Total Sales Records" = $salesData.Count
    "Total Customers" = $customerData.Count
    "Total Products" = $productData.Count
    "Total Sales Amount" = ($salesData | Measure-Object -Property Amount -Sum).Sum
}

$summary | Export-Excel -Path $excelPath -WorksheetName "Summary" `
    -AutoSize -BoldTopRow -FreezeTopRow

Advanced Excel Formatting and Charts

# Create Excel with conditional formatting
$data | Export-Excel -Path "C:\Reports\FormattedSales.xlsx" `
    -WorksheetName "Sales" `
    -AutoSize `
    -TableStyle Medium6 `
    -ConditionalFormat @(
        New-ConditionalFormattingIconSet -Range "E:E" -IconType ThreeSymbols
    )

# Add a pivot table
$pivotTableParams = @{
    PivotTableName = "SalesByRegion"
    PivotRows = "Region"
    PivotData = @{"Amount" = "Sum"}
    PivotTableStyle = "Medium9"
}

$data | Export-Excel -Path "C:\Reports\PivotReport.xlsx" `
    -WorksheetName "RawData" `
    -AutoSize `
    -PivotTableDefinition $pivotTableParams

# Create a chart
$chartParams = @{
    ChartType = "ColumnClustered"
    XRange = "Region"
    YRange = "Amount"
    Title = "Sales by Region"
    Column = 8
    Row = 1
}

$data | Export-Excel -Path "C:\Reports\ChartReport.xlsx" `
    -WorksheetName "Sales" `
    -AutoSize `
    -Chart $chartParams

"The ability to generate formatted Excel reports without Excel installation eliminated server licensing costs and made our reporting infrastructure significantly more reliable."

Error Handling and Logging: Building Reliable Automation

Production automation scripts must handle errors gracefully, provide meaningful diagnostics, and maintain audit trails. PowerShell offers robust error handling mechanisms through try-catch-finally blocks, error action preferences, and comprehensive logging capabilities. Implementing proper error handling transforms fragile scripts into reliable automation that can recover from transient failures, alert administrators to persistent issues, and provide the information needed for troubleshooting.

Logging serves multiple purposes: it creates an audit trail for compliance requirements, provides debugging information when issues occur, and enables performance monitoring over time. A well-designed logging strategy captures both successful operations and failures, includes timestamps and contextual information, and stores logs in a format that facilitates searching and analysis.

Comprehensive Error Handling

# Set error action preference
$ErrorActionPreference = "Stop"

# Define log file path
$logPath = "C:\Logs\DataImport_$(Get-Date -Format 'yyyyMMdd_HHmmss').log"

# Logging function
function Write-Log {
    param(
        [string]$Message,
        [ValidateSet("INFO","WARNING","ERROR","SUCCESS")]
        [string]$Level = "INFO"
    )
    
    $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
    $logMessage = "[$timestamp] [$Level] $Message"
    
    # Write to console with color coding
    switch ($Level) {
        "ERROR"   { Write-Host $logMessage -ForegroundColor Red }
        "WARNING" { Write-Host $logMessage -ForegroundColor Yellow }
        "SUCCESS" { Write-Host $logMessage -ForegroundColor Green }
        default   { Write-Host $logMessage }
    }
    
    # Write to log file
    Add-Content -Path $logPath -Value $logMessage
}

# Main script with error handling
try {
    Write-Log "Starting data import process" -Level INFO
    
    # Check if source file exists
    $sourcePath = "C:\Data\SalesReport.csv"
    if (-not (Test-Path $sourcePath)) {
        throw "Source file not found: $sourcePath"
    }
    
    Write-Log "Source file found: $sourcePath" -Level INFO
    
    # Import data
    $data = Import-Csv -Path $sourcePath
    Write-Log "Successfully imported $($data.Count) records" -Level SUCCESS
    
    # Validate data
    $invalidRecords = $data | Where-Object { 
        [string]::IsNullOrWhiteSpace($_.CustomerID) -or 
        [string]::IsNullOrWhiteSpace($_.Amount) 
    }
    
    if ($invalidRecords.Count -gt 0) {
        Write-Log "Found $($invalidRecords.Count) invalid records" -Level WARNING
        $invalidRecords | Export-Csv -Path "C:\Data\Invalid_Records.csv" -NoTypeInformation
    }
    
    # Filter valid records
    $validData = $data | Where-Object { 
        -not [string]::IsNullOrWhiteSpace($_.CustomerID) -and 
        -not [string]::IsNullOrWhiteSpace($_.Amount) 
    }
    
    # Database connection with retry logic
    $maxRetries = 3
    $retryCount = 0
    $connectionSuccess = $false
    
    while ($retryCount -lt $maxRetries -and -not $connectionSuccess) {
        try {
            $serverInstance = "SQLServer01\Production"
            $database = "SalesDB"
            
            Write-Log "Attempting database connection (Attempt $($retryCount + 1)/$maxRetries)" -Level INFO
            
            $testQuery = "SELECT 1"
            Invoke-Sqlcmd -ServerInstance $serverInstance -Database $database -Query $testQuery -QueryTimeout 5
            
            $connectionSuccess = $true
            Write-Log "Database connection established" -Level SUCCESS
        }
        catch {
            $retryCount++
            Write-Log "Database connection failed: $($_.Exception.Message)" -Level WARNING
            
            if ($retryCount -lt $maxRetries) {
                $waitTime = [Math]::Pow(2, $retryCount) * 5
                Write-Log "Waiting $waitTime seconds before retry..." -Level INFO
                Start-Sleep -Seconds $waitTime
            }
        }
    }
    
    if (-not $connectionSuccess) {
        throw "Failed to connect to database after $maxRetries attempts"
    }
    
    # Insert data with transaction
    $insertedCount = 0
    foreach ($record in $validData) {
        try {
            $query = @"
INSERT INTO DailySales (OrderDate, CustomerID, ProductID, Quantity, Amount, Region)
VALUES ('$($record.OrderDate)', '$($record.CustomerID)', '$($record.ProductID)', 
        $($record.Quantity), $($record.Amount), '$($record.Region)')
"@
            Invoke-Sqlcmd -ServerInstance $serverInstance -Database $database -Query $query
            $insertedCount++
        }
        catch {
            Write-Log "Failed to insert record for CustomerID $($record.CustomerID): $($_.Exception.Message)" -Level ERROR
        }
    }
    
    Write-Log "Successfully inserted $insertedCount of $($validData.Count) records" -Level SUCCESS
    
    # Archive processed file
    $archivePath = "C:\Data\Archive\SalesReport_$(Get-Date -Format 'yyyyMMdd_HHmmss').csv"
    Move-Item -Path $sourcePath -Destination $archivePath
    Write-Log "Source file archived to: $archivePath" -Level INFO
}
catch {
    Write-Log "Critical error occurred: $($_.Exception.Message)" -Level ERROR
    Write-Log "Stack trace: $($_.ScriptStackTrace)" -Level ERROR
    
    # Send alert email
    $emailParams = @{
        To = "admin@company.com"
        From = "automation@company.com"
        Subject = "Data Import Failed - $(Get-Date -Format 'yyyy-MM-dd HH:mm')"
        Body = "An error occurred during data import:`n`n$($_.Exception.Message)`n`nCheck log file: $logPath"
        SmtpServer = "smtp.company.com"
    }
    
    try {
        Send-MailMessage @emailParams
        Write-Log "Alert email sent" -Level INFO
    }
    catch {
        Write-Log "Failed to send alert email: $($_.Exception.Message)" -Level ERROR
    }
    
    # Exit with error code
    exit 1
}
finally {
    Write-Log "Data import process completed" -Level INFO
}

Scheduling and Orchestration: Running Automation Unattended

Creating PowerShell scripts for data imports and exports represents only half the automation equation; the other half involves scheduling these scripts to run automatically at appropriate intervals. Windows Task Scheduler provides built-in scheduling capabilities, while more sophisticated orchestration platforms like Azure Automation, Jenkins, or dedicated workflow engines offer additional features such as dependency management, parallel execution, and advanced monitoring.

Effective scheduling considers factors like data freshness requirements, system resource availability, dependency chains, and business hours. A sales report might need to run daily at 6 AM before business hours begin, while a data synchronization task might need to execute every 15 minutes throughout the day. Understanding these requirements helps design automation that delivers data when stakeholders need it without overwhelming system resources.

Creating Scheduled Tasks with PowerShell

# Create a scheduled task to run daily data import
$taskName = "Daily_Sales_Import"
$scriptPath = "C:\Scripts\Import-SalesData.ps1"
$logPath = "C:\Logs\ScheduledTask.log"

# Define the action (script to run)
$action = New-ScheduledTaskAction -Execute "PowerShell.exe" `
    -Argument "-ExecutionPolicy Bypass -File `"$scriptPath`" -LogPath `"$logPath`""

# Define the trigger (daily at 6 AM)
$trigger = New-ScheduledTaskTrigger -Daily -At "06:00AM"

# Define settings
$settings = New-ScheduledTaskSettingsSet `
    -AllowStartIfOnBatteries `
    -DontStopIfGoingOnBatteries `
    -StartWhenAvailable `
    -RunOnlyIfNetworkAvailable

# Define principal (run as SYSTEM or specific user)
$principal = New-ScheduledTaskPrincipal -UserId "SYSTEM" -LogonType ServiceAccount -RunLevel Highest

# Register the task
Register-ScheduledTask -TaskName $taskName `
    -Action $action `
    -Trigger $trigger `
    -Settings $settings `
    -Principal $principal `
    -Description "Automated daily sales data import from CSV to database"

# Create a task that runs every 4 hours
$frequentTrigger = New-ScheduledTaskTrigger -Once -At "12:00AM" -RepetitionInterval (New-TimeSpan -Hours 4) -RepetitionDuration (New-TimeSpan -Days 365)

Register-ScheduledTask -TaskName "Frequent_Data_Sync" `
    -Action $action `
    -Trigger $frequentTrigger `
    -Settings $settings `
    -Principal $principal

# View scheduled tasks
Get-ScheduledTask | Where-Object {$_.TaskName -like "*Data*"} | Format-Table TaskName, State, LastRunTime, NextRunTime

# Run a task immediately for testing
Start-ScheduledTask -TaskName $taskName

# Disable a task temporarily
Disable-ScheduledTask -TaskName $taskName

# Remove a task
Unregister-ScheduledTask -TaskName $taskName -Confirm:$false

Building a Monitoring Dashboard

# Script to monitor automation health
$automationTasks = @(
    "Daily_Sales_Import",
    "Hourly_Customer_Sync",
    "Weekly_Report_Generation"
)

$monitoringResults = foreach ($taskName in $automationTasks) {
    $task = Get-ScheduledTask -TaskName $taskName -ErrorAction SilentlyContinue
    
    if ($task) {
        $taskInfo = Get-ScheduledTaskInfo -TaskName $taskName
        
        # Determine status
        $status = switch ($task.State) {
            "Ready" { "OK" }
            "Running" { "Running" }
            "Disabled" { "Disabled" }
            default { "Unknown" }
        }
        
        # Check last run result
        if ($taskInfo.LastTaskResult -ne 0) {
            $status = "Failed"
        }
        
        [PSCustomObject]@{
            TaskName = $taskName
            Status = $status
            LastRunTime = $taskInfo.LastRunTime
            LastResult = $taskInfo.LastTaskResult
            NextRunTime = $taskInfo.NextRunTime
        }
    }
    else {
        [PSCustomObject]@{
            TaskName = $taskName
            Status = "Not Found"
            LastRunTime = $null
            LastResult = $null
            NextRunTime = $null
        }
    }
}

# Export monitoring results
$monitoringResults | Export-Excel -Path "C:\Reports\Automation_Health.xlsx" `
    -AutoSize -TableStyle Medium6 -WorksheetName "Task Status"

# Send alert if any tasks failed
$failedTasks = $monitoringResults | Where-Object { $_.Status -eq "Failed" }
if ($failedTasks.Count -gt 0) {
    $emailBody = "The following automation tasks have failed:`n`n"
    $failedTasks | ForEach-Object {
        $emailBody += "Task: $($_.TaskName)`n"
        $emailBody += "Last Run: $($_.LastRunTime)`n"
        $emailBody += "Result Code: $($_.LastResult)`n`n"
    }
    
    Send-MailMessage -To "admin@company.com" `
        -From "monitoring@company.com" `
        -Subject "Automation Task Failures Detected" `
        -Body $emailBody `
        -SmtpServer "smtp.company.com"
}

"Implementing comprehensive monitoring and alerting for our PowerShell automation reduced our mean time to resolution from hours to minutes, as we now know about failures immediately rather than discovering them when reports are missing."

Security Considerations: Protecting Credentials and Data

Automation scripts often require credentials to access databases, file shares, APIs, and other protected resources. Hardcoding passwords in scripts creates significant security vulnerabilities, as these credentials can be easily discovered by anyone with access to the script files or version control systems. PowerShell provides several secure credential management approaches, including encrypted credential files, Windows Credential Manager integration, and Azure Key Vault for cloud-based automation.

Beyond credential protection, data security requires attention to file permissions, encryption in transit and at rest, and audit logging. Automation scripts should follow the principle of least privilege, using service accounts with only the permissions necessary for their specific tasks. Data containing personally identifiable information (PII) or other sensitive content should be encrypted, and access to automation logs should be restricted to authorized personnel.

Secure Credential Management

# Method 1: Encrypted credential file (user-specific encryption)
# Create encrypted credential file (run once interactively)
$credential = Get-Credential
$credential | Export-Clixml -Path "C:\Secure\db_credential.xml"

# Use encrypted credential in automation script
$credential = Import-Clixml -Path "C:\Secure\db_credential.xml"
$username = $credential.UserName
$password = $credential.GetNetworkCredential().Password

# Method 2: Windows Credential Manager (requires CredentialManager module)
Install-Module -Name CredentialManager -Force

# Store credential in Windows Credential Manager (run once)
New-StoredCredential -Target "DatabaseServer" -UserName "sa" -Password "SecurePassword123" -Persist LocalMachine

# Retrieve credential in automation script
$credential = Get-StoredCredential -Target "DatabaseServer"

# Method 3: Azure Key Vault (for cloud-based automation)
# Requires Az.KeyVault module
Install-Module -Name Az.KeyVault -Force

# Connect to Azure (uses managed identity in production)
Connect-AzAccount

# Retrieve secret from Key Vault
$secretValue = Get-AzKeyVaultSecret -VaultName "MyAutomationVault" -Name "DatabasePassword"
$password = $secretValue.SecretValueText

# Method 4: Environment variables (for containerized automation)
# Set environment variable (in container configuration or host system)
# $env:DB_PASSWORD = "SecurePassword123"

# Retrieve in script
$password = $env:DB_PASSWORD

# Secure database connection using credential
$connectionString = "Server=SQLServer01;Database=SalesDB;User Id=$username;Password=$password;Encrypt=True;"

# Clear sensitive variables after use
Remove-Variable -Name password
[System.GC]::Collect()

Implementing Data Encryption

# Encrypt sensitive data before writing to file
function Protect-DataFile {
    param(
        [string]$SourcePath,
        [string]$DestinationPath,
        [string]$CertificateThumbprint
    )
    
    # Load certificate
    $cert = Get-ChildItem -Path "Cert:\CurrentUser\My\$CertificateThumbprint"
    
    # Read source data
    $data = Get-Content -Path $SourcePath -Raw
    
    # Convert to bytes
    $dataBytes = [System.Text.Encoding]::UTF8.GetBytes($data)
    
    # Encrypt using certificate
    $encryptedBytes = $cert.PublicKey.Key.Encrypt($dataBytes, $true)
    
    # Save encrypted data
    [System.IO.File]::WriteAllBytes($DestinationPath, $encryptedBytes)
}

# Decrypt data file
function Unprotect-DataFile {
    param(
        [string]$SourcePath,
        [string]$DestinationPath,
        [string]$CertificateThumbprint
    )
    
    # Load certificate
    $cert = Get-ChildItem -Path "Cert:\CurrentUser\My\$CertificateThumbprint"
    
    # Read encrypted data
    $encryptedBytes = [System.IO.File]::ReadAllBytes($SourcePath)
    
    # Decrypt
    $decryptedBytes = $cert.PrivateKey.Decrypt($encryptedBytes, $true)
    
    # Convert to string and save
    $data = [System.Text.Encoding]::UTF8.GetString($decryptedBytes)
    Set-Content -Path $DestinationPath -Value $data
}

# Secure file transfer with SFTP (requires Posh-SSH module)
Install-Module -Name Posh-SSH -Force

# Establish SFTP connection
$credential = Get-StoredCredential -Target "SFTPServer"
$session = New-SFTPSession -ComputerName "sftp.example.com" -Credential $credential -Port 22

# Upload file
Set-SFTPItem -SessionId $session.SessionId -Path "C:\Data\SalesReport.csv" -Destination "/uploads/"

# Download file
Get-SFTPItem -SessionId $session.SessionId -Path "/downloads/CustomerData.csv" -Destination "C:\Data\"

# Close session
Remove-SFTPSession -SessionId $session.SessionId

"Moving from hardcoded credentials to Azure Key Vault integration eliminated our biggest security audit finding and provided centralized credential rotation capabilities across all our automation scripts."

Performance Optimization: Handling Large Datasets Efficiently

As data volumes grow, automation scripts that performed adequately with small datasets may become bottlenecks. Processing millions of records requires attention to memory management, algorithm efficiency, and parallelization opportunities. PowerShell provides several approaches for optimizing performance, including streaming data processing, parallel execution with PowerShell jobs or workflows, and leveraging compiled .NET methods for computationally intensive operations.

Understanding PowerShell's pipeline behavior is crucial for optimization. By default, PowerShell processes objects one at a time through the pipeline, which provides excellent memory efficiency but may not be the fastest approach for all scenarios. For operations that benefit from batch processing, collecting objects into arrays or using specialized bulk operations can dramatically improve throughput. Similarly, identifying CPU-bound versus I/O-bound operations helps determine whether parallelization will provide benefits.

Streaming Large Files

# Inefficient: Loads entire file into memory
$data = Get-Content -Path "C:\Data\LargeFile.csv"
foreach ($line in $data) {
    # Process line
}

# Efficient: Streams file line by line
Get-Content -Path "C:\Data\LargeFile.csv" -ReadCount 1000 | ForEach-Object {
    # Process batch of 1000 lines
    foreach ($line in $_) {
        # Process individual line
    }
}

# Most efficient for CSV: Use Import-Csv with pipeline
Import-Csv -Path "C:\Data\LargeFile.csv" | ForEach-Object {
    # Process each record as it's read
    if ([decimal]$_.Amount -gt 1000) {
        # Export high-value records to separate file
        $_ | Export-Csv -Path "C:\Data\HighValue.csv" -Append -NoTypeInformation
    }
}

# Batch processing for database inserts
$batchSize = 1000
$batch = @()
$totalProcessed = 0

Import-Csv -Path "C:\Data\LargeFile.csv" | ForEach-Object {
    $batch += $_
    
    if ($batch.Count -ge $batchSize) {
        # Process batch
        Write-Host "Processing records $totalProcessed to $($totalProcessed + $batch.Count)"
        
        # Convert batch to DataTable for bulk insert
        $dataTable = New-Object System.Data.DataTable
        # Add columns based on first record
        $batch[0].PSObject.Properties | ForEach-Object {
            [void]$dataTable.Columns.Add($_.Name)
        }
        
        # Add rows
        foreach ($record in $batch) {
            $row = $dataTable.NewRow()
            $record.PSObject.Properties | ForEach-Object {
                $row[$_.Name] = $_.Value
            }
            $dataTable.Rows.Add($row)
        }
        
        # Bulk insert
        $bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($connectionString)
        $bulkCopy.DestinationTableName = "SalesData"
        $bulkCopy.WriteToServer($dataTable)
        $bulkCopy.Close()
        
        $totalProcessed += $batch.Count
        $batch = @()
    }
}

# Process remaining records
if ($batch.Count -gt 0) {
    # Process final batch
}

Parallel Processing

# Process multiple files in parallel using jobs
$files = Get-ChildItem -Path "C:\Data\*.csv"

$jobs = foreach ($file in $files) {
    Start-Job -ScriptBlock {
        param($FilePath)
        
        $data = Import-Csv -Path $FilePath
        $summary = [PSCustomObject]@{
            FileName = Split-Path $FilePath -Leaf
            RecordCount = $data.Count
            TotalAmount = ($data | Measure-Object -Property Amount -Sum).Sum
        }
        
        return $summary
    } -ArgumentList $file.FullName
}

# Wait for all jobs to complete
$jobs | Wait-Job

# Retrieve results
$results = $jobs | Receive-Job

# Clean up jobs
$jobs | Remove-Job

# Display results
$results | Format-Table -AutoSize

# Parallel processing using ForEach-Object -Parallel (PowerShell 7+)
$files | ForEach-Object -Parallel {
    $data = Import-Csv -Path $_.FullName
    
    [PSCustomObject]@{
        FileName = $_.Name
        RecordCount = $data.Count
        TotalAmount = ($data | Measure-Object -Property Amount -Sum).Sum
    }
} -ThrottleLimit 5

# Parallel database queries
$regions = @("North", "South", "East", "West")

$regionData = $regions | ForEach-Object -Parallel {
    $region = $_
    
    $query = "SELECT * FROM Sales WHERE Region = '$region'"
    Invoke-Sqlcmd -ServerInstance $using:serverInstance -Database $using:database -Query $query
} -ThrottleLimit 4

# Combine results
$allData = $regionData | ForEach-Object { $_ }

Real-World Implementation Examples

Practical automation scenarios often involve combining multiple techniques—file handling, database operations, error management, and scheduling—into cohesive solutions. The following examples demonstrate complete automation workflows that address common business requirements, showcasing how PowerShell's capabilities integrate to solve real problems.

Automated Daily Sales Report Pipeline

# Complete script for automated sales reporting
param(
    [string]$SourcePath = "\\FileServer\Sales\Daily\",
    [string]$ArchivePath = "\\FileServer\Sales\Archive\",
    [string]$ReportPath = "\\FileServer\Reports\",
    [string]$ServerInstance = "SQLServer01\Production",
    [string]$Database = "SalesDB"
)

# Initialize logging
$logPath = "C:\Logs\SalesReport_$(Get-Date -Format 'yyyyMMdd_HHmmss').log"

function Write-Log {
    param([string]$Message, [string]$Level = "INFO")
    $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
    $logMessage = "[$timestamp] [$Level] $Message"
    Add-Content -Path $logPath -Value $logMessage
    Write-Host $logMessage
}

try {
    Write-Log "Starting sales report pipeline"
    
    # Find today's sales files
    $today = Get-Date -Format "yyyyMMdd"
    $salesFiles = Get-ChildItem -Path $SourcePath -Filter "*$today*.csv"
    
    if ($salesFiles.Count -eq 0) {
        Write-Log "No sales files found for today" -Level WARNING
        exit 0
    }
    
    Write-Log "Found $($salesFiles.Count) sales files to process"
    
    # Combine all sales data
    $allSales = @()
    foreach ($file in $salesFiles) {
        Write-Log "Processing file: $($file.Name)"
        $data = Import-Csv -Path $file.FullName
        $allSales += $data
        
        # Archive processed file
        $archiveFile = Join-Path $ArchivePath $file.Name
        Move-Item -Path $file.FullName -Destination $archiveFile
        Write-Log "Archived: $($file.Name)"
    }
    
    Write-Log "Total records combined: $($allSales.Count)"
    
    # Data validation and cleaning
    $validSales = $allSales | Where-Object {
        -not [string]::IsNullOrWhiteSpace($_.OrderID) -and
        -not [string]::IsNullOrWhiteSpace($_.Amount) -and
        $_.Amount -match '^\d+(\.\d{1,2})?$'
    }
    
    $invalidCount = $allSales.Count - $validSales.Count
    if ($invalidCount -gt 0) {
        Write-Log "Filtered out $invalidCount invalid records" -Level WARNING
    }
    
    # Calculate summary statistics
    $totalSales = ($validSales | Measure-Object -Property Amount -Sum).Sum
    $averageSale = ($validSales | Measure-Object -Property Amount -Average).Average
    $topProducts = $validSales | Group-Object -Property ProductID | 
        Sort-Object Count -Descending | 
        Select-Object -First 10 @{N="ProductID";E={$_.Name}}, Count, 
            @{N="TotalRevenue";E={($_.Group | Measure-Object -Property Amount -Sum).Sum}}
    
    # Insert into database
    Write-Log "Inserting data into database"
    
    $dataTable = New-Object System.Data.DataTable
    $validSales[0].PSObject.Properties | ForEach-Object {
        [void]$dataTable.Columns.Add($_.Name)
    }
    
    foreach ($record in $validSales) {
        $row = $dataTable.NewRow()
        $record.PSObject.Properties | ForEach-Object {
            $row[$_.Name] = $_.Value
        }
        $dataTable.Rows.Add($row)
    }
    
    $connectionString = "Server=$ServerInstance;Database=$Database;Integrated Security=True;"
    $bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($connectionString)
    $bulkCopy.DestinationTableName = "DailySales"
    $bulkCopy.BulkCopyTimeout = 300
    $bulkCopy.WriteToServer($dataTable)
    $bulkCopy.Close()
    
    Write-Log "Database insert completed" -Level SUCCESS
    
    # Generate Excel report
    Write-Log "Generating Excel report"
    
    $reportFile = Join-Path $ReportPath "Sales_Report_$today.xlsx"
    
    # Summary sheet
    $summary = [PSCustomObject]@{
        "Report Date" = Get-Date -Format "yyyy-MM-dd"
        "Total Records" = $validSales.Count
        "Total Sales" = [math]::Round($totalSales, 2)
        "Average Sale" = [math]::Round($averageSale, 2)
        "Files Processed" = $salesFiles.Count
    }
    
    $summary | Export-Excel -Path $reportFile -WorksheetName "Summary" -AutoSize -BoldTopRow
    
    # Top products sheet
    $topProducts | Export-Excel -Path $reportFile -WorksheetName "Top Products" -AutoSize -TableStyle Medium9
    
    # Sales by region
    $salesByRegion = $validSales | Group-Object -Property Region | 
        Select-Object @{N="Region";E={$_.Name}}, Count, 
            @{N="TotalSales";E={[math]::Round(($_.Group | Measure-Object -Property Amount -Sum).Sum, 2)}}
    
    $salesByRegion | Export-Excel -Path $reportFile -WorksheetName "Sales by Region" `
        -AutoSize -TableStyle Medium10 -Chart @{
            ChartType = "ColumnClustered"
            XRange = "Region"
            YRange = "TotalSales"
            Title = "Sales by Region"
        }
    
    Write-Log "Report generated: $reportFile" -Level SUCCESS
    
    # Send email notification
    $emailBody = @"
Daily Sales Report Complete

Summary:
- Total Records: $($validSales.Count)
- Total Sales: $$([math]::Round($totalSales, 2))
- Average Sale: $$([math]::Round($averageSale, 2))
- Files Processed: $($salesFiles.Count)

Report file: $reportFile
"@
    
    Send-MailMessage -To "sales-team@company.com" `
        -From "automation@company.com" `
        -Subject "Daily Sales Report - $(Get-Date -Format 'yyyy-MM-dd')" `
        -Body $emailBody `
        -Attachments $reportFile `
        -SmtpServer "smtp.company.com"
    
    Write-Log "Email notification sent" -Level SUCCESS
    Write-Log "Sales report pipeline completed successfully" -Level SUCCESS
}
catch {
    Write-Log "Error occurred: $($_.Exception.Message)" -Level ERROR
    Write-Log "Stack trace: $($_.ScriptStackTrace)" -Level ERROR
    
    # Send error notification
    Send-MailMessage -To "it-support@company.com" `
        -From "automation@company.com" `
        -Subject "ERROR: Daily Sales Report Failed" `
        -Body "An error occurred during sales report generation. Check log: $logPath`n`nError: $($_.Exception.Message)" `
        -SmtpServer "smtp.company.com"
    
    exit 1
}

Multi-Source Data Integration

# Integrate data from multiple sources into centralized warehouse
param(
    [string]$ConfigPath = "C:\Config\integration_config.json"
)

# Load configuration
$config = Get-Content -Path $ConfigPath -Raw | ConvertFrom-Json

# Initialize logging
$logPath = "C:\Logs\DataIntegration_$(Get-Date -Format 'yyyyMMdd_HHmmss').log"

function Write-Log {
    param([string]$Message, [string]$Level = "INFO")
    $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
    $logMessage = "[$timestamp] [$Level] $Message"
    Add-Content -Path $logPath -Value $logMessage
    Write-Host $logMessage
}

try {
    Write-Log "Starting multi-source data integration"
    
    $allData = @()
    
    # Process each data source
    foreach ($source in $config.DataSources) {
        Write-Log "Processing source: $($source.Name)"
        
        switch ($source.Type) {
            "CSV" {
                $data = Import-Csv -Path $source.Path
                $data | Add-Member -MemberType NoteProperty -Name "SourceSystem" -Value $source.Name
                $allData += $data
            }
            
            "Database" {
                $connectionString = "Server=$($source.Server);Database=$($source.Database);Integrated Security=True;"
                $data = Invoke-Sqlcmd -ServerInstance $source.Server -Database $source.Database -Query $source.Query
                $data | Add-Member -MemberType NoteProperty -Name "SourceSystem" -Value $source.Name
                $allData += $data
            }
            
            "API" {
                $headers = @{
                    "Authorization" = "Bearer $($source.ApiKey)"
                    "Content-Type" = "application/json"
                }
                $response = Invoke-RestMethod -Uri $source.Endpoint -Headers $headers -Method Get
                $data = $response.data
                $data | Add-Member -MemberType NoteProperty -Name "SourceSystem" -Value $source.Name
                $allData += $data
            }
            
            "Excel" {
                $data = Import-Excel -Path $source.Path -WorksheetName $source.WorksheetName
                $data | Add-Member -MemberType NoteProperty -Name "SourceSystem" -Value $source.Name
                $allData += $data
            }
        }
        
        Write-Log "Retrieved $($data.Count) records from $($source.Name)"
    }
    
    Write-Log "Total records collected: $($allData.Count)"
    
    # Transform data to common schema
    $transformedData = $allData | Select-Object @{N="ID";E={$_.ID -or $_.OrderID -or $_.TransactionID}},
        @{N="Date";E={$_.Date -or $_.OrderDate -or $_.TransactionDate}},
        @{N="Customer";E={$_.Customer -or $_.CustomerName -or $_.ClientName}},
        @{N="Amount";E={$_.Amount -or $_.Total -or $_.Value}},
        @{N="Category";E={$_.Category -or $_.Type -or $_.ProductCategory}},
        SourceSystem
    
    # Load into data warehouse
    Write-Log "Loading data into warehouse"
    
    $warehouseConnection = "Server=$($config.Warehouse.Server);Database=$($config.Warehouse.Database);Integrated Security=True;"
    
    $dataTable = New-Object System.Data.DataTable
    @("ID", "Date", "Customer", "Amount", "Category", "SourceSystem") | ForEach-Object {
        [void]$dataTable.Columns.Add($_)
    }
    
    foreach ($record in $transformedData) {
        $row = $dataTable.NewRow()
        $row["ID"] = $record.ID
        $row["Date"] = $record.Date
        $row["Customer"] = $record.Customer
        $row["Amount"] = $record.Amount
        $row["Category"] = $record.Category
        $row["SourceSystem"] = $record.SourceSystem
        $dataTable.Rows.Add($row)
    }
    
    $bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($warehouseConnection)
    $bulkCopy.DestinationTableName = $config.Warehouse.Table
    $bulkCopy.WriteToServer($dataTable)
    $bulkCopy.Close()
    
    Write-Log "Data integration completed successfully" -Level SUCCESS
}
catch {
    Write-Log "Error: $($_.Exception.Message)" -Level ERROR
    exit 1
}

Best Practices and Recommendations

Successful PowerShell automation requires more than just functional code; it demands adherence to best practices that ensure maintainability, reliability, and scalability. As automation scripts evolve from quick solutions to critical business infrastructure, applying software engineering principles becomes essential. Code should be readable, well-documented, and structured in a way that facilitates troubleshooting and enhancement by team members who may not have written the original implementation.

Key Best Practices

• 📝 Comprehensive Documentation: Include inline comments explaining complex logic, maintain README files describing script purposes and requirements, and document configuration parameters with examples
• 🔒 Security First: Never hardcode credentials, implement least-privilege access, encrypt sensitive data, and regularly audit automation scripts for security vulnerabilities
• ⚠️ Robust Error Handling: Use try-catch blocks consistently, implement retry logic for transient failures, log all errors with context, and establish alerting for critical failures
• 📊 Detailed Logging: Log script start and completion, record key decision points and data volumes, include timestamps and severity levels, and implement log rotation to manage disk space
• 🧪 Testing and Validation: Test scripts with sample data before production deployment, validate data integrity after transformations, implement unit tests for complex functions, and maintain separate development and production environments

Code Organization Standards

# Script template with best practices
<#
.SYNOPSIS
    Brief description of script purpose

.DESCRIPTION
    Detailed explanation of what the script does, including data flow and dependencies

.PARAMETER SourcePath
    Path to source data files

.PARAMETER DestinationPath
    Path where processed data will be saved

.EXAMPLE
    .\Script-Name.ps1 -SourcePath "C:\Data" -DestinationPath "C:\Output"

.NOTES
    Author: Your Name
    Date: 2024-01-01
    Version: 1.0
#>

[CmdletBinding()]
param(
    [Parameter(Mandatory=$true)]
    [ValidateScript({Test-Path $_})]
    [string]$SourcePath,
    
    [Parameter(Mandatory=$true)]
    [string]$DestinationPath,
    
    [Parameter(Mandatory=$false)]
    [int]$BatchSize = 1000
)

# Script-level variables
$ErrorActionPreference = "Stop"
$script:LogPath = "C:\Logs\ScriptName_$(Get-Date -Format 'yyyyMMdd_HHmmss').log"

# Functions
function Write-Log {
    param(
        [Parameter(Mandatory=$true)]
        [string]$Message,
        
        [Parameter(Mandatory=$false)]
        [ValidateSet("INFO","WARNING","ERROR","SUCCESS")]
        [string]$Level = "INFO"
    )
    
    $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
    $logMessage = "[$timestamp] [$Level] $Message"
    Add-Content -Path $script:LogPath -Value $logMessage
    
    switch ($Level) {
        "ERROR"   { Write-Host $logMessage -ForegroundColor Red }
        "WARNING" { Write-Host $logMessage -ForegroundColor Yellow }
        "SUCCESS" { Write-Host $logMessage -ForegroundColor Green }
        default   { Write-Host $logMessage }
    }
}

function Test-DataValidity {
    param([object]$Data)
    
    # Implement validation logic
    $isValid = $true
    
    # Example validations
    if ($null -eq $Data) {
        Write-Log "Data is null" -Level ERROR
        $isValid = $false
    }
    
    return $isValid
}

# Main execution
try {
    Write-Log "Script started"
    Write-Log "Parameters: SourcePath=$SourcePath, DestinationPath=$DestinationPath, BatchSize=$BatchSize"
    
    # Pre-execution validation
    if (-not (Test-Path $DestinationPath)) {
        Write-Log "Creating destination directory: $DestinationPath"
        New-Item -Path $DestinationPath -ItemType Directory -Force | Out-Null
    }
    
    # Main processing logic
    # [Implementation here]
    
    Write-Log "Script completed successfully" -Level SUCCESS
}
catch {
    Write-Log "Critical error: $($_.Exception.Message)" -Level ERROR
    Write-Log "Stack trace: $($_.ScriptStackTrace)" -Level ERROR
    throw
}
finally {
    # Cleanup code
    Write-Log "Performing cleanup"
}

Frequently Asked Questions

How do I handle CSV files with different delimiters or encodings?

PowerShell's Import-Csv cmdlet supports custom delimiters through the -Delimiter parameter. For files using semicolons, tabs, or other separators, specify the delimiter explicitly: Import-Csv -Path "file.csv" -Delimiter ";". For encoding issues with international characters, use the -Encoding parameter with values like UTF8, Unicode, or ASCII. You can also combine both: Import-Csv -Path "file.csv" -Delimiter ";" -Encoding UTF8. If the file has no header row, use -Header to specify column names manually.

What's the most efficient way to process very large CSV files that don't fit in memory?

For files too large to load entirely into memory, use streaming approaches. Import-Csv naturally streams records through the pipeline, so avoid collecting all records into an array. Instead, process each record as it's read: Import-Csv -Path "large.csv" | ForEach-Object { # process record }. For even better performance with database operations, use batch processing by collecting records into groups of 1000-5000, then performing bulk inserts. This balances memory usage with database round-trip overhead. The Get-Content cmdlet with -ReadCount parameter can also batch lines for custom parsing scenarios.

How can I securely store database passwords in my automation scripts?

Never hardcode passwords in scripts. For Windows environments, use Windows Credential Manager with the CredentialManager PowerShell module, or create encrypted credential files using Export-Clixml (which uses Windows Data Protection API). For cloud automation, leverage Azure Key Vault or AWS Secrets Manager to store and retrieve credentials programmatically. Service accounts with Windows Authentication eliminate passwords entirely for SQL Server connections. For cross-platform scenarios, consider environment variables set at the system level, though these offer less security than dedicated credential management solutions.

What's the difference between Export-Csv and Export-Clixml, and when should I use each?

Export-Csv creates standard comma-separated value files readable by any application, making it ideal for data exchange with external systems, Excel, or other platforms. However, it only preserves string representations of data. Export-Clixml creates XML files that preserve PowerShell object types, properties, and complex structures, making it perfect for saving and restoring PowerShell objects between sessions with full fidelity. Use Export-Csv for interoperability and human readability; use Export-Clixml for PowerShell-to-PowerShell data persistence where type information matters.

How do I schedule PowerShell scripts to run automatically without keeping a PowerShell window open?

Use Windows Task Scheduler to create scheduled tasks that run PowerShell scripts unattended. Create tasks using the Register-ScheduledTask cmdlet or through the Task Scheduler GUI. Configure the task to run whether the user is logged in or not, and set it to run with highest privileges if needed. The action should execute PowerShell.exe with arguments like -ExecutionPolicy Bypass -File "C:\Scripts\YourScript.ps1". For enterprise environments, consider using orchestration platforms like Azure Automation, Jenkins, or dedicated workflow engines that provide advanced scheduling, dependency management, and centralized monitoring.

Can PowerShell connect to databases other than SQL Server?

Yes, PowerShell can connect to virtually any database system. For MySQL, PostgreSQL, Oracle, and other databases, use ODBC or OLEDB connections through .NET Framework classes. Install database-specific .NET drivers, then create connection strings appropriate for your database. For example, MySQL connections use MySql.Data.dll, while PostgreSQL uses Npgsql. Alternatively, use database-specific PowerShell modules if available (like MySQL PowerShell module). The connection pattern remains similar across databases: establish connection, execute queries, process results, and close connection. ODBC provides a standardized approach that works with any ODBC-compliant database.