Exporting Data from R: A Guide to CSV, TXT, Excel, SPSS, Stata, SAS, and HTML Files

  • Post author:
  • Post category:R Programming
  • Post last modified:November 5, 2024
  • Reading time:19 mins read
You are currently viewing Exporting Data from R: A Guide to CSV, TXT, Excel, SPSS, Stata, SAS, and HTML Files

Exporting or writing data from R to various formats like CSV, TXT, Excel, SPSS, Stata, SAS, and HTML. Each format serves unique purposes and benefits, from quick sharing of plain-text data to building high-quality reports for business or academic use. R programming provides various functions and packages to facilitate data export in each of these formats. In this article, I will explain how to export data in different formats using the following functions of R.

Advertisements
  • write.csv() for CSV files,
  • write.table() for text files,
  • write_xlsx() (from the writexl package) for Excel files,
  • write.foreign() for SPSS, Stata, and SAS formats.

Key Points-

  • R allows exporting data to various formats such as CSV, TXT, Excel, SPSS, Stata, SAS, and HTML, catering to different analytical needs.
  • Functions like write.csv() and write.table() are part of base R and provide basic functionality for exporting data.
  • The readr and openxlsx packages improve CSV and Excel file exporting capabilities, allowing for faster performance and better formatting options.
  • The xtable package facilitates exporting R data frames to HTML format, which is useful for web presentations and reports.
  • The foreign package enables exporting to popular statistical software formats, making it easy to transition data between R and software like SPSS, Stata, and SAS.
  • Many export functions offer customizable parameters, such as delimiters and sheet names, providing flexibility in how data is saved.
  • It’s important to ensure that the file paths specified in export functions are correct to avoid errors during the export process.

What is Exporting Data in R

Exporting data in R refers to the process of saving or writing data from an R session to an external file format. This allows you to share, analyze, or present the data in various formats outside of R, such as CSV, TXT, Excel, SPSS, Stata, SAS, or HTML. Exporting is essential for transferring data to other software or platforms and makes the results of your analysis accessible for broader use.

Export to CSV File from R

The base R write.csv() function is used to export the data frame to CSV format, which is highly compatible with most data analysis tools and spreadsheet software. By applying this function to a data frame and specifying the file path (the desired location for the file), it saves the data, separating each row with a comma (,).


# Write CSV file from R using write.csv()

# Create DataFrame
df <- data.frame(
  id = c(10,11,12,13),
  name = c('sai','ram','deepika','sahithi'),
  gender = c('M','M',NA,'F'),
  dob = as.Date(c('1990-10-02','1981-3-24','1987-6-14','1985-8-16')),
  state = c('CA','NY',NA,NA)
)
df

write.csv(df,file = "C:/data/dataframe.csv")

Yields below output.

Export Data from R

Export to CSV using readr Package

For faster CSV writing, you can use write_csv() from the readr package. The readr package in R is a highly efficient tool designed for quick and easy reading and writing of rectangular data. It offers functions to import data from multiple file formats, including CSV, TSV, and other delimited files, along with optimized functions for exporting data.

These functions are optimized for performance, making data import/export faster than base R functions, especially with large files.

Let’s install and load the reader package.


# Installing
install.packages("readr")
# Loading
library(readr)

Once the readr package is loaded, you can use the write_csv() function to quickly export an R data frame to a CSV file.


# Write CSV file from r using r package

library("readr")
# Write data to a csv file
write_csv(df, "C:/data/dataframe1.csv")

Yields below output.

Export Data from R

Export to TXT File from R

Alternatively, you can use the base R write.table() function to export a data frame or a matrix to a TXT file. TXT files are versatile, enabling custom delimiters, making them an excellent option when you need more flexibility than a CSV format provides. This format is ideal for exporting plain text data.

Following is the syntax of write.table() function.


# Sytax of write.table()
write.table(x, file, append = FALSE, sep = " ", dec = ".",
            row.names = TRUE, col.names = TRUE)

Pass the specified data frame, along with the file path and specified delimiter, to this function to export the R data as a text file.


# Write txt file from R using write.table()
write.table(df,file = "C:/data/dataframe.txt", sep ="\t")

Yields below output.

Export Data from R

Export Data to TXT File from R using readr Package

