SPSSX Discussion

"Get data" from Excel sheet reads wrong row: V17.0

Classic

List

Threaded

4 messages Options

Allan Reese (Cefas)

"Get data" from Excel sheet reads wrong row: V17.0

LISTSERV at the University of Georgia

The following command should read names from row 4. It read them from row 1. It should not even be looking at row 1.

Is that true for other versions?

GET DATA /TYPE=XLSX

/FILE='filename.xlsx'

/SHEET=name 'sheetname'

/CELLRANGE=range 'A4:BQ85'

/READNAMES=on

/ASSUMEDSTRWIDTH=32767.

Syntax reference manual (p778):

READNAMES Subcommand

ON Read the first row of the sheet or specified range as variable names. This is the

default. Values that contain invalid characters or do not meet other criteria for

variable names are converted to valid variable names. For more information,

see Variable Names on p. 45.

Allan Reese

This email and any attachments are intended for the named recipient only. Its unauthorised use, distribution, disclosure, storage or copying is not permitted. If you have received it in error, please destroy all copies and notify the sender. In messages of a non-business nature, the views and opinions expressed are the author's own and do not necessarily reflect those of Cefas. Communications on Cefas’ computer systems may be monitored and/or recorded to secure the effective operation of the system and for other lawful purposes.

Albert-Jan Roskam

Re: "Get data" from Excel sheet reads wrong row: V17.0

Hi,

Sounds like a bug to me. As a work-around you could use R (under Spss), using the XLConnect package. Here is some sample code:

require(XLConnect) || install.packages("XLConnect", dependencies=TRUE)
require(plyr) || install.packages("plyr", dependencies=TRUE)

getExcelData <- function(file) {
wb <- XLConnect::loadWorkbook(file)
sheets <- XLConnect::getSheets(wb)
allData = list()
for (i in 1:length(sheets)) {
    if(sheets[i] != "Info") {
      print(sprintf("%s - %s", basename(file), sheets[i]))
      # header
      data <- XLConnect::readWorksheet(wb, sheet=sheets[i], startRow=5,
        endRow=5, startCol=1, header=FALSE)
    # data
    data <- XLConnect::readWorksheet(wb, sheet=sheets[i], startRow=9,
        startCol=1, header=FALSE)
      names(data) <- header
    allData[[sheets[i]]] <- data
      }
}
return(do.call(plyr::rbind.fill, allData))
}

XLConnect is Java-based and is not breathtakingly fast, but it works better than other similar packages (RODBC, read.xls). Best is, of course, to avoid Excel like the plague! ;-)

Cheers!!
Albert-Jan

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a fresh water system, and public health, what have the Romans ever done for us?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: Allan Reese (Cefas) <[hidden email]>
To: [hidden email]
Sent: Monday, January 9, 2012 10:42 AM
Subject: [SPSSX-L] "Get data" from Excel sheet reads wrong row: V17.0

LISTSERV at the University of Georgia

The following command should read names from row 4. It read them from row 1. It should not even be looking at row 1.

Is that true for other versions?

GET DATA /TYPE=XLSX

/FILE='filename.xlsx'

/SHEET=name 'sheetname'

/CELLRANGE=range 'A4:BQ85'

/READNAMES=on

/ASSUMEDSTRWIDTH=32767.

Syntax reference manual (p778):

READNAMES Subcommand

ON Read the first row of the sheet or specified range as variable names. This is the

default. Values that contain invalid characters or do not meet other criteria for

variable names are converted to valid variable names. For more information,

see Variable Names on p. 45.

Allan Reese

This email and any attachments are intended for the named recipient only. Its unauthorised use, distribution, disclosure, storage or copying is not permitted. If you have received it in error, please destroy all copies and notify the sender. In messages of a non-business nature, the views and opinions expressed are the author's own and do not necessarily reflect those of Cefas. Communications on Cefas’ computer systems may be monitored and/or recorded to secure the effective operation of the system and for other lawful purposes.

Sonia Brandon-2

Re: "Get data" from Excel sheet reads wrong row: V17.0

LISTSERV at the University of Georgia We got rid of v 16 and 17 because we found both to have issues. We have both 18 and 19 now, and they both seem to be much better.

