Reading Excel data into SPSS

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Reading Excel data into SPSS

David Lindsay-4
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
Reply | Threaded
Open this post in threaded view
|

Re: Reading Excel data into SPSS

David Marso
Administrator
That's not going to fly without a bit of pain (learning SAX basic scripting) or dropping some coin into the tip jar!
--
David Lindsay-4 wrote
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.
---
"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?"
Reply | Threaded
Open this post in threaded view
|

Re: Reading Excel data into SPSS

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)
or dropping some coin into the tip jar!
--

David Lindsay-4 wrote
> 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

> LISTSERV@.UGA

>  (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
Reply | Threaded
Open this post in threaded view
|

Re: Reading Excel data into SPSS

David Marso
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!
--
Martha Hewett wrote
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)
> or dropping some coin into the tip jar!
> --
>
> David Lindsay-4 wrote
> > 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
>
> > LISTSERV@.UGA
>
> >  (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
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?"
Reply | Threaded
Open this post in threaded view
|

Re: Reading Excel data into SPSS

John F Hall
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
Sent: 17 January 2013 21:41
To: [hidden email]
Subject: 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)
or dropping some coin into the tip jar!
--

David Lindsay-4 wrote


> 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

Reply | Threaded
Open this post in threaded view
|

Re: Reading Excel data into SPSS

David Lindsay

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]]
Sent: 18 January 2013 08:44
To: 'David Lindsay'
Cc: [hidden email]
Subject: RE: Reading Excel data into SPSS

 

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
Sent: 17 January 2013 21:41
To: [hidden email]
Subject: 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)
or dropping some coin into the tip jar!
--

David Lindsay-4 wrote


> 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

Reply | Threaded
Open this post in threaded view
|

Reading Excel data into SPSS

Zuluaga, Juan
In reply to this post by David Lindsay-4
LISTSERV at the University of Georgia

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=””)

 

}

 

 

 

 

 

 

 

 

 

Reply | Threaded
Open this post in threaded view
|

Re: Reading Excel data into SPSS

Zuluaga, Juan
In reply to this post by David Lindsay-4
LISTSERV at the University of Georgia

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
Sent: Friday, January 18, 2013 9:03 AM
To: 'SPSSX(r) Discussion'
Subject: Reading Excel data into SPSS

 

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=””)

 

}

 

 

 

 

 

 

 

 

 

Reply | Threaded
Open this post in threaded view
|

Re: Reading Excel data into SPSS

Zuluaga, Juan
In reply to this post by Zuluaga, Juan
LISTSERV at the University of Georgia

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
Sent: 18 January 2013 15:03
To: [hidden email]
Subject: Reading Excel data into SPSS

 

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=””)

 

}

 

 

 

 

 

 

 

 

 

Reply | Threaded
Open this post in threaded view
|

Automatic reply: Reading Excel data into SPSS

Li Cao

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