Your error message tells me that you have different numbers of fields
in different lines. You say you, "copied and pasted the excel data into
a txt file". I usually copy what I want into a clean sheet then File -
Save, then File -"Save As" -"Save as type" = "CSV (Comma delimited)
(*.csv)" or "Text (Tab delimited) (*.txt)". Excel will ask if I'm sure
a couple of times, and I say yes. If that's what you've done and still
have a problem, then I have other tools:

First, I'll assign the file name to something like "File". Then,
'readLines(File, n=9)' tells me if the file starts as I think it does.
If I've got extra headers, it will tell me that.

Then, I do something like the following:

n.flds <- count.fields(File, sep="\t")
plot(n.flds)
sd(n.flds)

Then I play with the arguments to "count.fields" until 'sd(n.flds)'
is 0. Then I use "read.table" with arguments as I used to get
everything right in 'count.fields'. If I can't get sd(n.flds) to 0, you
can try read.table with 'fill=TRUE'. However, when you do that, you
need to check to make sure all the columns line up correctly with the
shorter lines.

Also, this issue has been discussed many times. 'RSiteSearch("read
excel")' just produced 1196 hits for me. If the above doesn't work, you
might try skimming a few from that list.

hope this helps.
spencer graves

Ling Jin wrote:

Hi all,

Does anybody know the easiest way to import excel data into R? I copied
and pasted the excel data into a txt file, and tried read.table, but R
reported that

Error in read.table("data_support.txt", sep = " ", header = T) :
more columns than column names

Thanks!

Ling

R-help (AT) stat (DOT) math.ethz.ch mailing list

PLEASE do read the posting guide!

Ling,

You might take a look at the function read.xls() in gdata library.

HTH.

6/22/05, Ling Jin <ljin (AT) lbl (DOT) govwrote:
Hi all,

Does anybody know the easiest way to import excel data into R? I copied
and pasted the excel data into a txt file, and tried read.table, but R
reported that

Error in read.table("data_support.txt", sep = " ", header = T) :
more columns than column names

Thanks!

Ling

R-help (AT) stat (DOT) math.ethz.ch mailing list

PLEASE do read the posting guide!

Ling,

If any column has text with spaces between words, this will lead
to the "more columns " problem.
Delete the spaces and try again.

e.g., if the Excel file is
Var1Var2Var3
text12
more text34
yet more56
and more78
blahblah910

a Mac, this will lead to the error message
"Error in scan(file = file, what = what, sep = sep, quote = quote,
dec = dec, :
line 1 did not have 4 elements"
(which I believe is the equivalent message to what you are getting on a PC)

But, if your remove the blanks in column 1, this reads as

x <- read.table("test.txt",header=T)
x
Var1 Var2 Var3
1 text 1 2
2 moretext 3 4
3 yetmore 5 6
4 andmore 7 8
5 blahblah 9 10

with no error message.

Alternatively, for small files, if using a PC try copying the Excel
spreadsheet to your clipboard and

x <- read.table(file("clipboard"), header = TRUE) or, if using a Mac

x <- read.table(pipe("pbpaste"), header = TRUE)

Bill

At 8:38 PM -0400 6/22/05, Wensui Liu wrote:
>Ling,
>
>You might take a look at the function read.xls() in gdata library.
>
>HTH.
>
>
6/22/05, Ling Jin <ljin (AT) lbl (DOT) govwrote:
>Hi all,
>>
>Does anybody know the easiest way to import excel data into R? I copied
>and pasted the excel data into a txt file, and tried read.table, but R
>reported that
>>
>Error in read.table("data_support.txt", sep = " ", header = T) :
>more columns than column names
>>
>Thanks!
>>
Ling
>>

William Revelle <lists (AT) revelle (DOT) netwrites:

Ling,

If any column has text with spaces between words, this will lead
to the "more columns " problem.
Delete the spaces and try again.

e.g., if the Excel file is
Var1Var2Var3
text12
more text34
yet more56
and more78
blahblah910

a Mac, this will lead to the error message
"Error in scan(file = file, what = what, sep = sep, quote = quote,
dec = dec, :
line 1 did not have 4 elements"
(which I believe is the equivalent message to what you are getting on a PC)

But, if your remove the blanks in column 1, this reads as

x <- read.table("test.txt",header=T)
x
Var1 Var2 Var3
1 text 1 2
2 moretext 3 4
3 yetmore 5 6
4 andmore 7 8
5 blahblah 9 10

with no error message.

Alternatively, for small files, if using a PC try copying the Excel
spreadsheet to your clipboard and

x <- read.table(file("clipboard"), header = TRUE) or, if using a Mac

x <- read.table(pipe("pbpaste"), header = TRUE)

PLEASE! There are functions read.csv(), and read.delim() specifically
for the purpose of reading exported files. They have the options set
exactly to handle issues of missing fields at end of line and embedded
blanks. Do use them. It's all on the help page for read.table

(read.csv2, read.delim2 in locales that use comma as decimal point)

'RSiteSearch' is an R command new with R 2.0.0 or 2.1.0, I believe.
It essentially passes the argument string to "www.r-project.org" -
Search -"R site search". Consequently, it requires internet access to
work. When I have an R (or S-Plus) question for which I do not already
know where to find the answer, "R site search" has been my primary
search tool for some time.

spencer graves

Ling Jin wrote:
Could you be more specific about RSiteSearch("read excel")? I think it
must be useful.
>

Your error message tells me that you have different numbers of fields
in different lines. You say you, "copied and pasted the excel data into
a txt file". I usually copy what I want into a clean sheet then File ->
Save, then File -"Save As" -"Save as type" = "CSV (Comma delimited)
(*.csv)" or "Text (Tab delimited) (*.txt)". Excel will ask if I'm sure
a couple of times, and I say yes. If that's what you've done and still
have a problem, then I have other tools:

First, I'll assign the file name to something like "File". Then,
'readLines(File, n=9)' tells me if the file starts as I think it does.
If I've got extra headers, it will tell me that.

Then, I do something like the following:

n.flds <- count.fields(File, sep="\t")
plot(n.flds)
sd(n.flds)

Then I play with the arguments to "count.fields" until 'sd(n.flds)'
is 0. Then I use "read.table" with arguments as I used to get
everything right in 'count.fields'. If I can't get sd(n.flds) to 0, you
can try read.table with 'fill=TRUE'. However, when you do that, you
need to check to make sure all the columns line up correctly with the
shorter lines.

Also, this issue has been discussed many times. 'RSiteSearch("read
excel")' just produced 1196 hits for me. If the above doesn't work, you
might try skimming a few from that list.

hope this helps.
spencer graves

Ling Jin wrote:

Hi all,

Does anybody know the easiest way to import excel data into R? I copied
and pasted the excel data into a txt file, and tried read.table, but R
reported that

Error in read.table("data_support.txt", sep = " ", header = T) :
more columns than column names

Thanks!

Ling

R-help (AT) stat (DOT) math.ethz.ch mailing list

PLEASE do read the posting guide!