R shiny: Copying Cells from Excel into SelectizeInput

Question

I want to be able to copy rows of text from Excel and paste it into the text field in our app like this:

Highlight all cells and copy into the Sample labels text field:

Excel

What we want (each row from Excel is its own entry in the text field)

Separated Data

What the app currently does when we paste the rows from Excel (each row from Excel combines into one entry):

Combined Data

Is there a way to copy cells into a SelectizeInput-like text field and somehow separate each cell whwen copied from Excel? Thank you.

Edit 1

Exceptions:

1) When rows have the same values:

only one string shows but what is needed is 4 "Female" labels (if possible)

2) Cells have values with a space:

each label is separated by a space, and repeat words like DAY are only shown once:

Ideally this is what is needed:

Edit 2

When delimiter is set to /n, everything is combined into one label instead rather than separate labels

Brian · Accepted Answer · 2020-01-16T20:21:30.770

Here is a minimal example that works for me.

library(shiny)

ui <- fluidPage(
  selectizeInput(
    "foo", 
    label = "inputs",
    choices = "", 
    multiple = T,
    options = list(delimiter = " ", create = T)
    ),
  textOutput("results")
)

server <- function(input, output, session) {
  output$results <- renderText(
    paste(paste("item", input$foo), collapse = " || ")
  )
}

shinyApp(ui, server)

If you take out the delimiter = " " option, the behavior reverts to the undesired default. When copying from Excel, items are concatenating with spaces, where selectize.js is expecting commas.

https://shiny.rstudio.com/articles/selectize.html

https://github.com/selectize/selectize.js/blob/master/docs/usage.md

Why I think this is the wrong approach:

library(shiny)

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")
)

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"))
  )
}

shinyApp(ui, server)

I think that selectizeInput, like textInput, sanitizes all whitespace, including newlines, to be single spaces. If you use textAreaInput as your container, it will copy the pasted text verbatim, and you can do the splitting on newlines yourself, then use that vector wherever you were going to use the choices returned by selectizeInput.

Thank you!! It works for my example where 1) each cell has different values and 2) it is only one string but I have some cases where it does not copy as desired: 1) If the text from the cells are the same. E.g. If 4 cells has 1, the input text field only shows one "1", but not "1", "1", "1", "1". 2) If the cells have multiple words (e.g. "Hello 1", 2, "3 apples"), what shows in the input field is: "Hello", "1", "2", "3", "apples" rather than "Hello 1, "2", "3 apples" because the delimiter is a space. — karuno, Jan 16 '20 at 16:44
@karuno if you have some pathological cases, you can edit your original question to add more detail. — Brian, Jan 16 '20 at 16:46
Thank you. I have edited the original post accordingly with additional examples. — karuno, Jan 16 '20 at 16:59
@karuno what happens if you change the delimiter to `"\n"`? Does that fix your examples that contain spaces in the cell? — Brian, Jan 16 '20 at 17:06
I have edited the post with a picture. Basically, it all gets combined into one label in the input field. — karuno, Jan 16 '20 at 17:15
@karuno Can you give some more context to your use case? We may be having a square peg/round hole situation here. — Brian, Jan 16 '20 at 17:34
Actually that's what I needed. I didn't know there was a function like TextAreaInput. Thanks! — karuno, Jan 25 '20 at 00:38
For me copying from excel worked with delimited "\t" (which is a tab). Also here is a whole copy-pasting example (copying from selectizeInput was another kettle of fish entirely) https://stackoverflow.com/a/67007451/498201 — drpawelo, Apr 08 '21 at 15:47

R shiny: Copying Cells from Excel into SelectizeInput

1 Answers1

Why I think this is the wrong approach:

Linked