Reading Excel data into SPSS
Locked
 
|
Good day
I have been given a survey for analysis that is in Excel The format is variable name in row 1, the variable label is in row 2 and the data in row 3 onwards. I would appreciate some help in writing a data read statement so that the variable label is written directly into variable labels for the variable in row 1. BTW I only have SPSS v12 id s1 s2 s3 id Completed Last page seen Start language 41 en 43 en 45 2012-12-24 13:55:32 6 en 49 2012-12-27 09:47:02 6 en 51 2012-12-27 09:48:45 6 en 53 2012-12-27 09:50:45 6 en 55 2012-12-27 09:52:10 6 en 57 2012-12-27 09:53:50 6 en Thanks and regards, David Lindsay ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD |
Administrator
|
That's not going to fly without a bit of pain (learning SAX basic scripting) or dropping some coin into the tip jar!
--
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me. --- "Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis." Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?" |
Re: Reading Excel data into SPSS
|
|
Here's my clunky suggestion -
Temporarily put the variable labels in a separate workbook or sheet and delete that row from the Excel file Read in the variable names and data just using the file/open/data route.
In Excel, copy the labels and then paste/transpose them. Copy the column of transposed variable names and past them into the label column of the variable view. Not elegant, but no scripting need be learned and you don't have to type the labels in from scratch.
On Thu, Jan 17, 2013 at 12:25 PM, David Marso <[hidden email]> wrote: That's not going to fly without a bit of pain (learning SAX basic scripting) This e-mail transmission and any attachments accompanying it may contain confidential and/or proprietary information and is intended only for the person or entity to whom it was originally addressed. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or other use of this information is strictly prohibited. Any unauthorized interception of this transmission is illegal. If you have received this transmission in error, please notify the sender by reply e-mail, and then destroy all copies of this transmission
|
|
Administrator
|
Actually not so clunky. If one had to do it to a whole bunch of files it would be ugly.
OTOH: the VBA/Scripting solution would be ugly as well and would best be run from Excel rather than SPSS unless one wanted to manipulate Excel com objects from SAX. Also wouldn't be very clean without segregating the Label row. Probably best that David tell his data vender for the future that the current format is really not very convenient! --
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me. --- "Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis." Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?" |
|
In reply to this post by Martha Hewett
David Martha’s solution is the simplest, and also what I was about to suggest. You don’t say how experienced you are with SPSS, but there is a teach-yourself course with dozens of (syntax-based) SPSS tutorials on my website, all of which will work with versions from 11 onwards. They are specifically oriented to the processing and analysis of data from questionnaire surveys, but as yet there are none on manipulation of data in date formats. Get back to me if you need further help. John F Hall (Mr) [retired academic survey researcher] Email: [hidden email] Website: www.surveyresearch.weebly.com From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Martha Hewett Here's my clunky suggestion - Temporarily put the variable labels in a separate workbook or sheet and delete that row from the Excel file Read in the variable names and data just using the file/open/data route. In Excel, copy the labels and then paste/transpose them. Copy the column of transposed variable names and past them into the label column of the variable view. Not elegant, but no scripting need be learned and you don't have to type the labels in from scratch. On Thu, Jan 17, 2013 at 12:25 PM, David Marso <[hidden email]> wrote: That's not going to fly without a bit of pain (learning SAX basic scripting) This e-mail transmission and any attachments accompanying it may contain confidential and/or proprietary information and is intended only for the person or entity to whom it was originally addressed. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or other use of this information is strictly prohibited. Any unauthorized interception of this transmission is illegal. If you have received this transmission in error, please notify the sender by reply e-mail, and then destroy all copies of this transmission |
Re: Reading Excel data into SPSS
|
|
Thanks for your help so far. I’ll have a look at your site, I am reasonably good at writing syntax/macros but self taught, of course. The main blockage was to read in the variable label text into a specific variable. David Lindsay 17 Bayliss Road, Wargrave, Berkshire, RG10 8DR. +44 (0)118 940 2500 and Cell +44 (0)78 9996 3336 From: John F Hall [mailto:[hidden email]] David Martha’s solution is the simplest, and also what I was about to suggest. You don’t say how experienced you are with SPSS, but there is a teach-yourself course with dozens of (syntax-based) SPSS tutorials on my website, all of which will work with versions from 11 onwards. They are specifically oriented to the processing and analysis of data from questionnaire surveys, but as yet there are none on manipulation of data in date formats. Get back to me if you need further help. John F Hall (Mr) [retired academic survey researcher] Email: [hidden email] Website: www.surveyresearch.weebly.com From: SPSSX(r) Discussion [[hidden email]] On Behalf Of Martha Hewett Here's my clunky suggestion - Temporarily put the variable labels in a separate workbook or sheet and delete that row from the Excel file Read in the variable names and data just using the file/open/data route. In Excel, copy the labels and then paste/transpose them. Copy the column of transposed variable names and past them into the label column of the variable view. Not elegant, but no scripting need be learned and you don't have to type the labels in from scratch. On Thu, Jan 17, 2013 at 12:25 PM, David Marso <[hidden email]> wrote: That's not going to fly without a bit of pain (learning SAX basic scripting) > Good day > > I have been given a survey for analysis that is in Excel > > The format is variable name in row 1, the variable label is in row 2 and > the > data in row 3 onwards. I would appreciate some help in writing a data read > statement so that the variable label is written directly into variable > labels for the variable in row 1. > BTW I only have SPSS v12 > > id s1 s2 s3 > id Completed Last page seen Start language > 41 en > 43 en > 45 2012-12-24 13:55:32 6 en > 49 2012-12-27 09:47:02 6 en > 51 2012-12-27 09:48:45 6 en > 53 2012-12-27 09:50:45 6 en > 55 2012-12-27 09:52:10 6 en > 57 2012-12-27 09:53:50 6 en > > > Thanks and regards, > David Lindsay > > ===================== > To manage your subscription to SPSSX-L, send a message to > [hidden email] > (not to SPSSX-L), with no body text except the > command. To leave the list, send the command > SIGNOFF SPSSX-L > For a list of commands to manage subscriptions, send the command > INFO REFCARD ----- Please reply to the list and not to my personal email. Those desiring my consulting or training services please feel free to email me. -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Reading-Excel-data-into-SPSS-tp5717456p5717457.html Sent from the SPSSX Discussion mailing list archive at Nabble.com. ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD This e-mail transmission and any attachments accompanying it may contain confidential and/or proprietary information and is intended only for the person or entity to whom it was originally addressed. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or other use of this information is strictly prohibited. Any unauthorized interception of this transmission is illegal. If you have received this transmission in error, please notify the sender by reply e-mail, and then destroy all copies of this transmission |
|
|
In reply to this post by David Lindsay-4
I would convert the xls to csv (using something like Gnumeric’ s ssconvert),
and then use some little script in R, python, or awk to massage the files.
With R, for a batch of files:
setfilestoprocess = list.files(pattern=”csv”)
# or something like that, to list all those you want to read. for (thisfile in setfilestoprocess) { firstpass = read.csv(“yourfile.csv”, head=FALSE, as.is=TRUE) variablenames = firstpass[1,] #reads 1st row variablelabels = firstpass[2,] # reads 2nd row secondpass = read.csv(“yourfile.csv”, head=FALSE, as.is=TRUE, skip=4) # skips 4 rows. names(secondpass) = variablenames #or names(secondpass) = variablelabels, whatever # Since your SPSS version may not be able to communicate with embedded code, you #may need to convert to csv, etc.
filename = paste(thisfile, ”.csv”, sep=””) write.csv(secondpass, file=filename, na=””) } |
Re: Reading Excel data into SPSS
|
|
In reply to this post by David Lindsay-4
I made a mistake, my apologies. The lines that say
firstpass = read.csv(“yourfile.csv”, head=FALSE, as.is=TRUE) secondpass = read.csv(“yourfile.csv”, head=FALSE, as.is=TRUE, skip=4) should read firstpass = read.csv(thisfile, head=FALSE, as.is=TRUE) secondpass = read.csv(thisfile, head=FALSE, as.is=TRUE, skip=4) From: Zuluaga, Juan
I would convert the xls to csv (using something like Gnumeric’ s ssconvert),
and then use some little script in R, python, or awk to massage the files.
With R, for a batch of files:
setfilestoprocess = list.files(pattern=”csv”)
# or something like that, to list all those you want to read. for (thisfile in setfilestoprocess) { firstpass = read.csv(“yourfile.csv”, head=FALSE, as.is=TRUE) variablenames = firstpass[1,] #reads 1st row variablelabels = firstpass[2,] # reads 2nd row secondpass = read.csv(“yourfile.csv”, head=FALSE, as.is=TRUE, skip=4) # skips 4 rows. names(secondpass) = variablenames #or names(secondpass) = variablelabels, whatever # Since your SPSS version may not be able to communicate with embedded code, you #may need to convert to csv, etc.
filename = paste(thisfile, ”.csv”, sep=””) write.csv(secondpass, file=filename, na=””) } |
Re: Reading Excel data into SPSS
|
|
In reply to this post by Zuluaga, Juan
Mr. Lindsay, greetings; apologies again, I got confused by rows 3th and 4th having blank values. I don’ t know enough to appreciate the role of variable
names vs. variable labels in SPSS vs. R. Else it would be trivial, just skip row 1, read headings from row 2.
onlypass = read.csv(thisfile,skip=1,head=TRUE,as.is=TRUE)
A nice weekend for you too.
From: SPSSX(r) Discussion [[hidden email]]
On Behalf Of Zuluaga, Juan I would convert the xls to csv (using something like Gnumeric’ s ssconvert),
and then use some little script in R, python, or awk to massage the files.
With R, for a batch of files:
setfilestoprocess = list.files(pattern=”csv”)
# or something like that, to list all those you want to read. for (thisfile in setfilestoprocess) { firstpass = read.csv(“yourfile.csv”, head=FALSE, as.is=TRUE) variablenames = firstpass[1,] #reads 1st row variablelabels = firstpass[2,] # reads 2nd row secondpass = read.csv(“yourfile.csv”, head=FALSE, as.is=TRUE, skip=4) # skips 4 rows. names(secondpass) = variablenames #or names(secondpass) = variablelabels, whatever # Since your SPSS version may not be able to communicate with embedded code, you #may need to convert to csv, etc.
filename = paste(thisfile, ”.csv”, sep=””) write.csv(secondpass, file=filename, na=””) } |
|
|
Thanks for your email. I am off sick today, Jan 18th, Friday.
For ARF, please contact ARF Sample
[hidden email] For SBA, Please contact Springboard US Sample
[hidden email] For SBUK, please contact Springboard UK Sample
[hidden email] Thanks! Li Cao
Sample Analyst | National Panels | Vision Critical
direct 778.373.0459
|
| Free forum by Nabble | Edit this page |