Skip to content

Instantly share code, notes, and snippets.

@auberginehill

auberginehill/Convert-JsonToCsv.ps1

Last active July 26, 2019 10:25
Show Gist options
  • Save auberginehill/a1ad990426398f8e4c49f5b4ae2b476d to your computer and use it in GitHub Desktop.
Save auberginehill/a1ad990426398f8e4c49f5b4ae2b476d to your computer and use it in GitHub Desktop.
Download ZIP
Converts a certain JSON-file (from the current directory) into a CSV-file to the Temp-folder (a Windows PowerShell script).
Raw
Convert-JsonToCsv.ps1
<#
Convert-JsonToCsv.ps1
#>
# Establish common parameters
$ErrorActionPreference = "Stop"
$path = $env:temp
$enumeration = @()
$counter = 0
$file = dir my_filename.json
# Import the JSON file
# Source: http://stackoverflow.com/questions/1825585/determine-installed-powershell-version?rq=1
# Source: https://msdn.microsoft.com/en-us/powershell/reference/5.1/microsoft.powershell.utility/convertfrom-json
# Source: https://blogs.technet.microsoft.com/heyscriptingguy/2014/04/23/powertip-convert-json-file-to-powershell-object/
# Source: http://powershelldistrict.com/powershell-json/
# Source: https://technet.microsoft.com/en-us/library/ee692803.aspx
# Source: http://stackoverflow.com/questions/32887583/powershell-v2-converts-dictionary-to-array-when-returned-from-a-function
If ((($PSVersionTable.PSVersion).Major -lt 3) -or (($PSVersionTable.PSVersion).Major -eq $null)) {
# PowerShell v2 or earlier JSON import
# Requires .NET 3.5 or later
# Credit: Goyuix: "Read Json Object in Powershell 2.0" http://stackoverflow.com/questions/17601528/read-json-object-in-powershell-2-0#17602226
$powershell_v2_or_earlier = $true
If (($PSVersionTable.PSVersion).Major -eq $null) {
$powershell_v1 = $true
# LoadWithPartialName is obsolete, source: https://msdn.microsoft.com/en-us/library/system.reflection.assembly(v=vs.110).aspx
[System.Reflection.Assembly]::LoadWithPartialName("System.Web.Extensions")
} ElseIf (($PSVersionTable.PSVersion).Major -lt 3) {
$powershell_v2 = $true
Add-Type -AssemblyName "System.Web.Extensions"
} Else {
$continue = $true
} # Else
$serializer = New-Object System.Web.Script.Serialization.JavaScriptSerializer
$source = $serializer.DeserializeObject((Get-Content -Path $file.FullName) -join "`n")
} ElseIf (($PSVersionTable.PSVersion).Major -ge 3) {
# PowerShell v3 or later JSON import
$source = (Get-Content -Path $file.FullName -Raw | ConvertFrom-Json)
} Else {
$continue = $true
} # Else
# Convert the JSON data into an object
# Note: The PSCustomObject is for a reference only, it probably has to be manually modified to match the relevant tree structure of the JSON-file.
ForEach ($item in $source.features) {
$counter++
# Find all instances of possible matches
# Credit: Keith Hill "How to capture multiple regex matches, from a single line, into the $matches magic variable in Powershell?" https://stackoverflow.com/questions/3141851/how-to-capture-multiple-regex-matches-from-a-single-line-into-the-matches-mag
$coordinates = ($item.geometry.coordinates -join ';').Replace(',','.').Replace(';',',')
$regex = Select-String "\d+.\d+" -InputObject $coordinates -AllMatches | ForEach {$_.Matches}
$enumeration += $obj_json = New-Object -TypeName PSCustomObject -Property @{
'#' = $counter
'family' = $item.type
'type' = $item.geometry.type
'lon' = $regex | select -ExpandProperty Value | select -First 1
'lat' = $regex | select -ExpandProperty Value | select -Last 1
'id' = $item.properties.id
'name' = $item.properties.name
'name_se' = $item.properties.name_se
'name_en' = $item.properties.name_en
'description' = $item.properties.description
'description_se' = $item.properties.description_se
'description_en' = $item.properties.description_en
'url' = $item.properties.url
'url_se' = $item.properties.url_se
'url_en' = $item.properties.url_en
'order' = $item.properties.order
'layer' = $item.properties.layer
'source' = $item.properties.source
'start' = $item.properties.start
'finish' = $item.properties.finish
'objectid' = $item.properties.objectid
'img_url' = $item.properties.img_url
'img_lic' = $item.properties.img_lic
'img_auth' = $item.properties.img_auth
'mod_date' = $item.properties.mod_date
'user_mod' = $item.properties.user_mod
'display' = $item.properties.display
'coordinates' = $coordinates
'mid' = $item.id
} # New-Object
} # ForEach
# Output
$output = [string]$file.BaseName + ".csv"
$enumeration.PSObject.TypeNames.Insert(0,"JSON columns")
$enumeration_selection = $enumeration | Select-Object '#','family','type','lon','lat','id','name','name_se','name_en','description','description_se','description_en','url','url_se','url_en','order','layer','source','start','finish','objectid','img_url','img_lic','img_auth','mod_date','user_mod','display','coordinates','mid'
$enumeration_selection | Export-Csv "$path\$output" -Delimiter ";" -Encoding UTF8 -NoTypeInformation
@auberginehill
Copy link
Author 

