0

I have an URL that connects to an API but when I open URL a small pop up opens that asks for a user name and a password, I do have that, and after I type it and click "sign in" a csv file is downloaded.

I am looking for a way to do this with R and that the csv file it gets stored in a dataframe instead of downloaded to the computer to do some more things with it later on.

Thanks for the help

the url API is: https://api.performancehorizon.com/reporting/export/export/click.csv?start_date=2021-11-23+00%3A00%3A00&end_date=2021-11-24+00%3A00%3A00&campaign_id=1011l3888&convert_currency=USD&ref_conversion_metric_id%5B%5D=2

right now I have a code that calls the https and I get a response, but how do I get the csv file as a dataframe?

library(httr)


    GET("https://api.performancehorizon.com/reporting/export/export/click.csv?start_date=2021-11-23+00%3A00%3A00&end_date=2021-11-24+00%3A00%3A00&campaign_id=1011l3888&convert_currency=USD&ref_conversion_metric_id%5B%5D=2", authenticate("xxxxxxx","xxxxxx"))

and this is the response from the server

  Date: 2021-11-24 19:00
  Status: 200
  Content-Type: application/octet-stream; charset=UTF-8
  Size: 395 kB

when I do

data <- GET("https://api.performancehorizon.com/reporting/export/export/click.csv?start_date=2021-11-23+00%3A00%3A00&end_date=2021-11-24+00%3A00%3A00&campaign_id=1011l3888&convert_currency=USD&ref_conversion_metric_id%5B%5D=2", authenticate("xxxxxxx","xxxxxx"))

I get a list of 10

and this is its content

