Hi! I am Mike Marin and in this video we’ll talk about how to import data from Excel into R. the steps outlined in this video should work with most other spreadsheet applications but since Excel is fairly commonly used we will focus our discussion on this Our two main options will be to save the data file as a comma-separated value or “.csv” file or as a tab-delimited text file with the “.txt” extension. Saving it as a csv file is probably easier and the better way to go! let’s go ahead and save this file on the desktop as a csv file: to do so under the “File menu” we can go to “Save As…” we’re going to save it on our desktop and we can save this as a “comma-separated” value or csv file. I’m also gonna add the CSV to the end of the file name just so it’s easily identifiable we can see the ExceldataCSV file saved here on the desktop you’ll notice if we double click on this file to open it by default it will open in Excel let’s go ahead and close this.

You’ll notice if we right click on this file we can “Open With…” and open it in any text editor that we like this here is what the csv file looks like in a text editor. okay so let’s go ahead and import this data into R. so I’ve gone ahead and opened up RStudio and R. the first option for importing data is to use the “read.csv” command. you can access the Help menu by typing help and in brackets the name of the command you will like help for or by placing a question mark (?) in front of the command name; let’s go ahead and import this data into R and save it in an object called “data1”. To do so we will use the “read.csv” command. the first argument to specify is the “file”. normally R would like us to specify the path to find the file here but a handy command to know in R is the “file.choose” command. rather than specifying the path to find this file we can use the “file.choose” command and this will result in a menu popping up allowing us to select the data file directly.

The next argument that we should worry about is the “header” argument. we can set this equal to TRUE using capital letters to let R know that the first row of our dataset are variable names or headers. if the first row does not contain variable names we would set the sequel to FALSE it’s also worth noting that we only need to provide a capital T and R will know this is TRUE. we can then submit this command by hitting the “enter” or “return” key on our keyboard. we can now go and find this file that we saved on the desktop : our “ExceldataCSV.CSV” select this file and now if we type in the object’s name “data1” we can see the entire dataset return to us. it looks like R has imported this dataset correctly we can also see in the workspace this object data1 which has 10 observations on 6 variables we can also import this data using the more generic “read.table” command.

Lets demonstrate the use of this command and import the data into R and save it in an object called “data2” here we will use the “read.table” command again we will use the “file.choose” command we will set the “header” argument equal to TRUE and here we also need to use the “sep” argument letting R know how the data values are separated in quotations we will place a comma letting R know these are comma separated values we can then submit this command, go on our desktop select the “ExceldataCSV.CSV” file and when we type in the name of this object again we can see the data has been imported correctly. now let’s go back to our excel file and look at importing data saved as a tab-delimited text file once again you can see our Excel data file we can go ahead and save this file as a tab-delimited text file here I will select the tab-delimited file with a .txt extension and we will also add the word TAB to the end of this file name, so we can be easily identify this is the tab-delimited file.

excel photo
Photo by Valeria95s

We can then save it and now we can see this tab-delimited text file saved on the desktop here you’ll notice if you double-click on this file to open it by default it will be opened in a text editor you can also notice these values are separated by tabs let’s go ahead and close this down. to open this file using Excel we can right click “open with” and select Microsoft Excel and now we’ll see our tab-delimited text file opened up in Excel now let’s go back into RSstudio and import this data file we can use the “read.delim” command in R to import a tab-delimited text file let’s go ahead and import this dataset using the “read.delim” command and we will save the data into an object called data3 we will use the “read.delim” command as before we use the “file.choose” command so that we can select our file from menu we can set the “header” argument equal to TRUE once we enter this command: we can go onto the desktop open up our tab-delimited text file.

Again if we enter the object name we can see the data has been imported correctly and finally as we saw before we could also use the more generic “read.table” command. let’s demonstrate this and save the data in an object called data4 we’ll use the “read.table” command. again “file.choose” so that we can select our file “header” equal to TRUE and finally we can set the “sep” argument equal to back-slash t (\t) letting R know this is a tab-delimited file we can go ahead and select the file we can enter the dataset’s name to view it and again the data looks as it should. the next video in the series talks a bit more about importing data and then how to get started working with the data in R once you’ve imported it. thanks for watching this video and make sure to check out my other instructional videos

As found on Youtube

More R posts

Get more stuff like this

Subscribe to our mailing list and get interesting stuff and updates to your email inbox.