Forum Discussion
Trouble Extracting CSV from Zipped Directory on Web Using Power Query
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
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,
6 Replies
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.
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
sorry forget my last message, I realized that I was wrong, I used a link to my hard drive 🙂
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,
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.
