Reading excel files from a link

[Shubhradev]

Hello every one,
While working on a shiny app I am facing some difficulties reading excel files from a link from DropBox and others. Here is the code below that I have used for reading the CSV file from DropBox and other platforms.

upURL <- link
      if(endsWith(upURL,"dl=0")){
        upURL = gsub("dl=0","dl=1",upURL)
      }
      datf <- read_csv(upURL,col_types =  cols(.default = col_character()))
      return(datf)

But in terms of excel file I am facing issues. Can anyone help me on that issue.

Thanks in advance.

[scottyd22]

I'm not sure the error you're receiving, but assuming upURL points to a file with a .xls or .xlsx extension, have you tried using datf <- readxl::read_excel(upURL)?

[Shubhradev]

Hello @scottyd22,
I have tried this function before but it couldn't read the path and always returning the error that I have mentioned below.

> x <- "https://www.dropbox.com/scl/fi/y196q11nz5ilvmdewskqj/excel_Value1_DateTime_ID_Value2.xlsx?dl=1&rlkey=6qpyxqhnlxoseq63vs96vc13n"
> datf <- read_excel(x,col_names = TRUE,col_types = "text")
Error: `path` does not exist: ‘https://www.dropbox.com/scl/fi/y196q11nz5ilvmdewskqj/excel_Value1_DateTime_ID_Value2.xlsx?dl=1&rlkey=6qpyxqhnlxoseq63vs96vc13n’

All other files like tsv, csv, txt, dat are working fine with their respective function. Only problem is with the excel file's link. When I am uploading the original file then its actually working.

[scottyd22]

Ok, thought it was worth a shot. I'm not a Dropbox user, so I am unable to troubleshoot this process on my end. Hope you find a resolution.

[nirgrahamuk]

read.csv may be considered nice, because it offers this sort of flexibility.
Its common for most functions to just accept a file path on your local system and leave it up to you to provide that.
The easiest approach is probably for you to use download.file() and then proceed from there

[Shubhradev]

Hello @nirgrahamuk ,
Thanks for your reply.
It would be easy to understand if show me a little example with the download.file() function. Thank you.

[nirgrahamuk]

x <- "https://www.dropbox.com/scl/fi/y196q11nz5ilvmdewskqj/excel_Value1_DateTime_ID_Value2.xlsx?dl=1&rlkey=6qpyxqhnlxoseq63vs96vc13n"

download.file(x,destfile = "local.xlsx")
datf <- read_excel("local.xlsx",col_names = TRUE,col_types = "text")

[Shubhradev]

Hello @nirgrahamuk , I have tried in your way. But I have faced error again. Did the code work on your machine? I am showing you the code below.

> x <- "https://www.dropbox.com/scl/fi/y196q11nz5ilvmdewskqj/excel_Value1_DateTime_ID_Value2.xlsx?dl=1&rlkey=6qpyxqhnlxoseq63vs96vc13n"
> download.file(x,destfile = "local.xlsx")
trying URL 'https://www.dropbox.com/scl/fi/y196q11nz5ilvmdewskqj/excel_Value1_DateTime_ID_Value2.xlsx?dl=1&rlkey=6qpyxqhnlxoseq63vs96vc13n'
Content type 'application/binary' length 11289 bytes (11 KB)
downloaded 11 KB

> datf <- read_excel("local.xlsx",col_names = TRUE,col_types = "text")
Error in readBin(con, raw(), n = size) : 
  error reading from the connection

In addition, the downloaded file is also not opening on ms excel or similar software.

[nirgrahamuk]

If ms excel wont open it then we wont expect any r package to do better...
Did you personally load this excel to dropbox ?
Maybe whoever loaded this to dropbox has made a mistake

[Flm]

Just tried the code posted above and seems to work

library(tidyverse)


x <- "https://www.dropbox.com/scl/fi/y196q11nz5ilvmdewskqj/excel_Value1_DateTime_ID_Value2.xlsx?dl=1&rlkey=6qpyxqhnlxoseq63vs96vc13n"

download.file(x,destfile = "local.xlsx")
datf <- readxl::read_excel("local.xlsx",col_names = TRUE,col_types = "text")

> str(datf)
tibble [9 × 4] (S3: tbl_df/tbl/data.frame)
 $ Value1 : chr [1:9] "1234 567.21" "2234 567.21" "3234 567.21" "4234 567.21" ...
 $ dmY_hms: chr [1:9] "01/01/2014 00:00:00" "01/01/2014 00:29:58" "01/01/2014 00:59:55" "01/01/2014 01:29:53" ...
 $ ID     : chr [1:9] "AD1231221" "AD1231222" "AD1231223" "AD1231224" ...
 $ Value2 : chr [1:9] "1" "2" "3" "4" ...