如何用PowerShell将含JSON的CSV转指定格式CSV并转换时区

需求说明 #

需要将包含嵌套JSON数据的Input.CSV转换为指定列的Output.CSV，目标列如下：

Date (Local Timezone, not UTC), IP Address, User, Record Type, Activity, Item

输入样本（Input.CSV） #

RecordId,CreationDate,RecordType,Operation,UserId,AuditData,AssociatedAdminUnits,AssociatedAdminUnitsNames
GUID,1/24/2023 12:39:18 AM,20,ViewReport,User.name@MyDomain.com,"{
    ""Id"": ""GUID"",
    ""RecordType"": 20,
    ""CreationTime"": ""2023-01-24T00:39:18"",
    ""Operation"": ""ViewReport"",
    ""OrganizationId"": ""GUID"",
    ""UserType"": 0,
    ""UserKey"": ""123"",
    ""Workload"": ""PowerBI"",
    ""UserId"": ""User.name@MyDomain.com"",
    ""ClientIP"": ""123.11.22.33"",
    ""Activity"": ""ViewReport"",
    ""ItemName"": ""Important Report"",
    ""WorkSpaceName"": ""banking Confidential Data"",
    ""DatasetName"": ""Corporate Finance"",
    ""ReportName"": ""Corporate Finance"",
    ""CapacityId"": ""GUID"",
    ""CapacityName"": ""Shared On Premium - Reserved"",
    ""WorkspaceId"": ""GUID"",
    ""ObjectId"": ""Corporate Finance"",
    ""DatasetId"": ""GUID"",
    ""ReportId"": ""GUID"",
    ""ArtifactId"": ""GUID"",
    ""ArtifactName"": ""Corporate Finance"",
    ""IsSuccess"": true,
    ""ReportType"": ""PowerBIReport"",
    ""RequestId"": ""GUID"",
    ""ActivityId"": ""GUID"",
    ""DistributionMethod"": ""Shared"",
    ""ConsumptionMethod"": ""Power BI Web"",
    ""ArtifactKind"": ""Report""
}",,

原代码问题 #

你之前的代码错误地将整个CSV文件直接转换为JSON，实际上只有AuditData列是JSON格式，且未处理UTC到本地时区的日期转换。

正确解决方案 #

以下是完整的PowerShell脚本，实现需求并处理时区转换：

# 导入输入CSV文件
$inputCsv = Import-Csv -Path "C:\Input.CSV"

# 初始化结果数组
$outputData = @()

# 遍历每一行数据
foreach ($row in $inputCsv) {
    # 处理AuditData中的转义双引号，转换为标准JSON格式
    $cleanJson = $row.AuditData.Replace('""', '"')
    # 解析JSON为PowerShell对象
    $auditJson = $cleanJson | ConvertFrom-Json

    # 将UTC时间字符串转换为DateTime对象，并指定为UTC时区
    $utcDate = [DateTime]::SpecifyKind([DateTime]$auditJson.CreationTime, [DateTimeKind]::Utc)
    # 转换为本地时区时间
    $localDate = $utcDate.ToLocalTime()

    # 构造目标列的对象
    $outputRow = [PSCustomObject]@{
        "Date (Local Timezone, not UTC)" = $localDate.ToString("yyyy/MM/dd HH:mm:ss")
        "IP Address" = $auditJson.ClientIP
        "User" = $auditJson.UserId
        "Record Type" = $auditJson.RecordType
        "Activity" = $auditJson.Activity
        "Item" = $auditJson.ItemName
    }

    # 添加到结果数组
    $outputData += $outputRow
}

# 导出到输出CSV文件
$outputData | Export-Csv -Path "C:\Output.CSV" -NoTypeInformation -Encoding UTF8

关键说明 #

• JSON解析处理：CSV中的AuditData列使用""作为双引号的转义符，需要先替换为"才能正常解析为JSON。
• 时区转换：通过[DateTime]::SpecifyKind将字符串明确标记为UTC时间，再调用ToLocalTime()转换为当前系统的本地时区时间。
• 日期格式化：使用ToString("yyyy/MM/dd HH:mm:ss")自定义日期输出格式，可根据需求调整。

内容的提问来源于stack exchange，提问作者Senior Systems Engineer