hi, i am using the RODBC package to read excel files using odbcConnectExcel and susequently sqlFetch to read the contents of the file. the file that i use is just a matrix of numbers thats all. no headers and column names. what happens is that the sqlFetch is not reading my first row of numbers. i have tried different combinations of colnames and rownames logical values but that first row is not being read into my data frame. i can manually set the first row to blanks in my worksheet, but since this is part of larger code where a user specifies a file to use this method is a little clumsy. i would be grateful if you help me get around this problem. regards, vivek
hi, the specifications for my application call that the input file be in excel only in the format i previously mentioned. now if the approach you mention has to be used then the part of converting has to be done automatically. this i found to be too complex. an alternative to that was to use MS access databases. but again it is the problem of automating the conversion that is proving difficult regards and best vivek On 6/16/05, ronggui <0034058 at fudan.edu.cn> wrote:> why not save the file as csv file(using the MS excel application) and use the read.csv function in foreign library? > > On Thu, 16 Jun 2005 15:51:22 +0530 > Vivek Subramanian <subramanian.vivek at gmail.com> wrote: > > > hi, > > > > i am using the RODBC package to read excel files using > > odbcConnectExcel and susequently sqlFetch to read the contents of the > > file. > > > > the file that i use is just a matrix of numbers thats all. no headers > > and column names. what happens is that the sqlFetch is not reading my > > first row of numbers. > > i have tried different combinations of colnames and rownames logical > > values but that first row is not being read into my data frame. > > > > i can manually set the first row to blanks in my worksheet, but since > > this is part of larger code where a user specifies a file to use this > > method is a little clumsy. > > > > i would be grateful if you help me get around this problem. > > > > regards, > > vivek > > > > ______________________________________________ > > R-help at stat.math.ethz.ch mailing list > > https://stat.ethz.ch/mailman/listinfo/r-help > > PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html >
You could use a VB macro in Excel to automate the data export in CSV
format, and it's not complex at all, for example:
Private Sub CommandButton1_Click()
Dim strB18 As String
strB18 = Me.Cells(18, 2)
'MsgBox "Export Folder = " & strB18
On Error GoTo ErrHandler
Sheets("Inputs").SaveAs FileName:= _
strB18 & "\Inputs.csv", FileFormat _
:=xlCSV, CreateBackup:=False
MsgBox "Data Exported"
Exit Sub
ErrHandler:
MsgBox Err.Description
End Sub
On the other hand, if the specifications call that the input file be
in Excel, then the specifications are _wrong_. See
http://www.burns-stat.com/pages/Tutor/spreadsheet_addiction.html
for details. Keep in mind that when exporting to CSV the numbers may
be written with a limited number of significant digits.
> -----Original Message-----
> From: Vivek Subramanian [mailto:subramanian.vivek at gmail.com]
> Sent: Thursday, June 16, 2005 7:01 AM
> To: ronggui; rhelp
> Subject: Re: [R] Excel files first row not being read
>
>
> hi,
>
> the specifications for my application call that the input file be in
> excel only in the format i previously mentioned.
> now if the approach you mention has to be used then the part of
> converting has to be done automatically. this i found to be too
> complex.
>
> an alternative to that was to use MS access databases. but again it is
> the problem of automating the conversion that is proving difficult
>
> regards and best
> vivek
>
> On 6/16/05, ronggui <0034058 at fudan.edu.cn> wrote:
> > why not save the file as csv file(using the MS excel
> application) and use the read.csv function in foreign library?
> >
> > On Thu, 16 Jun 2005 15:51:22 +0530
> > Vivek Subramanian <subramanian.vivek at gmail.com> wrote:
> >
> > > hi,
> > >
> > > i am using the RODBC package to read excel files using
> > > odbcConnectExcel and susequently sqlFetch to read the
> contents of the
> > > file.
> > >
> > > the file that i use is just a matrix of numbers thats
> all. no headers
> > > and column names. what happens is that the sqlFetch is
> not reading my
> > > first row of numbers.
> > > i have tried different combinations of colnames and
> rownames logical
> > > values but that first row is not being read into my data frame.
> > >
> > > i can manually set the first row to blanks in my
> worksheet, but since
> > > this is part of larger code where a user specifies a file
> to use this
> > > method is a little clumsy.
> > >
> > > i would be grateful if you help me get around this problem.
> > >
> > > regards,
> > > vivek
> > >
> > > ______________________________________________
> > > R-help at stat.math.ethz.ch mailing list
> > > https://stat.ethz.ch/mailman/listinfo/r-help
> > > PLEASE do read the posting guide!
> http://www.R-project.org/posting-guide.html
> >
>
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide!
> http://www.R-project.org/posting-guide.html
>
khobson@fd9ns01.okladot.state.ok.us
2005-Jun-16 18:44 UTC
[R] Excel files first row not being read
If you would post your R code and explain how a simple Excel matrix is set up, we might be able to help more. khobson at odot.org Kenneth Ray Hobson, P.E. Oklahoma DOT - QA & IAS Manager 200 N.E. 21st Street Oklahoma City, OK 73105-3204 (405) 522-4985, (405) 522-0552 fax