ForEach ($item in $source.cameraStations) {

    # Find all instances of possible matches
    # Credit: Keith Hill "How to capture multiple regex matches, from a single line, into the $matches magic variable in Powershell?" https://stackoverflow.com/questions/3141851/how-to-capture-multiple-regex-matches-from-a-single-line-into-the-matches-mag
    $main_id = $item.id
    $road_station_id = $item.roadStationId
    $nearest_weather_station_id = $item.nearestWeatherStationId

    ForEach ($camera in $item.cameraPresets) {

            $counter++

            $enumeration += $obj_json = New-Object -TypeName PSCustomObject -Property @{

                '#'                             = $counter
                'main_id'                       = $main_id
                'road_station_id'               = $road_station_id
                'nearest_weather_station_id'    = $nearest_weather_station_id
                'camera_id'                     = $camera.id
                'camera_name'                   = $camera.presentationName
                'image_url'                     = $camera.imageUrl
                'measured_time'                 = $camera.measuredTime

            } # New-Object

    } # ForEach $camera

} # ForEach $item


# Output
$output = [string]$file.BaseName + ".csv"
$enumeration.PSObject.TypeNames.Insert(0,"JSON columns")
$enumeration_selection = $enumeration | Select-Object '#','road_station_id','main_id','nearest_weather_station_id','camera_id','camera_name','image_url','measured_time'
$enumeration_selection | Export-Csv "$path\$output" -Delimiter ";" -Encoding UTF8 -NoTypeInformation

@auberginehill
Copy link
Author 

ForEach ($item in $source.features) {

    # Find all instances of possible matches
    # Credit: Keith Hill "How to capture multiple regex matches, from a single line, into the $matches magic variable in Powershell?" https://stackoverflow.com/questions/3141851/how-to-capture-multiple-regex-matches-from-a-single-line-into-the-matches-mag
    $road_station_id = $item.properties.id
    $main_id = $item.id
    $livi_id = $item.properties.liviId
    $nearest_weather_station_id = $item.properties.nearestWeatherStationId
    $road_station_name_a = $item.properties.name
    $road_station_name_b = $item.properties.names.en
    $road_station_name_c = $item.properties.names.sv
    $road_station_name_d = $item.properties.names.fi
    $municipality = $item.properties.municipality
    $province = $item.properties.province
    $camera_type = $item.properties.cameraType
    $collection_interval = $item.properties.collectionInterval
    $road_number = $item.properties.roadAddress.roadNumber
    $carriageway_code = $item.properties.roadAddress.carriagewayCode
    $road_maintenance_class = $item.properties.roadAddress.roadMaintenanceClass
    $contract_area = $item.properties.roadAddress.contractArea


    ForEach ($camera in $item.properties.presets) {

            $counter++

            $enumeration += $obj_json = New-Object -TypeName PSCustomObject -Property @{

                '#'                             = $counter
                'road_station_id'               = $road_station_id                
                'main_id'                       = $main_id
                'livi_id'                       = $livi_id
                'nearest_weather_station_id'    = $nearest_weather_station_id                
                'road_station_name_a'           = $road_station_name_a
                'road_station_name_b'           = $road_station_name_b
                'road_station_name_c'           = $road_station_name_c
                'road_station_name_d'           = $road_station_name_d
                'municipality'                  = $municipality
                'province'                      = $province
                'camera_type'                   = $camera_type
                'collection_interval'           = $collection_interval
                'road_number'                   = $road_number
                'carriageway_code'              = $carriageway_code
                'road_maintenance_class'        = $road_maintenance_class
                'contract_area'                 = $contract_area
                'camera_id'                     = $camera.presetId
                'camera_name'                   = $camera.presentationName
                'in_collection'                 = $camera.inCollection
                'camera_resolution'             = $camera.resolution
                'camera_direction_code'         = $camera.directionCode
                'image_url'                     = $camera.imageUrl
                'direction'                     = $camera.direction

            } # New-Object

    } # ForEach $camera

} # ForEach $item


