3

How to search within excel workbook programmatically using R?

Say I want to locate string "foo" and return all the cell addresses (row, col, worksheet name) where the text resides.

Is there any convenience function, instead of writing own script using xlsx::read.xlsx or XLConnect::readWorksheet ?

To give a little background, I want to extract only specific regions, which I need to specify somehow.

Extracting specific regions is supported by both read* functions mentioned above, however they require as input the row and column indices, whereas what I know beforehand are only some keywords (representing table column headings). Example: I know there will be data about "exposure" or "weight" organized as column region with the said heading, but don't know exactly where, so want to auto-detect and extract that.

Daniel Krizian
  • 4,586
  • 4
  • 38
  • 75
  • 2
    Why would you want to do something like this? Either import the data into R or use Excel/VBA facilities. – Roland Sep 02 '14 at 14:31
  • 2
    The normal way would be to import the data and then do your search. Or search the data in Excel and import what you need. – Hack-R Sep 02 '14 at 14:35
  • So, why can't you read-in all the data and subset? Excel sheets can hardly become so big that you run into RAM restrictions. – Roland Sep 02 '14 at 14:52
  • @Roland just asking whether such convenience function already exists. If not, I can first [convert all the sheets into giant R list](http://stackoverflow.com/questions/12945687/how-to-read-all-worksheets-in-an-excel-workbook-into-an-r-list-with-data-frame-e?rq=1) and then [search within the list using e.g. `rlist::equal` (which btw supports fuzzy match too)](http://cran.r-project.org/web/packages/rlist/vignettes/Searching.html) – Daniel Krizian Sep 02 '14 at 15:01

0 Answers0