PowerShell CSV批量处理：实现每10条记录分组循环处理

现有一段可正常运行的PowerShell脚本，通过Import-CSV导入CSV文件后，根据PasswordLastSet和LastLogonDate（采用epoch格式，通过与1比较判断是否为空）的取值情况，生成特定格式的JSON输出。现在需要修改脚本，实现按每10条记录为一组进行循环处理：每组处理完成后添加首尾大括号、去除末尾逗号生成完整JSON结构，再处理下一组，最后一组可能不足10条。自行尝试编写循环逻辑未成功，寻求技术帮助。原脚本代码如下：

#all 4 variations are similar, with the only difference being PasswordLastSet and LastLogonDate either present or not - they are in epoch format, so I compare them to 1, which works fine

$users = import-csv "C:\Users\username\Documents\filename.csv" -delimiter ";"
$body = foreach($_ in $users){
    if($_.'PasswordLastSet' -lt 1){
        
        if($_.'LastLogonDate' -lt 1){
                #last logon and password are empty
'"' +  $_."SamAccountName" + '"' + ':' + '{"DisplayName":"' + $_."DisplayName" + '","UserPrincipalName":"' +  $_."UserPrincipalName" + '","BusinessCategory":"' + $_."BusinessCategory" + '","EmployeeType":"' + $_."EmployeeType" + '","LeaveOfAbsence":"' +  $_."LeaveOfAbsence" + '","LineOfBusiness":"' + $_."LineOfBusiness" + '","Login":"' + $_."Login".replace('\','\\') + '","MultiFactorAuthentication":"' + $_."2FA" + '","DistinguishedName":"' + $_."DistinguishedName".replace('\','\\') + '","EmployeeNumber":"' + $_."EmployeeNumber" + '","Enabled":"' + $_."Enabled" + '"},'
         else{
            #password empty, last logon present
'"' +  $_."SamAccountName" + '"' + ':' + '{"DisplayName":"' + $_."DisplayName" + '","UserPrincipalName":"' +  $_."UserPrincipalName" + '","BusinessCategory":"' + $_."BusinessCategory" + '","EmployeeType":"' + $_."EmployeeType" + '","LeaveOfAbsence":"' +  $_."LeaveOfAbsence" + '","LineOfBusiness":"' + $_."LineOfBusiness" + '","Login":"' + $_."Login".replace('\','\\') + '","MultiFactorAuthentication":"' + $_."2FA" + '","DistinguishedName":"' + $_."DistinguishedName".replace('\','\\') + '","EmployeeNumber":"' + $_."EmployeeNumber" + '","LastLogonDate":"' + $_."LastLogonDate" + '","Enabled":"' + $_."Enabled" + '"},'
    }
    else{
        if($_.'LastLogonDate' -lt 1){
            #password present, last logon empty
'"' +  $_."SamAccountName" + '"' + ':' + '{"DisplayName":"' + $_."DisplayName" + '","UserPrincipalName":"' +  $_."UserPrincipalName" + '","BusinessCategory":"' + $_."BusinessCategory" + '","EmployeeType":"' + $_."EmployeeType" + '","LeaveOfAbsence":"' +  $_."LeaveOfAbsence" + '","LineOfBusiness":"' + $_."LineOfBusiness" + '","Login":"' + $_."Login".replace('\','\\') + '","MultiFactorAuthentication":"' + $_."2FA" + '","DistinguishedName":"' + $_."DistinguishedName".replace('\','\\') + '","EmployeeNumber":"' + $_."EmployeeNumber" + '","PasswordLastSet":"' + $_."PasswordLastSet" + '","Enabled":"' + $_."Enabled" + '"},'
         else{
            #password and last logon present
'"' +  $_."SamAccountName" + '"' + ':' + '{"DisplayName":"' + $_."DisplayName" + '","UserPrincipalName":"' +  $_."UserPrincipalName" + '","BusinessCategory":"' + $_."BusinessCategory" + '","EmployeeType":"' + $_."EmployeeType" + '","LeaveOfAbsence":"' +  $_."LeaveOfAbsence" + '","LineOfBusiness":"' + $_."LineOfBusiness" + '","Login":"' + $_."Login".replace('\','\\') + '","MultiFactorAuthentication":"' + $_."2FA" + '","DistinguishedName":"' + $_."DistinguishedName".replace('\','\\') + '","EmployeeNumber":"' + $_."EmployeeNumber" + '","PasswordLastSet":"' + $_."PasswordLastSet" + '","LastLogonDate":"' + $_."LastLogonDate" + '","Enabled":"' + $_."Enabled" + '"},'
    }
    } 
$output = "{" + $body
$output = $output.TrimEnd(',') + "}"
$output
$output | Out-File "$env:userprofile\Documents\filename.txt" -append -Encoding UTF8

修改后的脚本 #

# 导入CSV文件
$users = Import-Csv "C:\Users\username\Documents\filename.csv" -Delimiter ";"
$outputPath = "$env:userprofile\Documents\filename.txt"

# 清空输出文件（可选，根据需求保留或移除）
if (Test-Path $outputPath) { Clear-Content $outputPath }

# 按每10条记录分组
$groups = $users | Group-Object -Property { [math]::Floor($users.IndexOf($_)/10) }

foreach ($group in $groups) {
    $groupData = [ordered]@{}
    
    foreach ($user in $group.Group) {
        # 构建用户属性对象
        $userProps = [ordered]@{
            DisplayName = $user.DisplayName
            UserPrincipalName = $user.UserPrincipalName
            BusinessCategory = $user.BusinessCategory
            EmployeeType = $user.EmployeeType
            LeaveOfAbsence = $user.LeaveOfAbsence
            LineOfBusiness = $user.LineOfBusiness
            Login = $user.Login -replace '\\','\\'
            MultiFactorAuthentication = $user."2FA"
            DistinguishedName = $user.DistinguishedName -replace '\\','\\'
            EmployeeNumber = $user.EmployeeNumber
            Enabled = $user.Enabled
        }

        # 根据条件添加PasswordLastSet字段
        if ($user.PasswordLastSet -ge 1) {
            $userProps["PasswordLastSet"] = $user.PasswordLastSet
        }

        # 根据条件添加LastLogonDate字段
        if ($user.LastLogonDate -ge 1) {
            $userProps["LastLogonDate"] = $user.LastLogonDate
        }

        # 将用户信息添加到组数据中
        $groupData[$user.SamAccountName] = $userProps
    }

    # 将组数据转换为JSON并写入文件
    $groupJson = $groupData | ConvertTo-Json -Compress
    $groupJson | Out-File $outputPath -Append -Encoding UTF8
}

关键改进说明 #

• 分组逻辑：通过Group-Object结合计算属性[math]::Floor($users.IndexOf($_)/10)，将每10条记录分为一组，自动处理最后一组不足10条的情况
• JSON生成优化：使用PowerShell的[ordered]哈希表构建有序属性，再通过ConvertTo-Json生成标准JSON，彻底避免手动拼接字符串导致的转义错误和格式问题
• 空值处理简化：统一先构建基础属性集合，再根据PasswordLastSet和LastLogonDate的取值决定是否添加对应字段，逻辑更清晰
• 输出控制：可选清空输出文件，每组生成独立的JSON对象并追加到文件，符合需求中的分组输出要求

内容的提问来源于stack exchange，提问作者shalan