CODEWITHSUNDEEP
rexcelr-faq
ntamjo achille
ntamjo achille

Reputation: 861

Export data from R to Excel

I am writing code to export database from R into Excel, I have been trying others code including:

write.table(ALBERTA1, "D:/ALBERTA1.txt", sep="\t")
write.csv(ALBERTA1,":\ALBERTA1.csv")
your_filename_in_R = read.csv("ALBERTA1.csv")
your_filename_in_R = read.csv("ALBERTA1.csv")
write.csv(df, file = "ALBERTA1.csv")
your_filename_in_R = read.csv("ALBERTA1.csv")
write.csv(ALBERTA1, "ALBERTA1.csv")
write.table(ALBERTA1, 'clipboard', sep='\t')
write.table(ALBERTA1,"ALBERTA1.txt")
write.table(as.matrix(ALBERTA2),"ALBERTA2.txt")
write.table(as.matrix(vecm.pred$fcst$Alberta_Females[,1]), "vecm.pred$fcst$Alberta_Females[,1].txt")
write.table(as.matrix(foo),"foo.txt")
write.xlsx(ALBERTA2, "/ALBERTA2.xlsx")
write.table(ALBERTA1, "D:/ALBERTA1.txt", sep="\t").

Other users of this forum advised me this:

write.csv2(ALBERTA1, "ALBERTA1.csv")
write.table(kt, "D:/kt.txt", sep="\t", row.names=FALSE)

enter image description here

You can see on the pictures the outcome I have got from the code above. But this numbers can't be used to make any further operations such as addition with other matrices.

Has someone experienced this kind of problems?

Upvotes: 68

Views: 437196

Answers (9)

Jaap
Jaap

Reputation: 83245

Another option is the openxlsx-package (CRAN, website, GitHub). It doesn't depend on and can read, edit and write Excel-files. From the description from the package:

openxlsx simplifies the the process of writing and styling Excel xlsx files from R and removes the dependency on Java

Example usage:

library(openxlsx)

# read data from an Excel file or Workbook object into a data.frame
df <- read.xlsx('name-of-your-excel-file.xlsx')

# for writing a data.frame or list of data.frames to an xlsx file
write.xlsx(df, 'name-of-your-excel-file.xlsx')

Besides these two basic functions, the openxlsx-package has a host of other functions for manipulating Excel-files.

For example, with the writeDataTable-function you can create formatted tables in an Excel-file.

Upvotes: 103

robertspierre
robertspierre

Reputation: 4371

A package not mentioned in the previous answers is openxlsx2 (GitHub,website). Being based on openxlsx, it doesn't require Java.

Here is an example on how to save a dataframe to file:

openxlsx2::write_xlsx(starwars, "starwars.xlsx")

Upvotes: 0

lukitamodric
lukitamodric

Reputation: 37

If I might offer an alternative, you could also save your dataframe in a regular csv file, and then use the "get data" function within Excel to import the dataframe. This worked like a charm for me, and you need not bother with any excel packages in R.

Upvotes: 0

andschar
andschar

Reputation: 3973

One could also use the readODS package. Granted it doesn't produce an .xlsx, but Excel can read Open Document Spreadsheet (ODS) / LibreOffice files too.

require(readODS)

tmp = file.path(tempdir(), 'iris.ods')
write_ods(iris, tmp)

Upvotes: 2

Primesty
Primesty

Reputation: 115

Here is a way to write data from a dataframe into an excel file by different IDs and into different tabs (sheets) by another ID associated to the first level id. Imagine you have a dataframe that has email_address as one column for a number of different users, but each email has a number of 'sub-ids' that have all the data.

data <- tibble(id = c(1,2,3,4,5,6,7,8,9), email_address = c(rep('[email protected]',3), rep('[email protected]', 3), rep('[email protected]', 3)))

So ids 1,2,3 would be associated with [email protected]. The following code splits the data by email and then puts 1,2,3 into different tabs. The important thing is to set append = True when writing the .xlsx file.


temp_dir <- tempdir()

for(i in unique(data$email_address)){
    
  data %>% 
    filter(email_address == i) %>% 
    arrange(id) -> subset_data
  
  for(j in unique(subset_data$id)){
    write.xlsx(subset_data %>% filter(id == j), 
      file = str_c(temp_dir,"/your_filename_", str_extract(i, pattern = "\\b[A-Za-z0- 
       9._%+-]+"),'_', Sys.Date(), '.xlsx'), 
      sheetName = as.character(j), 
      append = TRUE)}
 
  }

The regex gets the name from the email address and puts it into the file-name.

Hope somebody finds this useful. I'm sure there's more elegant ways of doing this but it works.

Btw, here is a way to then send these individual files to the various email addresses in the data.frame. Code goes into second loop [j]

  send.mail(from = "[email protected]",
            to = i,
          subject = paste("Your report for", str_extract(i, pattern = "\\b[A-Za-z0-9._%+-]+"), 'on', Sys.Date()),
          body = "Your email body",
          authenticate = TRUE,
          smtp = list(host.name = "XXX", port = XXX,
                      user.name = Sys.getenv("XXX"), passwd = Sys.getenv("XXX")),
          attach.files = str_c(temp_dir, "/your_filename_", str_extract(i, pattern = "\\b[A-Za-z0-9._%+-]+"),'_', Sys.Date(), '.xlsx'))

Upvotes: -3

Christopher Louden
Christopher Louden

Reputation: 7592

The WriteXLS function from the WriteXLS package can write data to Excel.

Alternatively, write.xlsx from the xlsx package will also work.

Upvotes: 11

lukeA
lukeA

Reputation: 54237

writexl, without Java requirement: 

# install.packages("writexl")
library(writexl)
tempfile <- write_xlsx(iris)

Upvotes: 30

Fabiola
Fabiola

Reputation: 17

I have been trying out the different packages including the function:

install.packages ("prettyR") 

library (prettyR)

delimit.table (Corrvar,"Name the csv.csv") ## Corrvar is a name of an object from an output I had on scaled variables to run a regression.

However I tried this same code for an output from another analysis (occupancy models model selection output) and it did not work. And after many attempts and exploration I:

  • copied the output from R (Ctrl+c)
  • in Excel sheet I pasted it (Ctrl+V)
  • Select the first column where the data is

  • In the "Data" vignette, click on "Text to column"

  • Select Delimited option, click next

  • Tick space box in "Separator", click next

  • Click Finalize (End)

Your output now should be in a form you can manipulate easy in excel. So perhaps not the fanciest option but it does the trick if you just want to explore your data in another way.

PS. If the labels in excel are not the exact one it is because Im translating the lables from my spanish excel.

Upvotes: -7

d2a2d
d2a2d

Reputation: 1196

Recently used xlsx package, works well.

library(xlsx)
write.xlsx(x, file, sheetName="Sheet1")

where x is a data.frame

Upvotes: 42

Related Questions