Forum Discussion

pat_energetics's avatar
pat_energetics
Icon for Advocate II rankAdvocate II
9 years ago

Download and unzip multiple files from URL then query csv file/s within

Hi all,

 

I am trying to automate a process that involves;

1 - File/s download from given fixed URL,

2 - Unzip contents,

3 - Query contained CSV file/s.

 

The URL remains fixed, but files available for download are updated at regular frequency and so do not parmeterize easily using - for example - steps discussed here by Chris Webb and others. Ideally I would like to have a table of downloaded file names to avoid downloading the same file multiple times. Having a local copy of the files downloaded would be an advantage in this case.

 

If zip files were downloaded to a local directory, there seem to be a few different approaches to unzipping contents using either R script here from prathy or a Power Query M language approach discussed here at Mark White's sql10 blogspot.

 

The last step has been completed using power query within Power BI, but currently relies on a manual process to download and unzip contents to a local directory.

 

I would be very interested to know anyone's experience with this.

 

Questions

Is the R script or the Power Query M the best approach for now given the rate at which Power BI data connectors are being developed ?

Is a seamless download , unzip and query all within Power BI too far away ?

5 Replies

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        If you open that URL as a web page in Power Query then drill down through the tables, you can get a list of ZIP files:

        let
    Source = Web.Page(Web.Contents("http://www.nemweb.com.au/REPORTS/CURRENT/Daily_Reports/")),
    Data0 = Source{0}[Data],
    Children = Data0{0}[Children],
    Children1 = Children{1}[Children],
    Children2 = Children1{2}[Children],
    #"Removed Other Columns" = Table.SelectColumns(Children2,{"Children"}),
    #"Expanded Children" = Table.ExpandTableColumn(#"Removed Other Columns", "Children", {"Text"}, {"Children.Text"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Children", each ([Children.Text] <> null and [Children.Text] <> "[To Parent Directory]"))
in
    #"Filtered Rows"

        You could save that as a CSV each day with the R script you mention, then join with the result above the following day to find any new files, and pass those as parameters to a function to Unzip-process-save as CSV etc.

         

        Is that the sort of thing you're after?