none
Convert and Export JSON to CSV RRS feed

  • General discussion

  • Hello,

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

    $jsonRequest=Invoke-WebRequest 
    $jsonRequest.Content
    ConvertFrom-Json 
    Export-CSV -path c:\123lasttest -NoTypeInformation

    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






    • Edited by Petko94 Saturday, July 20, 2019 9:13 AM
    Thursday, July 18, 2019 5:37 PM

All replies

  • $jsonRequest.Content | 
         ConvertFrom-Json | 
         Export-CSv myjson.csv
    


    \_(ツ)_/

    Thursday, July 18, 2019 6:01 PM
    Moderator
  • This is all that I get when I try that: 

    Count Length LongLength Rank SyncRoot IsReadOnly IsFixedSize IsSynchronized
    25 25 25 1 System.Object[] FALSE TRUE FALSE

    • Edited by Petko94 Saturday, July 20, 2019 9:13 AM
    Thursday, July 18, 2019 6:08 PM
  • For some reason it needs to be done in two steps.

    $csv = $jsonRequest.Content | ConvertFrom-Json
    $csv | Export-CSv myjson.csv -NoType


    \_(ツ)_/

    Thursday, July 18, 2019 6:26 PM
    Moderator
  • I managed to export the information, but for some reason exported some Image URLs. Do you have an idea how to exclude them?

    Thank you

    Friday, July 19, 2019 7:10 AM
  • My code does not cause issues.

    I guess you will just have to learn PowerShell and figure out how to get what you need.


    \_(ツ)_/

    Friday, July 19, 2019 8:13 AM
    Moderator
  • That's because the JSON contains those links.

    Building on JRV's suggested code, try this:

    $csv = (Invoke-WebRequest -Uri https://api.punkapi.com/v2/beers).content| ConvertFrom-Json
    $csv |
        select -Property * -ExcludeProperty image_url |
            Export-CSV c:\temp\123lasttest.csv -NoTypeInformation
    Note that this will still leave you with some "problems": many of the columns will contain hashes and not simple values.


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)



    Friday, July 19, 2019 7:05 PM
  • I see someone else needs to read the docs:

    $csv | select * -ExcludeProperty image_url

    Just exclude the properties you don't want.

    This is why it is critical to read the help carefully and completely and to formally learn PowerShell.  Copying and guessing only causes coders to waste a lot of time and to write very bad code.

    I have been telling programmers this for more than 30 years so don't feel picked on.  When they fi ally listen their coding and approach to programming takes a whole new direction.  Things become much easier.


    \_(ツ)_/

    Friday, July 19, 2019 7:25 PM
    Moderator
  • I started off with "Select" and naming the individual properties to keep, but forgot about the "ExcludeProperties".

    I don't feel picked on. Switching back and forth between Perl and Powershell, I tend to fall back on techniques from years gone by.


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Friday, July 19, 2019 8:20 PM