Unable to Import EIA Data - Bulk Data File - Petroleum

[pwares]

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

[TomMartens]

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 · Accepted solution]

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

[pwares · Accepted solution]

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]

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.