0

I have the following vector that assigns col_types to a data frame: 

mytypes = c("text","text","text","text","text","text","text","text","text","text","text","numeric","text","text","text","text","numeric")

The next line creates a data frame: 

df <- data.frame(read_excel(path = path_vector[1], col_names = TRUE,
                 skip = 13,col_types=mytypes), stringsAsFactors = FALSE)

Whenever I run this second line, I get the following error: 

"1: In read_fun(path = enc2native(normalizePath(path)), sheet_i = sheet,  ... :  
    Expecting numeric in L15 / R15C12: got a date"

Columns 12 and 17 are currency, so there will be positive and negative values. Since there can't be negative dates, these negative values are lost. In looking at the excel workbook that is being read, those two columns are set to custom numbers:

Screen grab from excel workbook:

img

I'm guessing this is where the error is coming from. Any ideas how to work around this? There are a few thousand files that have to be read, so it's not really feasible to go in and manually change the column types. Thanks!

Edit: so I am dumb, the fix was really simple. I just changed all of the mytypes entries to "text." Took care of everything. Thanks for the response!

SucculentOtter
  • 11
  • 2
  • 1
    It's really difficult to know without being able to see any of your data or run any of your code. [See here](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) on making an R question that folks can help with. – camille Jan 27 '20 at 18:53
  • I've never quite understood this, `read_excel` seems to ignore the `guess_max` argument, or at least is guessing before looking at `guess_max` number of rows. Have you tried reading everything in as text then using `type.convert` on all the columns? You might have to do some extra date conversions but at least you won't lose any data – rawr Jan 27 '20 at 18:56
  • What is the value in cell L15? – SCDCE Jan 27 '20 at 19:51
  • L15 has the $40.09 value – SucculentOtter Jan 27 '20 at 21:30
  • I don't see anything in the help page for read_excel that suggest it will be able to read currency values (with leading dollar-sign symbols and comma-separators). I think you will need to create a function that converts text to numeric. There are examples of this conversion in SO already. https://stackoverflow.com/questions/23158074/import-currency-values-in-csv-into-r – IRTFM Jan 27 '20 at 22:38
  • Also: https://stackoverflow.com/questions/41081252/using-regular-expression-in-string-replacement/41081351 – IRTFM Jan 27 '20 at 22:45

0 Answers0