none
Open JSON Formatted File - From Data.gov

    Question

  • http://www.eia.gov/beta/api/bulkfiles.cfm publishes energy stats in a single, bulk/all data JSON-formatted TXT file.

    I've downloaded the file, opened with Power Query.  PQ evaluates the file but doesn't acknowledge the JSON formatting - dumps everything into a single column.  No luck splitting the column using the ":" delimiter.

    I tried editing the Source from the PQ query settings pane, selecting JSON formatting.  I get an "Expected EOF, found '{'. error

    Thoughts?

    Friday, June 06, 2014 3:32 AM

Answers

  • This is a format we don't handle very well automatically right now. Json.Document expects the file to contain a single JSON object, but this file actually contains multiple lines with a new object on each line. If you show the formula bar in the editor, you should see something like this:

    = Json.Document(File.Contents("C:\Users\CurtH\Desktop\SEDS.json"))

    Edit this line by replacing "Json.Document" with "Lines.FromBinary". You should now see multiple lines with JSON text on each line. Next, use "To Table" to convert the list of lines into a table with a single column. Highlight that column and find the menu option to "Parse" it, picking "JSON" as the format. Finally, you can click the expand icon in the column header in order to expand out each of the JSON records.

    Friday, June 06, 2014 11:50 AM

All replies

  • This is a format we don't handle very well automatically right now. Json.Document expects the file to contain a single JSON object, but this file actually contains multiple lines with a new object on each line. If you show the formula bar in the editor, you should see something like this:

    = Json.Document(File.Contents("C:\Users\CurtH\Desktop\SEDS.json"))

    Edit this line by replacing "Json.Document" with "Lines.FromBinary". You should now see multiple lines with JSON text on each line. Next, use "To Table" to convert the list of lines into a table with a single column. Highlight that column and find the menu option to "Parse" it, picking "JSON" as the format. Finally, you can click the expand icon in the column header in order to expand out each of the JSON records.

    Friday, June 06, 2014 11:50 AM
  • Brilliant.  Worked.  Thanks.

    -Eric.

    Friday, June 06, 2014 12:40 PM