Apologies if this is already answered elsewhere, but I could not find the
answer searching the archives.
I am trying to bring my data in from an .xlsx file. The data of interest is
an n x m array, but it is preceded with ~2 columns and 30 rows of
instrumental "information." An abbreviated example (cols denoted with
","
for this example:
Subject , 01
wavelength, 400
(30 more rows of similar metadata data)
10 , 11 ,23 ,34 ,54 (60 more columns of similar data)
1 , 65 ,61 ,43 ,21
12 ,14 ,78 ,23 ,23
(90 rows of similar data
I have put together a script that can read the data in with the "xlsx"
package, but this package runs a little slow compared to RODBC. Since I
will be processing hundreds to thousands of files, I would like a faster
option. RODBC seems to be it, but I am having trouble telling it not to
take the first row of "data" as a header/column name.
Here is my present command
rawspectrum <- odbcConnectExcel2007("directory path/filename.xlsx",
readOnly
= TRUE)
editspectrum <- sqlFetch(rawspectrum,"Sheet1", as.is=TRUE, colnames
= FALSE,
rownames = FALSE, nullstring="")
odbcClose(rawspectrum)
If I ran this on the above fake data I would get something like:
Subject , 01 ,F1 ,F2 ,F3
1 wavelength, 400
2 10 , 11 ,23 ,34 ,54
3 1 , 65 ,61 ,43 ,21
4 12 ,14 ,78 ,23 ,23
When I want to see
1 Subject , 01
2 wavelength, 400
3 10 , 11 ,23 ,34 ,54
4 1 , 65 ,61 ,43 ,21
5 12 ,14 ,78 ,23 ,23
For the moment, yes the files have to be in xlsx (unfortunately) and yes
they need to (and will always) have the "metadata" (again
unfortunately).
If the xlsx package is the only option given the awkward data format just
let me know.
[[alternative HTML version deleted]]
