cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dyingod Frequent Visitor
Frequent Visitor

Call web API, receive ZIP, decompress multiple JSON files, Load Them

Hello everyone,

 

Still a noob... I have a simple question. I searched around the forums and found this discussion as the most relevant but I can't make it work and maybe my case is different so I thought to ask.

 

I have an API connection that I can access via a simple web call:

https://api.bla.com/v2/get.php?action=get&project=Project+Name&date=yyyy-mm-dd&token=token&compressi...

 

This call returns a ZIP file containing many JSON files.

 

Making the call is simple but I get stuck on when I receive the ZIP file... how can I:

  1. Decompress the many JSON files
  2. Get M/PowerQuery to recognise them as JSON files (which we all know it can)
  3. Load them into 1+ tables

Preferably 1 table would be great but I can work with millions of them... My issue is how to go from Step 1 to Step 3. I have tried variations of the forum post I linked above and some other google results like:

As always, I thank & salute you all!

 

Rafa

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Call web API, receive ZIP, decompress multiple JSON files, Load Them

@dyingod,

Yes, File.Contents function requires path parameter.

In addition, I haven't found any built-in decompress function that can be used to decompress .zip file. Is there any possibility that your web service return .gzip file? If so, you can use Binary.Decompress to automatically proceed this transformation.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Moderator v-yuezhe-msft
Moderator

Re: Call web API, receive ZIP, decompress multiple JSON files, Load Them

@dyingod,

Create the unzip function as described in this blog: http://sql10.blogspot.sg/2016/06/reading-zip-files-in-powerquery-m.html . Then invoke the function and create a custom column in the returned table. There is an example for your reference.
1.JPG2.JPG



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
dyingod Frequent Visitor
Frequent Visitor

Re: Call web API, receive ZIP, decompress multiple JSON files, Load Them

Hey Lydia!

 

Thanks so much for this. I had also looked into that link but couldn't manage to do anything with it.

 

I get stuck here:

 

let
    Source = File.Contents("[Full path to your zip file]"),
    Files = UnzipContents(Source)
in
    Files

 

At the [Full path to your zip file], which make me think of a place in my drive but obviously it's not as it's within Power BI so what do I put in there? I tried calling the query with the ZIP by name but that didn't work. I really am a newbie. Smiley Sad

 

I think the Custom Column trick sounds amazing and I never thought that possible... but I need to get over this other obstacle first. Can I call on your awesome help again? Smiley Happy

 

Thank you!


Rafa

Moderator v-yuezhe-msft
Moderator

Re: Call web API, receive ZIP, decompress multiple JSON files, Load Them

@dyingod,

Is there any possiblity that you download the zip file to your computer from web?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
dyingod Frequent Visitor
Frequent Visitor

Re: Call web API, receive ZIP, decompress multiple JSON files, Load Them

Hey Lydia,

 

You mean outside PowerQuery? PowerQuery does download it and show it to me as a file:

 2017-11-27 09_49_58-Clipboard.png

 

The reason I'm trying to do it all via Power Query is just so it's automated.

 

Or maybe I am wrong in assuming that file that returns in the screenshot is the actual zip?

 

This is the ZIP file that contains 50+ JSON files I mentioned in the title.

 

Thanks Lydia!

 

Rafa

Moderator v-yuezhe-msft
Moderator

Re: Call web API, receive ZIP, decompress multiple JSON files, Load Them

@dyingod,

Yes, File.Contents function requires path parameter.

In addition, I haven't found any built-in decompress function that can be used to decompress .zip file. Is there any possibility that your web service return .gzip file? If so, you can use Binary.Decompress to automatically proceed this transformation.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.