Trouble Extracting CSV from Zipped Directory on Web Using Power Query

[AwatefM]

I want to import the file "GeolocalisationEtablissement_Sirene_pour_etudes_statistiques_utf8.csv", which is regularly updated on the French government website.

• I can directly download the URL of the file: https://www.data.gouv.fr/fr/datasets/r/ba6a4e4c-aac6-4764-bbd2-f80ae345afc5
• It points to a zipped directory: "GeolocalisationEtablissement_Sirene_pour_etudes_statistiques_utf8.zip"
• once decompressed, it contains a CSV file :  "GeolocalisationEtablissement_Sirene_pour_etudes_statistiques_utf8.csv"

Is it possible to get the CSV file with Power Query without having to save the file to my hard drive?

I only get an empty table when I apply this script:

let // Download the zip folder SourceFolderZipped SourceFolderZippe = Web.Contents("https://www.data.gouv.fr/fr/datasets/r/ba6a4e4c-aac6-4764-bbd2-f80ae345afc5"),

// Use the UnzipContents function to extract the contents of the ZIP folder UnzippedContents = UnzipContents(SourceFolderZippe),

// Access the content of the CSV file (if it is the first and only file in the ZIP) CsvContent = UnzippedContents{0}[Content]

in CsvContent

with the UnzipContents function found on tyhis blog Reading Zip files in PowerQuery / M (sql10.blogspot.com)

If it is not feasible, I would appreciate some guidances with any other strategies, such as including Python code or something.

Thank you in advance for your feedback,

AwatefM

Thank you in advance for your feedback

[AwatefM]

sorry forget my last message, I realized that I was wrong, I used a link to my hard drive 🙂

[ibarrau · Accepted solution]

Hi Greg, thanks for reaching. The file seems to have a compression that's not allowed by the methods in the power query script. Zips have several settings for compression that makes kind of difficult to create a function that works for almost all of them. They way your file looks like should work with this alternativa function for extracting:

https://github.com/Michael19842/PowerBiFunctions/blob/main/ZipFile/Unzip.m

Remember this function only extracts. The interpretation of the file inside it's a different thing.

I hope that helps,

[AwatefM]

Thank you so much @ibarrau and also @Greg_Deckler  to connect us, it works!!!! It wasn't simple at all and far beyond my skills as a beginner in Power BI. If I understand correctly, this means I'll face a similar trouble with XML files from French government too if compression steps aren't recognized by Power Query.

[AwatefM]

@Greg_Deckler 

Ok, it makes sense, but I already used this function to get an XML file and it worked perfectly. However, in this case, the file wasn't embedded into a folder. Here's my M code:

let
Source = Web.Contents("https://www.data.gouv.fr/fr/datasets/r/e9bfe674-7872-4bbc-bddf-e146bc8690fa"),
DezipFiles = UnzipContents(Source),
#"Type modifié XML" = Xml.Tables(File.Contents("C:\Users\amala\Downloads\export-fiches-rncp-v3-0-2024-04-05\export_fiches_RNCP_V3_0_2024-04-05.xml"))
in
#"Type modifié XML"

Don't know if this may help you.

I'm looking forward to hearing from you,

AwatefM

[Greg_Deckler]

@AwatefM From my testing, it appears as if the binary contents aren't being extracted correctly. Will try to troubleshooting and see what is going on.

[Greg_Deckler]

@AwatefM Interesting. If I download the file and use File.Contents instead of Web.Contents everything seems to work using the unzip function. @ibarrau any insights into why this doesn't seem to work with Web.Contents?