I tried creating a .xlsx file using odbcConnectExcel2007 and adding a
worksheet with sqlSave. This seems to work, I am even able to query the
worksheet, but when I try opening the file in Excel I get the following
message: "Excel cannot open the file 'test.xlx' because the file
format or
file extension is not valid. Verify that the file has not been corrupted and
that the file extension matches the format of the file." Is this a known
issue? Or just user error? The RODBC manual seemed to indicate that sqlSave
worked fine with Excel, however it did not mention Excel 2007.
I am running Excel 2007 and R 2.12.1 on Windows XP. Below is my example code.
$ library(RODBC)
$
$ # This doesn't work
$ # Connect to an previously non-existent Excel file
$ out <- odbcConnectExcel2007("test.xlsx", readOnly=FALSE)
$ test <- data.frame(x=1:10, y=rnorm(10))
$ sqlSave(out, test)
$ sqlTables(out)
TABLE_CAT TABLE_SCHEM
TABLE_NAME TABLE_TYPE REMARKS
1 C:\\Documents and Settings\\G69974\\My Documents\\test.xlsx <NA>
test$ SYSTEM TABLE <NA>
2 C:\\Documents and Settings\\G69974\\My Documents\\test.xlsx
<NA> test TABLE <NA>
$ sqlFetch(out, "test")
x y
1 1 0.5832882
2 2 0.4387569
3 3 -0.6444048
4 4 -1.0013450
5 5 1.0324718
6 6 -0.7844128
7 7 -1.6789266
8 8 0.1402672
9 9 0.8650061
10 10 -0.0420201
$ close(out)
$ # Opening test.xlsx now fails
$
$ # This works
$ out <- odbcConnectExcel("test.xls", readOnly=FALSE)
$ test <- data.frame(x=1:10, y=rnorm(10))
$ sqlSave(out, test)
$ sqlTables(out)
TABLE_CAT TABLE_SCHEM
TABLE_NAME TABLE_TYPE REMARKS
1 C:\\Documents and Settings\\G69974\\My Documents\\test <NA>
test$ SYSTEM TABLE <NA>
2 C:\\Documents and Settings\\G69974\\My Documents\\test <NA>
test TABLE <NA>
$ sqlFetch(out, "test")
x y
1 1 0.5955787
2 2 1.0517528
3 3 0.3884892
4 4 -2.1408813
5 5 -0.7081686
6 6 0.1511828
7 7 2.0560555
8 8 -0.5801912
9 9 -0.6988058
10 10 -0.1237739
$ close(out)
$ # Opening test.xls now works
