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 format of the file is a text file that I downloaded from this website (originally a zip file that I extracted). When I attempted to load this text file into Power BI desktop, unfortunately Power BI was unable to recognize the file. I tried various delimiters to get Power BI to recognize the file format. However, I was unable to reach an effective solution.

 

According to EIA's website: . The extracted text file contains all the dataset's series (ordered by most recent update data first) followed by all of the dataset's categories (ordered by hierarchy from top to bottom). Each line of the extracted text file is a complete object in JSON.

 

Could I please get some help one which keystrokes are required to view this data effectively in Power BI?

 

Thanks a lot

Peter

 

 

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

3 Replies

  • 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.