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. Kyong [[alternative HTML version deleted]]
On Wed, 2004-07-07 at 13:21, Park, Kyong H Mr. RDECOM wrote:> 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. > > KyongYep. 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 file. 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. HTH, Marc Schwartz
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. On Wed, 2004-07-07 at 19:21, Park, Kyong H Mr. RDECOM wrote:> 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. > > Kyong > > > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help at 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 >
On Wed, 2004-07-07 at 13:44, Marc Schwartz wrote:> On Wed, 2004-07-07 at 13:21, Park, Kyong H Mr. RDECOM wrote: > > 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. > > > > KyongOne other thing: The default delimiting characters in read.csv() and read.delim() are NOT the same. The former uses a comma and the latter a TAB character. If you did not change the defaults in Excel when you created your CSV file, that would account for the difference behaviors upon import. Be sure that the delimiting character in the R function you use properly corresponds to the actual delimiting character in your CSV file. Marc
Am Mittwoch, 7. Juli 2004 20:21 schrieb Park, Kyong H Mr. RDECOM:> 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.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 hth, Richard -- Richard M??ller - Am Spring 9 - D-58802 Balve-Eisborn r.mueller at oeko-sorpe.de - www.oeko-sorpe.de
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. Bye Vito Ricci Park, Kyong H Mr. RDECOM kyong.ho.park at us.army.mil Wed Jul 7 20:21:42 CEST 2004 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. Kyong ====Diventare costruttori di soluzioni Visitate il portale http://www.modugno.it/ e in particolare la sezione su Palese http://www.modugno.it/archivio/cat_palese.shtml
Hi, I'm trying to use read.xls() function to import Excel data, but I've this error: Error in system(cmd, intern = !verbose) : perl not found R is running under Win2000; This function works translating the named Microsoft Excel file into a temporary .csv file, using Greg Warnes' xls2csv perl script (installed as part of the gregmisc package). What happens? Cordially Vito Ricci ====Diventare costruttori di soluzioni Visitate il portale http://www.modugno.it/ e in particolare la sezione su Palese http://www.modugno.it/archivio/cat_palese.shtml
Hi Vito: You may consider obtaining ActivePerl from the following site and install it in Windows: http://www.activestate.com/Products/ActivePerl/ and then retry. HTH, Arin On Thu, 08 Jul 2004 Vito Ricci wrote :>Hi, >I'm trying to use read.xls() function to import Excel >data, but I've this error: > >Error in system(cmd, intern = !verbose) : perl not >found > >R is running under Win2000; > >This function works translating the named Microsoft >Excel file into a temporary .csv file, using Greg >Warnes' xls2csv perl script (installed as part of the >gregmisc package). > >What happens? >Cordially >Vito Ricci > >====>Diventare costruttori di soluzioni > >Visitate il portale http://www.modugno.it/ >e in particolare la sezione su Palese http://www.modugno.it/archivio/cat_palese.shtml > >______________________________________________ >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.htmlArindam Basu MD MPH DBI Assistant Director Fogarty International Program on Environmental Health in India IPGMER 244 AJC Bose Road, Kolkata 700027 India [[alternative HTML version deleted]]