Forum Discussion

Mu's avatar
Mu
Frequent Visitor
3 years ago
Solved

Attempting to Download Zipped Json File

I am trying to downlaoad a bulk data file from the EIA (https://www.eia.gov/opendata/bulk/PET.zip). I've been going through a few forums and have gotten this far. Copied this from https://sql1...
Need Help
  • ppm1's avatar
    ppm1
    3 years ago

    Try this instead where each row is a complete JSON string and Json.Document is used to parse it. 

     

     

     

    let
    Source = Web.Contents("https://www.eia.gov/opendata/bulk/PET.zip"),
    Custom1 = UnzipContents(Source),
    Content = Custom1{0}[Content],
    #"Imported CSV" = Csv.Document(Content,null,{0},ExtraValues.Ignore,1252),
    #"Added Custom" = Table.AddColumn(#"Imported CSV", "Custom", each Json.Document([Column1])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Columns", "Custom", {"series_id", "name", "units", "f", "unitsshort", "description", "copyright", "source", "iso3166", "geography", "geography2", "start", "end", "last_updated", "data"}, {"series_id", "name", "units", "f", "unitsshort", "description", "copyright", "source", "iso3166", "geography", "geography2", "start", "end", "last_updated", "data"})
in
    #"Expanded Custom"

     

     

    Pat

ppm1's avatar
ppm1
Icon for Solution Sage rankSolution Sage
3 years ago

Looks like it is a csv file that has at least one field with nested JSON. Please try this instead.

 

let
    Source = Web.Contents("https://www.eia.gov/opendata/bulk/PET.zip"),
    Custom1 = UnzipContents(Source),
    Content = Custom1{0}[Content],
    #"Imported CSV" = Csv.Document(Content,null,{0, 43, 55, 78, 261},ExtraValues.Ignore,1252)
in
    #"Imported CSV"

 

 

Pat

  • Mu's avatar
    Mu
    Frequent Visitor
    3 years ago

    Hi,

    Inputting your code gave the same response that you have in the picture, but the data seems to be all messed up. If you look in the columns, the fields are all separated and I don't know of a way to use it in a chart like that.

    Thanks

    • ppm1's avatar
      ppm1
      Icon for Solution Sage rankSolution Sage
      3 years ago

      Try this instead where each row is a complete JSON string and Json.Document is used to parse it. 

       

       

       

      let
    Source = Web.Contents("https://www.eia.gov/opendata/bulk/PET.zip"),
    Custom1 = UnzipContents(Source),
    Content = Custom1{0}[Content],
    #"Imported CSV" = Csv.Document(Content,null,{0},ExtraValues.Ignore,1252),
    #"Added Custom" = Table.AddColumn(#"Imported CSV", "Custom", each Json.Document([Column1])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Columns", "Custom", {"series_id", "name", "units", "f", "unitsshort", "description", "copyright", "source", "iso3166", "geography", "geography2", "start", "end", "last_updated", "data"}, {"series_id", "name", "units", "f", "unitsshort", "description", "copyright", "source", "iso3166", "geography", "geography2", "start", "end", "last_updated", "data"})
in
    #"Expanded Custom"

       

       

      Pat