How do you move data from tricky worksheets into R? [Excel -> R Meetup Q&A]

General
1

A Question from Meetup: Making the Shift from Excel to R: Perspectives from the back-office

Smita: I had problems with getting Excel data into R, how do people deal with that?

  • Mandip: I’m dealing with that now with a student that I’m teaching who wants to bring in Excel documents from a number of different counterparties and they’re always a bit different, some of them have macros. What we tried to do was to convert them into csv. There’s the readxl function which has worked for us.

  • Tatsu (in chat): 11 Data import | R for Data Science

  • Smita: Can it read from a file that has multiple sheets?
    Vasant: Yes you can, https://readxl.tidyverse.org/

  • Brad (in chat): readxl doesn’t work on password protected workbooks. You can use other packages though

  • Tony: readxl is great if you have a tidy-looking document but it may fail for many excel files. It’s assuming a 2-dimensional table. You should also check out tidyxl + unpivotr This is significantly more powerful for dealing with that.

  • Vasant (in chat): GitHub - nacnudus/unpivotr: Unpivot complex and irregular data layouts in R

4

This topic was automatically closed 42 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.