locked
JSON to CSV RRS feed

  • Question

  • Hi I am trying to use PowerShell to grab  JSON data from the following website and export the values to a CSV file.

    https://whattomine.com/coins.json

    $file = Invoke-WebRequest -Uri https://whattomine.com/coins.json
    $file |ConvertTo-Csv |Export-Csv C:\Users\Administrator2\Downloads\JSON\test.csv

    When I try to export the data to a CSV file I don't get any data.

    What am I missing here / anyone done anything similar before ?

    Thanks

    Aekash

    Sunday, February 4, 2018 3:30 PM

All replies

  • Does this help?


    $object = Invoke-WebRequest -Uri https://whattomine.com/coins.json |
    ConvertFrom-Json
    $object.coins.Ethereum | ConvertTo-Csv
    "id","tag","algorithm","block_time","block_reward","block_reward24","last_block","difficulty","difficulty24","nethash","exchange_rate","exchange_rate24","exchange_rate_vol","exchange_rate_curr","market_cap","estimated_rewards","estimated_rewards24","btc_revenue","btc_revenue24","profitability","profitability24","lagging","timestamp"
    "151","ETH","Ethash","14.4765","2.91","2.91000000000001","5030054","2.7894684495789E+15","2.76773185333948E+15","192689424210195","0.101892","0.103969939457203","11836.09707067","BTC","$83,419,777,963.75","0.00757","0.00763","0.00077144","0.0007775","100","100","False","1517760021"


    Sunday, February 4, 2018 4:06 PM
  • You need to cnvert it from JSON to PS object $file = Invoke-WebRequest -Uri https://whattomine.com/coins.json | ConvertFrom-Json ...

    Best Regards,
    Łukasz Antoniak


    Please do take a moment to "Vote as Helpful" and/or "Mark as Answer", wherever applicable. Thanks!

    Sunday, February 4, 2018 4:11 PM
  • JSON typically represents hierarchical data while CSV typicality represents one dimensional flat data.

    If you browse the data from the URL you listed with a JSON fomatter (like https://jsonformatter.curiousconcept.com/) you will see that the JSON represents an object that has properties that are collections of properties that are collections... etc.

    What data do you want to see in the CSV? Do you have an example of what the CSV should look like?

     


    Mike Smith TechTrainingNotes.blogspot.com
    Books: SharePoint 2007 2010 Customization for the Site Owner, SharePoint 2010 Security for the Site Owner

    Sunday, February 4, 2018 5:16 PM
  • Too much guessing.

    $results = Invoke-WebRequest -Uri https://whattomine.com/coins.json
    $Json= $results.Content | ConvertFrom-Json
    $json.Coins.Zencash
    $json.Coins.Zencash | Export-Csv zencash.csv -NoType
    

    You can also enumerate "coins" and append each one to a Csv.


    \_(ツ)_/

    Sunday, February 4, 2018 6:05 PM
  • #get and convert web result to PsObject
    $results = Invoke-WebRequest -Uri https://whattomine.com/coins.json
    $Json= $results.Content | ConvertFrom-Json
    
    # extract with names
    $names = $json.Coins.PsObject.Properties|select -expand name
    $names  | 
        ForEach-Object{
            $json.Coins.$_ |
                Add-Member -MemberType NoteProperty -Name Name -Value $_ -PassThru
        } | 
        Export-Csv coins.csv -NoType
    

    An issue occurs because this Json does not have a discrete object structure so we need to "evolve" it a bit.  We need to merge the "name" tag with the record content.

    Of course you can skip the full name and just rely on the "tag" and "id" in the results:

    $json.Coins.PsObject.Properties|select -expand name | 
        ForEach-Object{$json.Coins.$_ } | 
        Export-Csv coins.csv -NoType


    \_(ツ)_/


    • Edited by jrv Sunday, February 4, 2018 6:23 PM
    Sunday, February 4, 2018 6:20 PM
  • This would be a basic shortcut .

    $json.coins.PsObject.Properties.Value | Export-Csv coins.csv -NoType
    


    \_(ツ)_/

    Sunday, February 4, 2018 6:30 PM