无法从R中读取.xls [英] Unable to read in .xls from R

问题描述

• 文件:这是一个大约100行7列的文件.XLS扩展名(MS Excel 97-2003).
• 问题:无法通过 read.xlsx 或 read.xlsx2 读入带有R的文件.

• 用于尝试读取文件的代码:

• File: it is a file with about 100 rows and 7 columns. XLS extension (MS Excel 97-2003).
• Issue: unable to read in the file with R through read.xlsx or read.xlsx2.

• Code used to try to read the file:

library(xlsxjars)
library(rJava)
library(xlsx) 
excel <- read.xlsx("File.xls",sheetIndex=1,startRow=1,stringsAsFactor=F)

提示错误:

Error prompted:

.jcall中的错误("RJavaTools"，"Ljava/lang/object;"，"invokeMethod"，cl，:java.lang.IllegalArgumentException:您的InputStream既不是OLE2流，也不是OLE2流.OOXML流.

Error in .jcall ("RJavaTools","Ljava/lang/object;","invokeMethod",cl, : java.lang.IllegalArgumentException: Your InputStream was neither an OLE2 stream, nor an OOXML stream.

PD:出于记录目的，我确实尝试使用 read.csv 读取它，并且确实读取了它，但是由于它是一个Excel文件，并且没有用逗号或点分隔，因此R将其读取为如果所有内容都在1列中.也许任何人都可以建议一种通过 read.csv 读取它的方法?

PD: for the record, I did try to read it with read.csv and it does read it but since it's an Excel file and it's not separated by commas or dots, R reads it as if everything is in 1 column. Maybe anyone can suggest a way to read it through read.csv?

推荐答案

您所说的两件事使我认为您没有处理要处理的文件类型:

There are two things you have stated that make me think that you are not dealing with the type of file you think you are dealing with:

Your InputStream was neither an OLE2 stream, nor an OOXML stream"

我确实尝试使用read.csv()读取它，并且确实读取了它……"

"I did try to read it with read.csv() and it does read it ..."

如果它确实是MS Excel 97-2003本机格式的文件，则 read.csv 根本无法使用. read.xlsx 中的错误支持此功能.这似乎是一个文本文件，您的注释是，在 read.table 中使用 sep ="\ t" 可以正确读取以确认它.您所拥有的是一个制表符分隔的文本文件，恰好具有后缀 .xls .

If it really was a file in the MS Excel 97-2003 native format(s), read.csv would not have worked at all on it. The error from read.xlsx supports this. This appears to be a text file, and your comment that using sep="\t" in read.table allows it to be read correctly confirms it. What you have is a tab separated text file that happens to have the suffix .xls.

应注意，文件后缀不能保证该文件为所声称的格式.一个更好的指示符是在其上运行 file shell命令(如果该命令在您的操作系统上可用).GUI有时使用文件扩展名来确定使用哪个应用程序打开文件.由于Excel能够读取制表符分隔的文件(并且由于Excel可能未设置为 .tsv 文件的默认应用程序，而该文件可能是更传统的文件扩展名)，因此可以使用 .xls 扩展名，该文件(通常)将由Excel打开.

It should be noted that the suffix of a file is not a guarantee that the file is of the purported format. A much better indicators is running the file shell command on it (if that command is available on your operating system). File extensions are sometimes used by GUIs to determine which application to use to open the file with. As Excel is capable of reading a tab delimited file (and because Excel prorbably is not set to be the default application for a .tsv file, which would be the more traditional file extension), by using a .xls extension, the file will (generally) be opened by Excel.

总而言之，仅仅因为文件名以 .xls 结尾就并不意味着它确实是Excel本机格式文件.

In summary, just because the filename ends in .xls does not mean it really is an Excel native format file.

这篇关于无法从R中读取.xls的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！