Question 108872 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Using PowerShell to convert JSON file into .CSV file?

I need some help formatting 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,[email protected],"{
    ""Id"": ""GUID"",
    ""RecordType"": 20,
    ""CreationTime"": ""2023-01-24T00:39:18"",
    ""Operation"": ""ViewReport"",
    ""OrganizationId"": ""GUID"",
    ""UserType"": 0,
    ""UserKey"": ""123"",
    ""Workload"": ""PowerBI"",
    ""UserId"": ""[email protected]"",
    ""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:temppsData.csv

Any help is much appreciated.

Tags:

2

Answers


  1. 0

    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:temppsData.csv -NoTypeInformation
    Login or Signup to reply.
  2. 0

    I have reproduced in my environment and got expected results as below :

    $x = Import-CSV -path "C:UsersDownloadsemooo.csv"
$y=$x.AuditData | ConvertFrom-Json
$o = [DateTimeOffset]::Parse($y.CreationTime)
$datetime = $o.UtcDateTime
$local = [TimeZoneInfo]::Local
$localtime = [TimeZoneInfo]::ConvertTimeFromUtc($datetime, $local)
$y.CreationTime = $localtime 
$z=$x | Select-Object -Property RecordId,CreationDate, RecordType, Operation, UserId
foreach ($emo in $y.PSObject.Properties) {
$z | Add-Member -MemberType NoteProperty -Name $emo.Name -Value $emo.Value -Force
}
$z | Export-Csv -Path "C:UsersDownloadsvammo.csv"

    enter image description here

    Output :

    enter image description here

    enter image description here

    Now(Just to check):

    $z

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search