Using PowerShell to convert JSON file into .CSV file?

Question

How can I format the Input.CSV which contains JSON into Output .CSV which will show the column like:

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

See the sample Input.csv in the below:

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""
}",,

The big challenge here is to convert the Date column from UTC into the local timezone.

#some json data...
$jsonData = Import-CSV -path C:\Input.CSV


#convert JSON to PowerShell
$psData = $jsonData | ConvertFrom-Json

# then exporting...
$psData | Export-Csv -Path c:\temp\psData.csv

Answer 1

You can use the .ToLocalTime() method from datetime for this. In PowerShell 7+ ConvertFrom-Json would already convert the CreationTime key in your Json into a datetime instance thus the casting of [datetime] wouldn't be needed but for compatibility with both versions you should leave it as is.

Import-Csv C:\Input.CSV | ForEach-Object AuditData |
    ConvertFrom-Json | ForEach-Object {
        [pscustomobject]@{
            Date       = ([datetime] $_.CreationTime).ToLocalTime().ToString('yyyy-MM-ddTHH:mm:ss')
            IpAddress  = $_.ClientIp
            User       = $_.UserId
            RecordType = $_.RecordType
            Activity   = $_.Activity
            Item       = $_.ItemName
        }
    } | Export-Csv c:\temp\psData.csv -NoTypeInformation