How to read .Excel files into R using openxlsx package?
1
0
Entering edit mode
4.0 years ago

Good morning

I am trying read excel file into R using openxlsx package with read.xlsx function, please find links below to my file and code

Please find example file here https://www.dropbox.com/s/8arrp4lt2zhg6c6/example.xlsx?dl=0

and my code here

#install.packages("openxlsx")
library("openxlsx")

a<-read.xlsx('reddy.xlsx', sheet = 1, startRow = 1, colNames = FALSE,
rowNames = FALSE, detectDates = FALSE, skipEmptyRows = TRUE,
skipEmptyCols = TRUE, rows = NULL, cols = NULL, check.names = FALSE,
namedRegion = NULL, na.strings = "NA", fillMergedCells = FALSE)
dim(a)
f<-t(a)
dim(f)


i know it is basic question but i am unable to solve it and i do not where i am doing mistake in this to read my file correctly. any help in this regard is highly appreciated and thanks in advance. Regards

R Excel import • 9.6k views
0
Entering edit mode

Put the three tables in three different sheets and import them separately and remove the extra text form your excel file like "Data I have", "but I am getting like this".

0
Entering edit mode

Dear Arup thanks lot for reply. I do not want to upload all three sheets. i provided them just for understanding i want to upload data i have to data i wish to thats all please find my data as per your suggestion here https://www.dropbox.com/s/lr0q9j5gnu0otp2/example.xlsx?dl=0 Thanks in advance

0
Entering edit mode

That does not feel like a bioinformatics question to me! StackOverflow is better suited for questions like this.

1
Entering edit mode
4.0 years ago

like this?

> library(readxl)
# A tibble: 9 x 6
INDIVIDUAL IND1  IND2  IND3  IND4  IND5
<chr>      <chr> <chr> <chr> <chr> <chr>
1 M1         TT    TT    TT    TT    TT
2 M2         CC    CC    GG    CC    CC
3 M3         GG    GG    AA    AA    GG
4 M4         GG    GG    GG    GG    GG
5 M5         GG    GG    GG    GG    GG
6 M6         CC    CC    TT    CC    CC
7 M7         CC    AA    CC    CC    CC
8 M8         TT    TT    CC    TT    TT
9 M9         AA    AA    AA    AA    AA

0
Entering edit mode

Dear cpad0112 Thanks for your reply, yes like that but i do not want these <chr> things and numbers from 1 to 9 rest is like expected. Please suggest me how to remove them (<chr> and numbers) Thanks in advance

0
Entering edit mode

They are part of print tibble method. They are not there, <chr>... row just shows column class. and numbers on the left 1:9 are just row names. They are not part of your data.

0
Entering edit mode

Question is about "openxlsx" package, maybe update the post?

0
Entering edit mode
> library(openxlsx)
INDIVIDUAL IND1 IND2 IND3 IND4 IND5
1         M1   TT   TT   TT   TT   TT
2         M2   CC   CC   GG   CC   CC
3         M3   GG   GG   AA   AA   GG
4         M4   GG   GG   GG   GG   GG
5         M5   GG   GG   GG   GG   GG
6         M6   CC   CC   TT   CC   CC
7         M7   CC   AA   CC   CC   CC
8         M8   TT   TT   CC   TT   TT
9         M9   AA   AA   AA   AA   AA

0
Entering edit mode

Thanks lot zx8754 and cpad0112 for your help and now i am able to read that as i wish thanks lot again

0
Entering edit mode

Hello @blacktomato27,

If an answer was helpful, you should upvote it; if the answer resolved your question, you should mark it as accepted. You can accept more than one if they work.