[R] Replies for Importing Excel File

From: Park, Kyong H Mr. RDECOM <kyong.ho.park_at_us.army.mil>
Date: Thu 08 Jul 2004 - 22:01:11 EST

I really appreciate all the helpful answers from Richard Müller, Marc Schwartz, Adaikalavan Ramasamy, Vito Ricci. I tried Marc's simple suggestion which was to copy only the data area and paste to a new excel sheet, and that solved my problem. I'll try other suggestions while I'm learning more about R. Unedited answers are provided below followed by my question. Thanks again.


From Richard Müller:

I import my OpenOffice calc files as follows (OOo or Excel won't make any difference, the csv-format is the same):

inp <- (scan(file, sep=";", dec=",", list(0,0), skip = 13, nlines = 58) x <- inp[[1]]; y <- inp [[2]]

sep=";": column separator ;
dec="," decimal separator ,
list(0,0): first two columns
skip: no of lines to skip (these lines contain comments etc.) nlines=58: 58 lines of values to plot

From Marc Schwartz

Yep. This is one of the behaviors that I had seen with Excel when I was
running Windows XP. Seemingly empty cells outside the data range would
get exported in the CSV file causing a data integrity problem.

It is one of the reasons that I installed OpenOffice under Windows and
used Calc to open the Excel files and then do the CSV exports before I
switched to Linux.... :-)

Depending upon the version of Excel you are using, you might try to
highlight and copy only the rectangular range of cells in the sheet that
actually have data to a new sheet and then export the new sheet to a CSV

Do not just click on the upper left hand corner of the sheet to
highlight the entire sheet to copy it. Only highlight the range of cells
you actually need for copying.

Another option is to use the read.xls() function in the 'gregmisc'
package on CRAN or install OpenOffice.

From Adaikalavan Ramasamy: Are all you data numerical or does it contain characters as well ? Check for the common culprits - the hash (#) which is the comment character in R, unbalanced single and double quotes and other suspicious characters around line 16 and in general. If that does not work, open in Excel and just delete all the unwanted columns (and rows) even if they appear empty. Then save as csv and try again. read.delim() will not work properly in your case because it recognises tab as separator.
From Vito Ricci: I use very much Excel in my job and often I've to read Excel data in R. Usually I save Excel file as a .txt file and then I read it in R with read.table(). I find really interesting the suggest of using the function read.xls() in the 'gregmisc' package. I work also with R in Excel sheets by R-Com/R-Interface (see: http://www.sciviews.org/_rgui/projects/RDcom.html) It has several limits, but for some analysis is helpfull.
My original question: Hello, R users, I am a very beginner of R and tried read.csv to import an excel file after saving an excel file as csv. But it added alternating rows of fictitious NA values after row number 16. When I applied read.delim, there were trailing several commas at the end of each row after row number 16 instead of NA values. Appreciate your help. [[alternative HTML version deleted]] ______________________________________________ R-help@stat.math.ethz.ch mailing list https://www.stat.math.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
Received on Thu Jul 08 22:09:50 2004

This archive was generated by hypermail 2.1.8 : Wed 03 Nov 2004 - 22:54:46 EST