none
How to get an CSV file from that's downloaded via a webpage? RRS feed

  • Question

  • Hello,

     

    We have a Tableau server which can provide data via a CSV file.

    (If you are in a browser, you goto http://my.tableau.server.com/site/MyReport.CSV and then the browser downloads a CSV file to your Downloads folder Smiley Happy    The Tableau server uses the windows credentials for authentication)

     

    I'd love to import this data via PowerQuery.  Is this possible and can you point me to how to start?

    When I do this:

    let
        Source = Web.Contents("https://tableauq.idexx.com/#/site/emea/views/IDEXXLOCATIONS/USA.csv"),
        CSV= Csv.Document( Source,[Delimiter=",", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None])
    in
        CSV

    At the source line, I get this(which I don't really understand):

    <!DOCTYPE html><html xmlns:ng="" xmlns:tb=""><head ng-csp><meta charset="UTF-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=1024, maximum-scale=2"><meta name="format-detection" content="telephone=no"><meta name="vizportal-config" data-buildId="819vpf68wrqrryd7mv8zd7vi" data-staticAssetsUrlPrefix=""><link rel="stylesheet" type="text/css" href="vizportal.css?819vpf68wrqrryd7mv8zd7vi"><script src="/javascripts/api/tableau-2.2.1.min.js?819vpf68wrqrryd7mv8zd7vi"></script><script src="vizportalMinLibs.js?819vpf68wrqrryd7mv8zd7vi"></script><script src="vizportal.min.js?819vpf68wrqrryd7mv8zd7vi"></script></head><body class="tb-body"><div ng-app="VizPortalRun" id="ng-app" tb-window-resize class="tb-app"><div ui-view="" class="tb-app-inner"></div><tb:react-toaster></tb:react-toaster><script type="text/ng-template" id="inline_stackedElement.html"><div tb-window-resize tb-left="left" tb-top="top" tb-right="right" tb-bottom="bottom" tb-visible="visible" class="tb-absolute">...

    But, it doesn't actually look like it's getting the actual CSV file.... (this is a bit confusing as this question is similar and insinuates that it should work: link )

    Many Thanks,

    Alex

    (PS: I tried posting this at community.powerbi.com but no joy)


    Wednesday, February 13, 2019 1:47 PM

Answers

  • Are the URLs actually exactly the same? Above you had:

    https://tableauq.idexx.com/#/site/emea/views/IDEXXLOCATIONS/USA.csv

    But the Request URL from the DevTools appears to be:

    https://tableauq.idexx.com/t/emea/views/IDEXXLOCATIONS/USA.csv

    Try the second URL. Maybe it will give you direct access to the CSV.

    Ehren


    Wednesday, February 27, 2019 12:45 AM
    Owner

All replies

  • Have you tried using the URL as a direct path to the CSV? In the sample you linked to where they loaded an Excel workbook from a website, it would be more akin to doing this:
    let
        Source = "https://tableauq.idexx.com/#/site/emea/views/IDEXXLOCATIONS/USA.csv",
        CSV= Csv.Document( Source,[Delimiter=",", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None])
    in
        CSV


    Wednesday, February 13, 2019 5:25 PM
  • Hi pdelia,

    Yup, I've tried a few iterations of that....

    The one you show, just yields the URL in the 1st cell of a two column table....

    If I put the Csv url into get New Data->From Web what Pops up is this:

    With this Power query code:

    let
        Source = Csv.Document(Web.Contents("https://tableauq.idexx.com/#/site/emea/views/IDEXXLOCATIONS/USA.csv"),[Delimiter=",", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}})
    in
        #"Changed Type"

    (My impression is that PQ sees the the .csv extension and assumes it'll be fed a .csv  ....it doesn't actually check what it's getting.

    Also it seems to be that CSV.Document blindly accepts the 1st argument as a CSV document...where as Excel.Workbook does some extra checking and somehow knows to wait/look for the attachment that's the Excel file.

    Either way, I'm flying blind here :)


    Thursday, February 14, 2019 8:07 AM
  • Have you ensured that the Tableau URL data source is using Windows Credentials (and not just Anonymous)? You can view/change this in the "Data source settings" dialog.

    Ehren

    Thursday, February 14, 2019 7:45 PM
    Owner
  • Hi Ehren,

    Yes, I specifically remember setting that when I 1st tried this....

    ...but, since you mentioned it...I did go back and verify...

    what I've found is that I get the same result (that <!DOCTYPE html> line...)even if I set it to Anonymous or Basic or back to Windows->use my current credentials.

    I don't know what that means....

    ....with IE, Edge & Chrome, when I goto the URL, it downloads the CSV right away... with Firefox(which doesn't have as good Windows credentials support), I get prompted to input my username&password and then it works (as an aside, this is what I used to figure out what format the userid should be and tired that with the Data Sources Settings -> Basic...but still no joy)

    Thanks! If you have any other suggestions I'd be happy to try them.

    Kind Regards,

    Alex

    Friday, February 15, 2019 7:15 PM
  • Hi Alex. If you've set the credentials for the data source to Windows, then this should work. However, one of my coworkers pointed out that the URL you're hitting in your browser may not actually be a direct link to the CSV. It may instead be doing some redirects or possibly downloading the CSV via javascript. Can you look at what requests are actually happening behind the scenes using a the browser's built-in F12 tools?

    Ehren

    Tuesday, February 19, 2019 11:45 PM
    Owner
  • Hi Ehren,

    Yes, it looks like it comes from a javascript(.js) thing when I do like you suggest and open up the chrome developer tools(see screen shot).

    Is this something I can interpret via Power Query?

    Tuesday, February 26, 2019 12:40 PM
  • Can you look at the Network tab in the Chrome DevTools? You should hopefully be able to see the actual request to download the CSV file. Try using that URL from PQ instead, and let me know if that works.

    Ehren

    Tuesday, February 26, 2019 6:30 PM
    Owner
  • Hi Ehern,

    The Network tab looks sort of similar....I've gone through each of the URLs and either it's not really a URL or what I end up with is a JQuery file that looks to be code....

    I'm sort of guessing, but I get the feeling that when I got to the original .CSV URL, that it kicks off the script to create the CSV and then passes it on (and so when I goto the URL directly then it's no longer waiting for a connecction)....

    ......(not sure what to do with this .... am I at a dead end?)

    Tuesday, February 26, 2019 7:51 PM
  • What do you see if you click on the USA.csv entry?

    Ehren

    Tuesday, February 26, 2019 8:04 PM
    Owner
  • the below (which looks the same is the same as this: https://social.technet.microsoft.com/Forums/getfile/1407718 from earlier today...just the color is different :)

    (edit, to be clear...this one is a screen shot from just now...)
    • Edited by alex1alex Tuesday, February 26, 2019 8:19 PM added clarification
    Tuesday, February 26, 2019 8:18 PM
  • That's strange. I'd expect you to get a pane in the righthand area with Headers, Preview, Response, Cookies, and Timing tabs.

    Chrome DevTools

    Ehren

    Tuesday, February 26, 2019 8:27 PM
    Owner
  • AH!  I was clicking the corresponding underlined link in the "Initiator" column...that's what the screen shot was from.   If I click on the USA.csv then I get the below ..... but the REQUEST URL is the same..... :(

    Tuesday, February 26, 2019 8:44 PM
  • What do you see if you click on the Response tab? Is it the CSV data, or some html/js?

    Ehren

    Tuesday, February 26, 2019 9:04 PM
    Owner
  • both Response and Preview tabs give me a Failed to Load Response Data

    (clicking all of the other items in the Name column give me html/js)

    .......

    Tuesday, February 26, 2019 10:01 PM
  • Are the URLs actually exactly the same? Above you had:

    https://tableauq.idexx.com/#/site/emea/views/IDEXXLOCATIONS/USA.csv

    But the Request URL from the DevTools appears to be:

    https://tableauq.idexx.com/t/emea/views/IDEXXLOCATIONS/USA.csv

    Try the second URL. Maybe it will give you direct access to the CSV.

    Ehren


    Wednesday, February 27, 2019 12:45 AM
    Owner
  • Huh, I never noticed that the # turns into a t :$

    Yeah, that works!  Thanks for putting the time into this question.  I learned a few things through the process!

    Kindest Regards,

    Alex

    Wednesday, February 27, 2019 8:02 AM