Getting data from Excel to R

Exercise 3.3.

Some notes on importing data to supplement Chapter 3.

• Incomplete final line error on CSV import
• Edit file in text editor
• Send extra linefeed from R

Sometimes you can get an error message when trying to import a CSV file, most likely with the read.csv() or read.table() command. The “incomplete final line” error message arises when there is a “missing” return in the last data row of your CSV file. You need to add an extra line-feed at the end of the file.

There are two ways you can set about this:

• Open the file in a text editor and add an extra line. Then import the file to R.
• Send an additional line-feed directly to the file from R itself. Then import the file to R.

Whichever method you choose it is a good idea to make a backup of the file, just in case. If you open your “faulty” file in a spreadsheet it will look absolutely fine; it is only in a text editor or word processor that you can see the end-of-line characters (not all editors display hidden characters).

The OS-based method of fixing this problem is to open the file in any kind of text editor. WordPad is a good choice in Windows as it can handle line-breaks more effectively (Windows likes end-of-line characters to be CR & LF, Mac uses LF and Linux CR, WordPad displays all correctly, Word does not). Go to the bottom of the file and if you cannot get the cursor past the end of the final line there is a missing linefeed. Simply press the Enter key on the keyboard to add the final linefeed and save the file.

Now when you try to import the file you should not get the error.

You can send a linefeed to a file directly from R using the cat() command. Use a command like so:

cat("\n", file = file.choose(), append = TRUE)

The “\n” is a “newline” character (you do need the quotes). Note that you need to use append = TRUE otherwise you will overwrite the file with nothing except your newline character. If you are using Linux then you’ll need to specify the filename explicitly but in Windows or Mac the file.choose() part will allow you to choose the file.