auberginehill/Convert-JsonToCsv.ps1

## 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
	<#
	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