You can also use the readr package to write text files with write_tsv() for tab-separated values.


# Load readr package
library(readr)
# Write data to a txt file
write_tsv(df, path = "C:/data/dataframe1.txt")

Yields below output.

Export Data from R

Related: You can also write multiple lines from R to text file.

Export Data to Excel Files

You can use the write.xlsx() of openxlsx package to export data to an Excel file from R with custom sheet names and formatting, making it easy to share data within business and academic settings. Excel files (.xlsx) are widely used for organizing and presenting data in a spreadsheet format.

Installing and loading the openxlsx package.


# Installing and loading xlsx package
install.packages("openxlsx")
library("openxlsx")

Let’s pass the data frame or data into the this function to export the in the form of spreadsheet.


# Export the data to excel file
library(openxlsx)
write.xlsx(df, "C:/data/dataframe.xlsx", sheetName = "emp_details", overwrite = TRUE)

Yields below output.

export data from R

Export Data to HTML Using xtable

If you want to display data in HTML format, the xtable package allows you to convert R data frames to HTML tables, which can be embedded in web reports.

Let’s install and load the Xtable package.


# Install and load the Xtable package
install.packages("xtable")
library(xtable)

Let’s use the Xtable package to convert the R data frame to an HTML table.


library(xtable)
# Create an xtable and save to HTML
html_table <- xtable(df)
print(html_table, type = "html", file = "C:/data/dataframe.html")

Yields below output.

Export Data from R

Export Data from R to Various Software

The foreign package allows you to export R data to various software formats, including:

  • SPSS
  • Stata
  • SAS

 Export Data to SPSS

To export data to SPSS, you can use the write.foreign() function from the foreign package, which creates a data file and an SPSS syntax file. SPSS is a statistical software package frequently used in social sciences.

Install and load foreign packages.


# Installing and loading foreign package
install.packages("foreign") 
library(foreign)

Use the write.foreign() function from the foreign package to create the data file (mydata.txt) and SPSS syntax file (mydata.sps) in your working directory.


# Write data to spss
library(foreign) 
write.foreign(df, "C:/data/dataframe.txt", "C:/data/dataframe.sps", package="SPSS")

Yields below output.

Export Data from R

Export data to Stata

Use write.dta() function from the foreign package to export data to Stata format, compatible with .dta files. Stata files are popular in various scientific and academic fields. Exporting data to .dta format ensures compatibility with Stata, allowing researchers to perform rigorous statistical analysis.


# # Write data to stata
library(foreign) 
write.dta(df, "C:/data/dataframe.dta")

Yields below output.

Export Data from R

Export data to SAS

You can export data to SAS format using write.foreign() of the foreign package. Like with SPSS, this creates both a data file and a SAS script file for easy data import in SAS. SAS is a high-level data analysis software used in finance, healthcare, and other data-centric fields. 


# Write data to sAs
library(foreign) 
write.foreign(df, "C:/data/dataframe.txt", "C:/data/dataframe.sas", package="SAS")

Yields below output.

Export Data from R

Frequently Asked Questions of Export Data from R

What is the difference between write.csv() and write.table() ?

write.csv() is a specialized version of write.table() that defaults to comma separators and includes options for handling row names. In contrast, write.table() is more flexible, allowing for different delimiters.

How can I export data to Excel with multiple sheets?

You can use the openxlsx package’s write.xlsx() function to create an Excel file with multiple sheets by specifying different sheet names for each data frame.

What is the purpose of the write.foreign() function?

The write.foreign() function facilitates exporting data to formats compatible with statistical software like SPSS, Stata, and SAS by generating both a data file and a syntax file.

How can I customize the delimiter when exporting to a TXT file?

When using write.table(), you can specify a custom delimiter using the sep argument to separate values in the TXT file.

What is the performance difference between base R functions and those from the readr package?

Functions from the readr package, like write_csv(), are optimized for performance, providing faster reading and writing capabilities, especially for large datasets compared to base R functions.

Conclusion

In this article, I have explained various export techniques across multiple platforms and formats in R to be compatible everywhere with ultimate accessibility. Suppose your goal is to create a simple CSV for analysis, prepare an Excel file to upload during a presentation, or format data before use in statistical software. In that case, these techniques will get about what you need from the R environment.

Happy Learning!!

Tags: