Downloading a CSV from website with ADF

Question

Downloading a CSV from website with ADF

Menegay, Chris 1

I've found bits of info on the web, but still no success in doing this. I'm attempting to do a POST call to the below website to download the CSV so that I can use it in a COPY activity to load it into a database. I've tried doing this directly with a COPY activity, using an HTTP dataset as a source, and also have tried as a web activity and never get a response.

https://www.transtats.bts.gov/DL_SelectFields.aspx?gnoyr_VQ=GEF&QO_fu146_anzr=

Using Fiddler, I came up with a Body of the below
txtSearch=&cboGeography=All&cboYear=All&cboPeriod=All&btnDownload=Download&YEAR=on&MONTH=on&AIRLINE_ID=on&UNIQUE_CARRIER=on&UNIQUE_CARRIER_NAME=on&CARRIER=on&CARRIER_NAME=on&CARRIER_GROUP=on&CARRIER_GROUP_NEW=on&EMPFULL=on&EMPPART=on&EMPTOTAL=on&EMPFTE=on

Not sure what I'm doing wrong here. Any help would be greatly appreciated.

2 answers

Your answer

Answer 1

ShaikMaheer-MSFT 38,546 Microsoft Employee Moderator

Hi @Menegay, Chris ,

Thank you for posting query in Microsoft Q&A Platform.

Yes, you are right. If your API call is going to download a file directly, then we should use HTTP connector type dataset in Source tab of Copy activity. And Sink tab we can use destination storage dataset.

Kindly check below video, where we make a API call which downloads file.
Download file from API and load it in to Sink using Azure Data Factory

I tried to issue GET request call to your below URL. Its opening a web page. It's actually not downloading anything.
https://www.transtats.bts.gov/DL_SelectFields.aspx?gnoyr_VQ=GEF&QO_fu146_anzr=

So, I feel you need to get correct API details which will downloads file on request.

Hope this helps. Please let me know if any concerns.

-------------

Please consider hitting Accept Answer button. Accepted answers help community as well.

Menegay, Chris 1 Reputation point

2022-09-19T14:24:17.147+00:00

Thanks for the reply! Yeah, GET doesn't return anything, you have to do a POST. Basically, if you go to https://www.transtats.bts.gov/DL_SelectFields.aspx?gnoyr_VQ=GEF&QO_fu146_anzr= and click the download button that gets you the file. I'm trying to automate that with ADF so I can then load the contents of that file into a DB

The video example shows using a GET call, not a POST. I think the issue I'm having is sending the right BODY for the POST

EDIT - I think something else might be happening, from ADF I can't even hit https://www.transtats.bts.gov/ with an HTTP GET call. Getting this error:

Error calling the endpoint 'https://www.transtats.bts.gov'. Response status code: 'NA - Unknown'. More details: Exception message: 'NA - Unknown [ClientSideException] An error occurred while sending the request.'.
Request didn't reach the server from the client. This could happen because of an underlying issue such as network connectivity, a DNS failure, a server certificate validation or a timeout.
ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2022-09-27T16:27:21.717+00:00

Hi @Menegay, Chris , Using ADF you can make POST call, to any API. but you cannot make it automating of clicking download button there.

Here the API POST call should download the file then only HTTP connector best suits here.

It seems you are thinking as "Download button anyways post the html form to server and downloads the files, so lets try using POST method on the URL". If yes, then thats not how APIs will actually work. HTML form posting to server is different and APIs are completely different. They both are two different things.

So, kindly check if you can get API details which can directly download a file for you and use the same. Hope this helps.

----------------

Please consider hitting Accept Answer button. Accepted answers help community as well.
Menegay, Chris 1 Reputation point

2022-09-27T17:10:19.19+00:00

Well, it's not really an API though. I was able to do the post using Postman and download the file (simulating what is posted when user submits the form, which I got using the browser dev tools to export a curl). So I've got the POST info figured out. What seems to be the biggest problem is that I can't hit the URL https://www.transtats.bts.gov from ADF. Is there some sort of firewall issue in Azure stopping access to that? Is there a way you can check?

Answer 2

Chandra Sekhar Katabathina (External) 5

Hi,

I have also same requirement, download the zip files using API,

But I have only oauth2 token to access API, I got access_key using web activity.

But now Im facing problem is every pipeline run new accees_key generated,
So I need to pass this key to http linked service in Auth headers ( here it not taken any dynamic values).

Please suggest any way to pass the access_key to http linked serves dynamically.

Thanks,

Chandra.

Share via

Downloading a CSV from website with ADF

2 answers

Your answer