# Output
$output = [string]$file.BaseName + ".csv"
$enumeration.PSObject.TypeNames.Insert(0,"JSON columns")
$enumeration_selection = $enumeration | Select-Object '#','road_station_id','main_id','livi_id','nearest_weather_station_id','road_station_name_a','road_station_name_b','road_station_name_c','road_station_name_d','municipality','province','camera_type','collection_interval','road_number','carriageway_code','road_maintenance_class','contract_area','camera_id','camera_name','in_collection','camera_resolution','camera_direction_code','image_url','direction'
$enumeration_selection | Export-Csv "$path\$output" -Delimiter ";" -Encoding UTF8 -NoTypeInformation

@auberginehill
Copy link
Author 

ForEach ($item in $source.markers) {

        $counter++

        $enumeration += $obj_json = New-Object -TypeName PSCustomObject -Property @{

            '#'                 = $counter
            'latitude'          = $item.latitude
            'longitude'         = $item.longitude
            'text'              = $item.text
            'name'              = $item.name
            'theme'             = $item.theme

        } # New-Object

} # ForEach $item


# Output
$output = [string]$file.BaseName + ".csv"
$enumeration.PSObject.TypeNames.Insert(0,"JSON columns")
$enumeration_selection = $enumeration | Select-Object '#','latitude','longitude','text','name','theme'
$enumeration_selection | Export-Csv "$path\$output" -Delimiter ";" -Encoding UTF8 -NoTypeInformation

@auberginehill
Copy link
Author 

ForEach ($item in $source.results) {

    $counter++

    $coordinates = ($item.location.coordinates -join ";").Replace(',','.').Replace(';',',')
    $regex = Select-String "\d+.\d+" -InputObject $coordinates -AllMatches | ForEach {$_.Matches}

        $enumeration += $obj_json = New-Object -TypeName PSCustomObject -Property @{
            '#'                 = $counter
            'id'                = $item.id
            'service_id'        = $item.services -join ","
            'identifiers'       = ($item.identifiers | select -First 1).value
            'lon'               = $regex | select -ExpandProperty Value | select -First 1
            'lat'               = $regex | select -ExpandProperty Value | select -Last 1
            'name_fi'           = $item.name.fi
            'name_se'           = $item.name.sv
            'street_address_fi' = $item.street_address.fi
            'street_address_se' = $item.street_address.sv
            'address_zip'       = $item.address_zip            
            'info_1_fi'         = ($item.connections | select -First 1).name.fi
            'info_1_sv'         = ($item.connections | select -First 1).name.sv
            'info_2_fi'         = ($item.connections | select -Skip 1 -First 1).name.fi
            'info_2_sv'         = ($item.connections | select -Skip 1 -First 1).name.sv
            'info_3_fi'         = ($item.connections | select -Skip 2 -First 1).name.fi
            'info_3_sv'         = ($item.connections | select -Skip 2 -First 1).name.sv
            'info_4_fi'         = ($item.connections | select -Skip 3 -First 1).name.fi
            'info_4_sv'         = ($item.connections | select -Skip 3 -First 1).name.sv
            'info_5_fi'         = ($item.connections | select -Skip 4 -First 1).name.fi
            'info_5_sv'         = ($item.connections | select -Skip 4 -First 1).name.sv
            'keywords_fi'       = $item.keywords.fi -join ', '
            'keywords_se'       = $item.keywords.sv -join ', '            
            'note_fi'           = $item.short_description.fi
            'note_sv'           = $item.short_description.sv
            'contract_type_fi'  = ($item.contract_type | select -First 1).description.fi
            'contract_type_sv'  = ($item.contract_type | select -First 1).description.sv
            'picture_url'       = $item.picture_url       
            'www_fi'            = $item.www.fi
            'www_se'            = $item.www.sv     
            'created_time'      = $item.created_time
            'modified_time'     = $item.last_modified_time
            'data_source'       = $item.data_source
            'maintenance'       = ($item.extensions | select -First 1).maintenance_organization
            'department'        = $item.department
            'root_department'   = $item.root_department
            'root_service'      = $item.root_service_nodes -join ","
            'main_service'      = $item.service_nodes -join ","
            'municipality'      = $item.municipality
            'coordinates'       = $coordinates

        } # New-Object

} # ForEach


# Output
$output = [string]$file.BaseName + ".csv"
$enumeration.PSObject.TypeNames.Insert(0,"JSON columns")
$enumeration_selection = $enumeration | Select-Object '#','id','service_id','identifiers','lon','lat','name_fi','name_se','street_address_fi','street_address_se','address_zip','info_1_fi','info_1_sv','info_2_fi','info_2_sv','info_3_fi','info_3_sv','info_4_fi','info_4_sv','info_5_fi','info_5_sv','keywords_fi','keywords_se','note_fi','note_sv','contract_type_fi','contract_type_sv','picture_url','www_fi','www_se','created_time','modified_time','data_source','maintenance','department','root_department','root_service','main_service','municipality','coordinates'
$enumeration_selection | Export-Csv "$path\$output" -Delimiter ";" -Encoding UTF8 -NoTypeInformation

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment