Forum Discussion

pwares's avatar
pwares
Frequent Visitor
5 years ago
Solved

Unable to Import EIA Data - Bulk Data File - Petroleum

Hi There,   I'm interested in importing Petroleum Data from the EIA. https://www.eia.gov/opendata/bulkfiles.php from here I downloaded the petroleum data: http://api.eia.gov/bulk/PET.zip   The ...
Need Help
Tips and Tricks
  • pwares's avatar
    pwares
    5 years ago

    Thanks for your response, unfortunately I received errors when trying your method:

     

    Specifically, the Power BI error mentioned: Unable to connect, we encountered an error while trying to connect, Details: "We found extra errors at the end of JSON input"

     

    As a result, I searched this forum for a similar error message, and came across this tip: 

     

     

    BNajlis
    New Member
    Re: We found extra characters at the end of JSON input.

     

     
    ‎08-24-2016 01:10 PM

    Found the same issue when trying to load a JSONL file (http://jsonlines.org/). To solve it, I ended up importing it as a text file (which takes one record per line) and then using the Parse function in PowerQuery to parse the JSON in each line.

     

     

    Eventually I was able to get a successful result by using the Parse Function

TomMartens's avatar
TomMartens
Icon for Super User rankSuper User
5 years ago

Hey pwares ,

 

this is how the data looks like

To achieve this I did the following after extracting the txt file from the zip archive

Connect to a JSON file

I just changed the file type to All files (*.*)

I choose the PET.txt file

 

Voila, hopefully this is what you are looking for.

 

Regards,

Tom

pwares's avatar
pwares
Frequent Visitor
5 years ago

Thanks for your response, unfortunately I received errors when trying your method:

 

Specifically, the Power BI error mentioned: Unable to connect, we encountered an error while trying to connect, Details: "We found extra errors at the end of JSON input"

 

As a result, I searched this forum for a similar error message, and came across this tip: 

 

 

BNajlis
New Member
Re: We found extra characters at the end of JSON input.

 

 
‎08-24-2016 01:10 PM

Found the same issue when trying to load a JSONL file (http://jsonlines.org/). To solve it, I ended up importing it as a text file (which takes one record per line) and then using the Parse function in PowerQuery to parse the JSON in each line.

 

 

Eventually I was able to get a successful result by using the Parse Function

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi pwares ,

     

    Could text connectors meet your needs?

    You could split columns or expand columns as you like in transform data.

     

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.