Json conversion to CSV comes out empty

PowerShell Help
1

Hi there!

I am probably missing something trivial, but i have looked at alot of examples for a while now and cant seem to figure this out myself.

I just want to convert this local Json to CSV, but the “sections” does does have name. I have looked at other forum posts, and found this one:

Here, the data is defined with:
$json.public_cluster.records

I have tried using this example to make the conversion, but my JSON file lacks a recurring definition of the sections, which are a different number for each dataset.

The JSON structure looks like this:


{

"2": {

"id": "2",

"url": "https://domaitest.dk/",

"name": "domainone"

},

"7": {

"id": "7",

"url": "https://domaintest1.dk/",

"name": "midtjysk-pladebearbejdning.dk"

},

"10": {

"id": "10",

"url": "http://domaintest2.dk/",

"name": "domaintest2.dk"

}
}

This is what i am trying with right now, which just gives me an emtpy CSV file:

$file = “C:\Users\jtn\Desktop\PS2020\DNS-SCAN\downloadedjson\downloadeddomains.json”
$json = ConvertFrom-JSON (Get-Content $file -Raw)
$json | ForEach-Object {
$_ -join ‘,’ | ConvertFrom-CSV -Header $json | Export-CSV -path C:\Users\jtn\Desktop\PS2020\DNS-SCAN\downloadedjson\downloadeddomains.csv -NoTypeInformation -Append
}

Can anyone help me in figuring out how i am supposed to define, if i want to get ID URL and NAME exported to the CSV?

Best regards
Jonatan

2

I’m far away from being a JSON expert but could it be this what you’re looking for?

$JSON = 
@'
{
    "2": {
        "id": "2",
        "url": "https://domaitest.dk/",
        "name": "domainone"
    },
        "7": {
        "id": "7",
        "url": "https://domaintest1.dk/",
        "name": "midtjysk-pladebearbejdning.dk"
    },
        "10": {
        "id": "10",
        "url": "http://domaintest2.dk/",
        "name": "domaintest2.dk"
    }
}
'@ | ConvertFrom-Json 

$JSON.psobject.Properties.Value

The output of this would be :

id url                     name
-- ---                     ----
2  https://domaitest.dk/   domainone
7  https://domaintest1.dk/ midtjysk-pladebearbejdning.dk
10 http://domaintest2.dk/  domaintest2.dk
2 Likes
3

Hi Olaf!

Thanks for your input!

That does seem to work quite allright, but im afraid it cant work for my situation here.

I have an API connection where i have to get the JSON data from, so i sort of have to be able to make the “input” an URL Adress.

Do you have a suggestion to approach this? :slight_smile:

4

It does not matter where the JSON comes from. Did you try to output the JSON like I showed?

1 Like
5

Hi Olaf!

Sorry for the very slow response.

Got distracted by a project and completely forgot about this thread.

I did try yes, but not really working, however my input comes from an URL address (an API).
The webadress itself, that i access the API with, is not a .json file path, but a json file is generated for download when the API loads.

Im not really sure how to “grab this in the right way”

How would you approach this? :slight_smile:

6

In your initial question you seemed to have a proper JSON object to work with. If that’s not the case you may solve this issue first. At least the snippet you posted is not a valid JSON format or at least not complete.

It’s going to be hard to recommend something meaningful if you don’t share the needed information.

Regardless of that all …

… is not that helpful. You may be a little more detailed about what happend or not happend and what exactly you tried actually.