$ url        : chr "https://api.performancehorizon.com/reporting/export/export/click.csv?start_date=2021-11-23+00%3A00%3A00&end_dat"| __truncated__
 $ status_code: int 200
 $ headers    :List of 17
  ..$ server                   : chr "nginx"
  ..$ date                     : chr "Wed, 24 Nov 2021 19:08:23 GMT"
  ..$ content-type             : chr "application/octet-stream; charset=UTF-8"
  ..$ transfer-encoding        : chr "chunked"
  ..$ expires                  : chr "0"
  ..$ cache-control            : chr "must-revalidate, post-check=0, pre-check=0"
  ..$ cache-control            : chr "private"
  ..$ content-disposition      : chr "attachment; filename=\"click_report_2021-11-24_19:08:23.csv\";"
  ..$ content-transfer-encoding: chr "binary"
  ..$ pragma                   : chr "public"
  ..$ cache-control            : chr "no-cache"
  ..$ pragma                   : chr "public"
  ..$ x-ratelimit-limit        : chr "16"
  ..$ x-ratelimit-remaining    : chr "15"
  ..$ x-ratelimit-reset        : chr "0"
  ..$ x-ratelimit-retry-after  : chr "-1"
  ..$ x-request-id             : chr "aa2dec1001680abdddaf22262375a0dd"
  ..- attr(*, "class")= chr [1:2] "insensitive" "list"
 $ all_headers:List of 1
  ..$ :List of 3
  .. ..$ status : int 200
  .. ..$ version: chr "HTTP/1.1"
  .. ..$ headers:List of 17
  .. .. ..$ server                   : chr "nginx"
  .. .. ..$ date                     : chr "Wed, 24 Nov 2021 19:08:23 GMT"
  .. .. ..$ content-type             : chr "application/octet-stream; charset=UTF-8"
  .. .. ..$ transfer-encoding        : chr "chunked"
  .. .. ..$ expires                  : chr "0"
  .. .. ..$ cache-control            : chr "must-revalidate, post-check=0, pre-check=0"
  .. .. ..$ cache-control            : chr "private"
  .. .. ..$ content-disposition      : chr "attachment; filename=\"click_report_2021-11-24_19:08:23.csv\";"
  .. .. ..$ content-transfer-encoding: chr "binary"
  .. .. ..$ pragma                   : chr "public"
  .. .. ..$ cache-control            : chr "no-cache"
  .. .. ..$ pragma                   : chr "public"
  .. .. ..$ x-ratelimit-limit        : chr "16"
  .. .. ..$ x-ratelimit-remaining    : chr "15"
  .. .. ..$ x-ratelimit-reset        : chr "0"
  .. .. ..$ x-ratelimit-retry-after  : chr "-1"
  .. .. ..$ x-request-id             : chr "aa2dec1001680abdddaf22262375a0dd"
  .. .. ..- attr(*, "class")= chr [1:2] "insensitive" "list"
 $ cookies    :'data.frame':    1 obs. of  7 variables:
  ..$ domain    : chr "#HttpOnly_api.performancehorizon.com"
  ..$ flag      : logi FALSE
  ..$ path      : chr "/"
  ..$ secure    : logi FALSE
  ..$ expiration: POSIXct[1:1], format: NA
  ..$ name      : chr "session"
  ..$ value     : chr "g4t0lljcilmm22utbeqcpqvugr"
 $ content    : raw [1:395376] 63 6c 69 63 ...
 $ date       : POSIXct[1:1], format: "2021-11-24 19:08:23"
 $ times      : Named num [1:6] 0 0.012 0.144 0.38 1.463 ...
  ..- attr(*, "names")= chr [1:6] "redirect" "namelookup" "connect" "pretransfer" ...
 $ request    :List of 7
  ..$ method    : chr "GET"
  ..$ url       : chr "https://api.performancehorizon.com/reporting/export/export/click.csv?start_date=2021-11-23+00%3A00%3A00&end_dat"| __truncated__
  ..$ headers   : Named chr "application/json, text/xml, application/xml, */*"
  .. ..- attr(*, "names")= chr "Accept"
  ..$ fields    : NULL
  ..$ options   :List of 4
  .. ..$ useragent: chr "libcurl/7.64.1 r-curl/4.3.2 httr/1.4.2"
  .. ..$ httpauth : num 1
  .. ..$ userpwd  : chr "XXXXXXXXXXXX"
  .. ..$ httpget  : logi TRUE
  ..$ auth_token: NULL
  ..$ output    : list()
  .. ..- attr(*, "class")= chr [1:2] "write_memory" "write_function"
  ..- attr(*, "class")= chr "request"
 $ handle     :Class 'curl_handle' <externalptr> 
 - attr(*, "class")= chr "response"
Juan Lozano
  • 635
  • 1
  • 6
  • 17
  • It looks like that site is just using basic HTTP authentication. You should be able to use something like [httr::authenticate](https://httr.r-lib.org/reference/authenticate.html) to get the file without too much trouble. It's not easy to test since we don't have any login information. – MrFlick Nov 24 '21 at 18:43
  • @MrFlick I look at your suggestion and I looked at that function and I do get a response from the server, knowing that, do you know what else I need to get the csv file as a dataframe? – Juan Lozano Nov 24 '21 at 19:05
  • Did you try `df <- GET(…` and then `content(df)`? – deschen Nov 24 '21 at 19:09
  • @deschen I tried that but I get something that is not the file. – Juan Lozano Nov 24 '21 at 19:12
  • It looks like you used `str()`, not `content()`. It's hard to say for sure what's going on since we can't run and test ourselves. See also: https://stackoverflow.com/questions/35486730/r-httr-contentreq-to-data-frame-after-receiving-data-from-api – MrFlick Nov 24 '21 at 19:21
  • @MrFlick I went and added the API credentials in the code so you guys can test – Juan Lozano Nov 24 '21 at 19:24
  • 1
    Use `df <- read.csv(text=content(data, type = "text", encoding="UTF-8"))` to turn the response object into a data.frame – MrFlick Nov 24 '21 at 19:27
  • @MrFlick Thanks a million it works! you want to add a little answer so I give you the response? – Juan Lozano Nov 24 '21 at 19:35

0 Answers0