Skip to content

Instantly share code, notes, and snippets.

@auberginehill
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.
Converts a certain JSON-file (from the current directory) into a CSV-file to the Temp-folder (a Windows PowerShell script).
<#
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

auberginehill commented Feb 2, 2018

The PSCustomObject is for a reference only, it probably has to be manually modified to match the relevant tree structure of the JSON-file.

@auberginehill
Copy link
Author

auberginehill commented Mar 31, 2018

ForEach ($item in $source.features) {

    $counter++

        $enumeration += $obj_json = New-Object -TypeName PSCustomObject -Property @{
            '#'             = $counter
            'id'            = $item.id
            'type'          = $item.geometry.type    
            'lon_x'         = $item.geometry.coordinates -Split "," | Select -First 1 
            'lat_y'         = $item.geometry.coordinates -Split "," | Select -Last 1 
            'SNMID'         = $item.properties.SNMID
            'ERFID'         = $item.properties.ERFID
            'District'      = $item.properties.TPI
            'Area'          = $item.properties.ARE
            'LFNs'          = $item.properties.LFNs
            'LFNf'          = $item.properties.LFNf            
            'Title'         = $item.properties.TIT
            'Message'       = $item.properties.MES
            'Location'      = $item.properties.LDT
            'Date'          = $item.properties.TIM
            'TRO1'          = $item.properties.TRO1
            'TRO2'          = $item.properties.TRO2
            'Timestamp'     = $item.properties.INP
            'SOT'           = $item.properties.SOT
            'CO1X'          = $item.properties.CO1X
            'CO1Y'          = $item.properties.CO1Y
            'CO2X'          = $item.properties.CO2X
            'CO2Y'          = $item.properties.CO2Y
            'ICOX'          = $item.properties.ICOX
            'ICOY'          = $item.properties.ICOY            
            'ARE2'          = $item.properties.ARE2
            'RNO'           = $item.properties.RNO
            'RNA'           = $item.properties.RNA
            'LCOfCode'      = $item.properties.LCOfCode
            'LCOsCode'      = $item.properties.LCOsCode            
            'DSL'           = $item.properties.DSL
            'DPL'           = $item.properties.DPL
            'SUP'           = $item.properties.SUP
            'RDI'           = $item.properties.RDI
            'DOP'           = $item.properties.DOP
            'PHRNumber'     = $item.properties.PHRNumber
            'PHRCode'       = $item.properties.PHRCode
            'PRVCode'       = $item.properties.PRVCode
            'Symbol'        = $item.properties.SYMBOLOGY
            'SNA'           = $item.properties.SNA
            'PRV2Code'      = $item.properties.PRV2Code            
            'PHRNumber2'    = $item.properties.PHRNumber2
            'PHRNumber3'    = $item.properties.PHRNumber3
            'PHRNumber4'    = $item.properties.PHRNumber4
            'PHRCode2'      = $item.properties.PHRCode2
            'PHRCode3'      = $item.properties.PHRCode3
            'PHRCode4'      = $item.properties.PHRCode4
            'AttUnits'      = $item.properties.AttUnits
            'AttSev'        = $item.properties.AttSev
            'ATTCodes'      = $item.properties.ATTCodes
            'ATTValues'     = $item.properties.ATTValues
            'DIV'           = $item.properties.DIV
            'ROAD_NR'       = $item.properties.ROAD_NR          
            'STA'           = $item.properties.STA
            'STO'           = $item.properties.STO       
            'crs'           = $source.crs.properties.name
            'oid'           = $item.properties.OBJECTID                   

        } # New-Object

} # ForEach


# Output
$output = [string]$file.BaseName + ".csv"
$enumeration.PSObject.TypeNames.Insert(0,"JSON columns")
$enumeration_selection = $enumeration | Where {$_.DOP -eq "RES"} | Select-Object '#','id','type','lon_x','lat_y','SNMID','ERFID','District','Area','LFNs','LFNf','Title','Message','Location','Date','TRO1','TRO2','Timestamp','SOT','CO1X','CO1Y','CO2X','CO2Y','ICOX','ICOY','ARE2','RNO','RNA','LCOfCode','LCOsCode','DSL','DPL','SUP','RDI','DOP','PHRNumber','PHRCode','PRVCode','Symbol','SNA','PRV2Code','PHRNumber2','PHRNumber3','PHRNumber4','PHRCode2','PHRCode3','PHRCode4','AttUnits','AttSev','ATTCodes','ATTValues','DIV','ROAD_NR','STA','STO','crs','oid'
$enumeration_selection | Export-Csv "$path\$output" -Delimiter ";" -Encoding UTF8 -NoTypeInformation

@auberginehill
Copy link
Author

ForEach ($item in $source.data.bikeRentalStations) {

    $counter++

        $enumeration += $obj_json = New-Object -TypeName PSCustomObject -Property @{
            '#'                 = $counter
            'name'              = $item.name
            'stationId'         = $item.stationId
            'lat'               = $item.lat
            'lon'               = $item.lon
            'bikesAvailable'    = $item.bikesAvailable
            'spacesAvailable'   = $item.spacesAvailable
            'allowDropoff'      = $item.allowDropoff
            'state'             = $item.state
        } # New-Object

} # ForEach


# Output
$output = [string]$file.BaseName + ".csv"
$enumeration.PSObject.TypeNames.Insert(0,"JSON columns")
$enumeration_selection = $enumeration | Select-Object '#','name','stationId','lat','lon','bikesAvailable','spacesAvailable','allowDropoff','state'
$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