Unable to import data from a sample Excel file

Question

I have an Excel file consisting a sample data of 5 rows. I have given the following code to import it to R.

> testdata <-read.table(file.choose(),header=TRUE)

I am receiving a warning message as follows.

Warning message:
In read.table(file.choose(), header = TRUE) :
  incomplete final line found by readTableHeader on

I have ignored the warning message and have continued to view my data but found that there were no rows selected. Below is what I am arriving at

> testdata
[1] PK...
<0 rows> (or 0-length row.names)

As I am a beginner in R I am unable to trace the error. Any help regarding the warning and error would be appreciated. Please help.

Answer 1

That's because you're trying to import a regular Excel workbook/worksheet (extension .xls or .xlsx). These are binary files; R doesn't know what to do with them.

To import your data into R, you have a couple of options:

1. Use a package like RODBC or xlsx, which can import the file into a data frame. This can be a bit complex for a beginner.

2. Save your worksheet as a .csv file. These are plain text files, which you can import with the function read.csv.

A few tips if you go with option 2:

1. clear all formatting from your cells before importing. If you have numbers with embedded dollar signs, percent signs, commas etc, R will treat your numbers as text and this can lead to much confusion.

2. Excel saves numbers to CSV files only with the visible precision and not the actual precision. So you want to make sure that all the decimal places needed are saved.

3. The exception is dates, for which you should retain your formatting. R will import them as factors, which you can then convert to R dates if necessary.

Answer 2

Standard R does not import Excel files, but you can save them in CSV format and then import them with read.csv.