2

I am developing an R Shiny application where I need to develop the following capability -

I need to copy rows of cells from Excel (one column at a time to start with) and paste them in Shiny maybe by using selectizeInput, textInput or textAreaInput.

How data looks like in Excel -

Image of data in Excel

Next I need to create a render datatable having this values in a single column spanning over as many rows as there were in the Excel sheet. So far, I have come across this question as reference but adding to this does not let me create the datatable as the output from this are all single vectors.

What I have tried so far -

library(shiny)
library(DT)

ui <- fluidPage(
  selectizeInput(
    "foo", 
    label = "inputs",
    choices = "", 
    multiple = T,
    options = list(
      delimiter = " ", 
      create = T
    )
  ),
  textOutput("results"),
  
  hr(),
  
  "textInput",
  textInput("pasted1", "paste text here"), 
  
  h5("Raw hex code points (20 is space, 0a is linefeed"),
  textOutput("verb1"), 
  h5("Vector of results from splitting on '\\n'"),
  textOutput("split1"),
  
  hr(),
  
  "textAreaInput",
  textAreaInput("pasted2", "paste text here"), 
  
  h5("Raw hex code points (20 is space, 0a is linefeed"),
  textOutput("verb2"), 
  h5("Vector of results from splitting on '\\n'"),
  textOutput("split2"),
  
  dataTableOutput("table1")
)

server <- function(input, output, session) {
  output$results <- renderText(
    paste(paste("item", input$foo))
  )
  
  output$verb1 <- renderPrint(charToRaw(input$pasted1))
  
  output$split1 <- renderText(
    paste(strsplit(input$pasted1, "\n"))
  )
  
  output$verb2 <- renderPrint(charToRaw(input$pasted2))
  
  output$split2 <- renderText(
    paste(strsplit(input$pasted2, "\n"))
  )
  
  df <- reactive({
    df <- as.data.frame(paste(strsplit(input$pasted2, "\n")))
    
  })
  
  output$table1 <- renderDataTable({
    df()
  }, filter="top", class = 'hover cell-border stripe', editable= TRUE,extensions= 'Buttons',
  options = list(dom = 'Bfrtip',pageLength =10,
                 buttons = c('copy','csv','excel','pdf','print'), scrollX=TRUE),server=FALSE)
  
  
}
 

shinyApp(ui, server)

Output I am getting -

Image of Output

I need each record to be in separate rows and not be in a single row and if possible have data type as it was in Excel. Can someone please help me

EDIT

Using this code -

    df_table <- reactive({ 
      if (input$pasted != '') {
        df_table <- fread(paste(input$pasted, collapse = "\n"))
        df_table <-as.data.frame(df_table)
        colnames(df_table) <- c("Method")
        df_table
        
      }
      
    })
    
    output$table <- renderDataTable({
      df_table()
    }, filter="top", class = 'hover cell-border stripe', editable= TRUE,extensions= 'Buttons',
    options = list(dom = 'Bfrtip',pageLength =10,
                   buttons = c('copy','csv','excel','pdf','print'), scrollX=TRUE),server=FALSE)

I am not being able to copy and paste the following text from Excel into the app -

Excel text

The text is getting split into several components as show in the following image-

enter image description here

And I am getting the following error in R -

enter image description here

Can someone please help me solve this problem? I think it has got something to do with collapse = "\n"

Sky_7
  • 77
  • 1
  • 10

1 Answers1

1

Simple solution for this part:

df <- reactive({
    if (input$pasted2 != '') {
      df <- as.data.frame(paste(input$pasted2, collapse = "\n"))
    }
  })

EDIT: The answer before that is not working. Here is the new one! You can use fread() function from data.table package. Solution is (i've tried it):

 df <- reactive({
    if (input$pasted2 != '') {
      df <- fread(paste(input$pasted2, collapse = "\n"))
    }
  })
gokhan can
  • 189
  • 9
  • Hi, this solution helps to load the datatable only after pasting the values from Excel. I need to get the records in separate rows and not in a single row as is shown in the image. I have also updated the image to clear any doubt. 1,2,3,4 need to be in separate rows of the datatable like they were in Excel – Sky_7 Aug 11 '21 at 13:58
  • Thanks for the solution. I'll be using this to develop far more complicated applications. Please update this in your original answer so that I can upvote this. Please upvote the question if you found it helpful!. – Sky_7 Aug 11 '21 at 14:18
  • Did it! Thank you! – gokhan can Aug 11 '21 at 14:28
  • Although, this is working but can you tell me while inputting manually (example - tried inserting 1) instead of copy pasting in the textAreaInput I am getting this error in R Console although the app is running alright - Warning: Error in fread: File '1' does not exist or is non-readable. getwd()=='D:/Folder 1/P4' – Sky_7 Aug 12 '21 at 08:55
  • Using this I am not being able to copy and paste text like "milk shake - chocolate milk - box 10 kg" as it is getting split into several columns and I am getting the warning. I have edited the original question with required images. Please help! – Sky_7 Aug 20 '21 at 13:55