>>> Albert-Jan Roskam <[hidden email]> 1/10/2012 1:08 PM >>>

Hi,

Sounds like a bug to me. As a work-around you could use R (under Spss), using the XLConnect package. Here is some sample code:

require(XLConnect) || install.packages("XLConnect", dependencies=TRUE)
require(plyr) || install.packages("plyr", dependencies=TRUE)

XLConnect is Java-based and is not breathtakingly fast, but it works better than other similar packages (RODBC, read.xls). Best is, of course, to avoid Excel like the plague! ;-)

Cheers!!
Albert-Jan

From: Allan Reese (Cefas) <[hidden email]>
To: [hidden email]
Sent: Monday, January 9, 2012 10:42 AM
Subject: [SPSSX-L] "Get data" from Excel sheet reads wrong row: V17.0

The following command should read names from row 4. It read them from row 1. It should not even be looking at row 1.

Is that true for other versions?

GET DATA /TYPE=XLSX

/FILE='filename.xlsx'

/SHEET=name 'sheetname'

/CELLRANGE=range 'A4:BQ85'

/READNAMES=on

/ASSUMEDSTRWIDTH=32767.

Syntax reference manual (p778):

READNAMES Subcommand

ON Read the first row of the sheet or specified range as variable names. This is the

default. Values that contain invalid characters or do not meet other criteria for

variable names are converted to valid variable names. For more information,

see Variable Names on p. 45.

Allan Reese

This email and any attachments are intended for the named recipient only. Its unauthorised use, distribution, disclosure, storage or copying is not permitted. If you have received it in error, please destroy all copies and notify the sender. In messages of a non-business nature, the views and opinions expressed are the author's own and do not necessarily reflect those of Cefas. Communications on Cefas’ computer systems may be monitored and/or recorded to secure the effective operation of the system and for other lawful purposes.

Jarrod Teo-2

Re: "Get data" from Excel sheet reads wrong row: V17.0

Hi Allan,

If it does not trouble you

Save the original data first in a seperate folder.
Delete the first few unwanted rows and import it into SPSS.

Warmest Regards
Dorraj Oet

Date: Tue, 10 Jan 2012 13:22:17 -0700
From: [hidden email]
Subject: Re: "Get data" from Excel sheet reads wrong row: V17.0
To: [hidden email]

We got rid of v 16 and 17 because we found both to have issues. We have both 18 and 19 now, and they both seem to be much better.

>>> Albert-Jan Roskam <[hidden email]> 1/10/2012 1:08 PM >>>

Hi,

Sounds like a bug to me. As a work-around you could use R (under Spss), using the XLConnect package. Here is some sample code:

require(XLConnect) || install.packages("XLConnect", dependencies=TRUE)
require(plyr) || install.packages("plyr", dependencies=TRUE)

XLConnect is Java-based and is not breathtakingly fast, but it works better than other similar packages (RODBC, read.xls). Best is, of course, to avoid Excel like the plague! ;-)

Cheers!!
Albert-Jan

From: Allan Reese (Cefas) <[hidden email]>
To: [hidden email]
Sent: Monday, January 9, 2012 10:42 AM
Subject: [SPSSX-L] "Get data" from Excel sheet reads wrong row: V17.0

The following command should read names from row 4. It read them from row 1. It should not even be looking at row 1.

Is that true for other versions?

GET DATA /TYPE=XLSX

/FILE='filename.xlsx'

/SHEET=name 'sheetname'

/CELLRANGE=range 'A4:BQ85'

/READNAMES=on

/ASSUMEDSTRWIDTH=32767.

Syntax reference manual (p778):

READNAMES Subcommand

ON Read the first row of the sheet or specified range as variable names. This is the

default. Values that contain invalid characters or do not meet other criteria for

variable names are converted to valid variable names. For more information,

see Variable Names on p. 45.

Allan Reese

This email and any attachments are intended for the named recipient only. Its unauthorised use, distribution, disclosure, storage or copying is not permitted. If you have received it in error, please destroy all copies and notify the sender. In messages of a non-business nature, the views and opinions expressed are the author's own and do not necessarily reflect those of Cefas. Communications on Cefas’ computer systems may be monitored and/or recorded to secure the effective operation of the system and for other lawful purposes.