Issue while read pivot sheet from excel into R

[jpappire1]

Hi All,

I am trying to read pivot sheet using R studio ,But am not getting proper out put and getting error .can some one please advise the solution .

Am using rpivottable package and the below r code to read sheet.

code:
Mapper_Data <- read_excel("Coupon Receivable 04022019.xlsx", sheet= "Mapper")

Error which I received:

Warning messages:
1: In read_fun(path = enc2native(normalizePath(path)), sheet_i = sheet, :
Expecting numeric in D4 / R4C4: got a date
2: In read_fun(path = enc2native(normalizePath(path)), sheet_i = sheet, :
Expecting numeric in E4 / R4C5: got a date

[jdlong]

I'm a little perplexed by your question. You say you're using rpivottable and you're tagged your question as dplyr... only your code example contains nothing from either package. Are you using readxl possibly for the read_excel function? Or are you getting that somewhere else?

If I stretch my imagination and assume you're using readxl, then why don't you share an example of your excel data here. I hate to sound sarcastic, but do you have a date in the middle of your numeric data, possibly? The error would lead one to think that might be the case. Have you checked?

[jpappire1]

Thanks for the reply jdlong, I am new to R .

Please find below sample data ,The file is having two headers and started from 3rd row in file and the date column is present in 3rd row and 4-Feb column is present in 4th row as well as filter condition using in 3rd row. pleas advise how can I achieve this

Sum of Mntry Amnt			Date
Exhnge Cde	Secty Cde	Secty Name	1-Jan	4-Mar
AUOTC	110425	CBH RESOURCES LTD CB 7.25% 05/	(14,237.020)	(14,285.200)

[jdlong]

you're going to need to give read_excel more info in order to read your data in.

you can look at the documentation for read_excel by typing ?read_excel into your R console. You'll see the syntax for the function is:

read_excel(path, sheet = 1, col_names = TRUE, col_types = NULL, na = "", skip = 0)

so a data.frame in R can't have two header rows, so you should skip that first one. If that's row 1, then you could do:

read_excel("Coupon Receivable 04022019.xlsx", sheet= "Mapper", skip = 1)

see if that helps.

[jpappire1]

So I can read pivot sheet as well using read_excel ?

[jdlong]

what do you mean by "read pivot sheets"?

Are you asking if you can read values from a pivot table in a sheet in Excel? Yes. But it will not recalculate. readxl will just read the values into R.

[jpappire1]

The values are not coming proper format

[jdlong]

Ok.

If you want help with that you'll need to provide example of your data as it exists in the spreadsheet, the code you're using to pull the data, and how the data is showing up in R.