Importing Data from Excel to SPSS/PASW
Uploaded by bartonpoulson on 27.10.2009
Transcript:
Hi, my name's Bart Poulson and in this tutorial, I'm going to show how to take data that has
been entered in Excel, in a spreadsheet, into, how to import it into SPSS, uh, now know as
PASW, a statistics program for analysis. Uh, now, in an earlier, uh, tutorial, I showed
how I create a data form. I call— you know, it's "Creating a Questionnaire in Word."
Uh, I've got a title there. I've got a little statement from my institutional review board
about voluntary anonymous confidential participation. And I have just a few sample questions I put
on here. I asked people how old they were, their gender, relationships they might be
in, and then I have three questions here that people rate their agreement from a 0 to 10
scale. So, I showed that one already. Lemmie show you what it looks like in Excel. Right
here, I first have an ID variable that, uh, so I can, uh, go back and find a questionnaire
if something needs to be checked. Uh, that's, by the way, that's just the number I write
on the top of the questionnaire. There's age, and— and I left a few by— I
made up all of these data, but I intentionally left things blank every now and then to show
how to deal with it. And what you do is you just skip it over entirely. Then I have a
variable here called "Female." When I entered the data, I wrote it down as female is 1,
male is 0. It's called a dichotomous variable and it's easy to deal with. Uh, for relationship,
I had some codes. Single's 1, married is 2, and anything else I wrote it, but I ultimately
coded that as whether they were in a relationship— 0 for no, 1 for yes. And then, uh, questions
1, 2, and 3 are, uh, rating scales agreements and they go from 0 to 10. And I just entered
the numbers right there. So, now that you have it in Excel, um, the job is to get it
out of Excel and into SPSS. Now, I'll let you know, um, this is my first time importing
data from SPSS, excuse me, from Excel, uh, 2008 to SPSS/PASW version 17 on my Mac, and
I experienced some technical errors, but I'll show you how it's supposed to work and how
I actually am able to make it work on this computer. Alright. So here's what I do. First
thing is you need to save the Excel spreadsheet. And, uh, I saved it earlier, "Data Form for
SPSS" and I've actually got it right here on my desktop. There it is. Um, now normally
what you have to do is you have to close the data file in Excel. It can't be open somewhere
else. So I close that and then I go over to SPSS, uh, now known as PASW. SPSS stands for
"Statistical Package for the Social Sciences"; PASW is for "Predictive Analytic Software."
They just changed their name, but it's the same program. Now the way it's supposed to
work is you come over here to this little, uh, folder that says "Open a data document."
You click on that. And the dialogue box should come up. There it is. And "Open Data." Now,
you need to tell it what kind of file you're looking for. I saved it as an Excel spreadsheet
so I come down here and I go to Excel and I saved it on the desktop and you can see,
it's right there, "Data Form for SPSS.xls." Normally it should open this up right away
or I'll get a single dialogue box that asks, you know, which sheet the data are on and
how many. Now, uh, I believe if I press this, I'm gonna get an error message. Ok. That's
fine. I have not been able to figure out why this is happening, but there's a way around
it. Um, but normally this would open up and if you're on a PC, it might still work, so
I wanted you to be aware of that. Ok. What I'm gonna do then is I'm gonna go back, uh,
to Excel by opening up my data sheet again. Alright. So there it is and I'm gonna save
it in a generic format that works really well for, uh, exporting and importing. So I'm gonna
come up here to "Save As," file. So I'm gonna save it in a different format. Because right
now it's in an Excel xls format. See there's the "xls" right there. Instead, what I'm gonna
do is I'm gonna save this. In the drop down menu I'm gonna come down to this one right
here, a tab delimited file ".txt" for text. Basically anything in the world can read this.
So I'm gonna select that one. And you can see it's now become "txt" up here. It's got
the same name, but a different, uh, suffix. I'm gonna hit "Save." It's gonna go to the
desktop. Now the thing is, uh, text files can only save one, uh, sheet. I've got two
sheets here. Um, but since I've got those, I'm just gonna go ahead. That's fine. Press
"OK." Alright, I'm gonna close that because, again, uh, yeah I'll save it. And I'm gonna
go to the desktop and you see I have the new file right here. "Data Form for SPSS.txt."
Uh, tab the limited text file. So now I'm gonna go back to SPSS and I'm gonna do this
open thing. I click on the folder again. This time I tell it I am looking for a text file.
I have to scroll down a little bit. There it is. Text. And you can see it just appeared
right there. Now, I'm gonna have to go through a little bit of rigmarole to get this in,
but it's— it's not a big deal. So I press "Open" and [reading] "Does it have a
predefined format?" No. I'm just gonna go ahead and hit "Continue." See, here it gives
an, uh, basic idea of what it looks like. These, uh, labels are off be— just
simply because this one's really long. Press "Continue." Um, this says that they're separated
by a tab. So I leave that. The variable names are at the top. You see this first line, "ID",
"Age"'... so I— I click that, say "Yes," go to the next one... Each line represents
one person's responses. That's true. I want all of 'em in there. I can imagine you would
use these other ones, but you know, we're not gonna deal with that. Press "Continue"
and I say that it's a tab that's separating everything. There's no qualifiers. I—
I don't even really know what that is. And I press "Continue." And it asks me if the
variables are numeric or whatnot. I'm just gonna leave that the default and press "Continue"
again. Oh, by the way, you can see how it's lining up right here. And "save this file
format..." would I "like to paste the system" [reading questions to self.] Whatever. I'm
just gonna press "Done." And, it does this little magic thingy and there it is! Now I
have a data file that says "ID," "Age," "Female" — um, this one's "Relationship"—
I'll just drag it and make it a little bit bigger. "q01," "q02," "q03." Perfect. We're
set. I now have the data entered in here. Now, I want you to notice one thing. Uh, back
in the Excel spreadsheet, when I had age and I didn't enter it, it was totally blank. When
it comes into SPSS, it puts this little dot there. Now it is not a decimal place. It's
the system missing dot. Um, and so you don't wanna mess with it, you just leave it right
there and everything's cool. Now, in another tutorial, I'm gonna show— oh. You know
what? Always, always save. Um, let's put this "Sample Data in SPSS." And I'll just save
that to the desktop and I save it as an SPSS file and save. By the way, I would save constantly
because, uh, in my experience SPSS crashes all the time, um, so you wanna save after
almost every step. And by the way, so you know in SPSS, you have one sheet for the data,
you have another sheet for something called the syntax— it keeps a record of what's
done. This means that I opened it. This means how I saved it. And then you can have another
one, I'm sorry, this is the syntax file; the other one was the output. Anyhow, I'll talk
more about those in a minute, but here's the data. The data are entered; the data are in
SPSS; I have saved 'em. I'm gonna show how to create what are called labels, uh, so,
for instance, the "Female" shows up as male and female instead of the 0's and 1's and
the relationship shows up as, you know in a relationship or not. And then, then it can
code some of these variables. Anyhow, I'll save that for next one. And I will see you
there.
been entered in Excel, in a spreadsheet, into, how to import it into SPSS, uh, now know as
PASW, a statistics program for analysis. Uh, now, in an earlier, uh, tutorial, I showed
how I create a data form. I call— you know, it's "Creating a Questionnaire in Word."
Uh, I've got a title there. I've got a little statement from my institutional review board
about voluntary anonymous confidential participation. And I have just a few sample questions I put
on here. I asked people how old they were, their gender, relationships they might be
in, and then I have three questions here that people rate their agreement from a 0 to 10
scale. So, I showed that one already. Lemmie show you what it looks like in Excel. Right
here, I first have an ID variable that, uh, so I can, uh, go back and find a questionnaire
if something needs to be checked. Uh, that's, by the way, that's just the number I write
on the top of the questionnaire. There's age, and— and I left a few by— I
made up all of these data, but I intentionally left things blank every now and then to show
how to deal with it. And what you do is you just skip it over entirely. Then I have a
variable here called "Female." When I entered the data, I wrote it down as female is 1,
male is 0. It's called a dichotomous variable and it's easy to deal with. Uh, for relationship,
I had some codes. Single's 1, married is 2, and anything else I wrote it, but I ultimately
coded that as whether they were in a relationship— 0 for no, 1 for yes. And then, uh, questions
1, 2, and 3 are, uh, rating scales agreements and they go from 0 to 10. And I just entered
the numbers right there. So, now that you have it in Excel, um, the job is to get it
out of Excel and into SPSS. Now, I'll let you know, um, this is my first time importing
data from SPSS, excuse me, from Excel, uh, 2008 to SPSS/PASW version 17 on my Mac, and
I experienced some technical errors, but I'll show you how it's supposed to work and how
I actually am able to make it work on this computer. Alright. So here's what I do. First
thing is you need to save the Excel spreadsheet. And, uh, I saved it earlier, "Data Form for
SPSS" and I've actually got it right here on my desktop. There it is. Um, now normally
what you have to do is you have to close the data file in Excel. It can't be open somewhere
else. So I close that and then I go over to SPSS, uh, now known as PASW. SPSS stands for
"Statistical Package for the Social Sciences"; PASW is for "Predictive Analytic Software."
They just changed their name, but it's the same program. Now the way it's supposed to
work is you come over here to this little, uh, folder that says "Open a data document."
You click on that. And the dialogue box should come up. There it is. And "Open Data." Now,
you need to tell it what kind of file you're looking for. I saved it as an Excel spreadsheet
so I come down here and I go to Excel and I saved it on the desktop and you can see,
it's right there, "Data Form for SPSS.xls." Normally it should open this up right away
or I'll get a single dialogue box that asks, you know, which sheet the data are on and
how many. Now, uh, I believe if I press this, I'm gonna get an error message. Ok. That's
fine. I have not been able to figure out why this is happening, but there's a way around
it. Um, but normally this would open up and if you're on a PC, it might still work, so
I wanted you to be aware of that. Ok. What I'm gonna do then is I'm gonna go back, uh,
to Excel by opening up my data sheet again. Alright. So there it is and I'm gonna save
it in a generic format that works really well for, uh, exporting and importing. So I'm gonna
come up here to "Save As," file. So I'm gonna save it in a different format. Because right
now it's in an Excel xls format. See there's the "xls" right there. Instead, what I'm gonna
do is I'm gonna save this. In the drop down menu I'm gonna come down to this one right
here, a tab delimited file ".txt" for text. Basically anything in the world can read this.
So I'm gonna select that one. And you can see it's now become "txt" up here. It's got
the same name, but a different, uh, suffix. I'm gonna hit "Save." It's gonna go to the
desktop. Now the thing is, uh, text files can only save one, uh, sheet. I've got two
sheets here. Um, but since I've got those, I'm just gonna go ahead. That's fine. Press
"OK." Alright, I'm gonna close that because, again, uh, yeah I'll save it. And I'm gonna
go to the desktop and you see I have the new file right here. "Data Form for SPSS.txt."
Uh, tab the limited text file. So now I'm gonna go back to SPSS and I'm gonna do this
open thing. I click on the folder again. This time I tell it I am looking for a text file.
I have to scroll down a little bit. There it is. Text. And you can see it just appeared
right there. Now, I'm gonna have to go through a little bit of rigmarole to get this in,
but it's— it's not a big deal. So I press "Open" and [reading] "Does it have a
predefined format?" No. I'm just gonna go ahead and hit "Continue." See, here it gives
an, uh, basic idea of what it looks like. These, uh, labels are off be— just
simply because this one's really long. Press "Continue." Um, this says that they're separated
by a tab. So I leave that. The variable names are at the top. You see this first line, "ID",
"Age"'... so I— I click that, say "Yes," go to the next one... Each line represents
one person's responses. That's true. I want all of 'em in there. I can imagine you would
use these other ones, but you know, we're not gonna deal with that. Press "Continue"
and I say that it's a tab that's separating everything. There's no qualifiers. I—
I don't even really know what that is. And I press "Continue." And it asks me if the
variables are numeric or whatnot. I'm just gonna leave that the default and press "Continue"
again. Oh, by the way, you can see how it's lining up right here. And "save this file
format..." would I "like to paste the system" [reading questions to self.] Whatever. I'm
just gonna press "Done." And, it does this little magic thingy and there it is! Now I
have a data file that says "ID," "Age," "Female" — um, this one's "Relationship"—
I'll just drag it and make it a little bit bigger. "q01," "q02," "q03." Perfect. We're
set. I now have the data entered in here. Now, I want you to notice one thing. Uh, back
in the Excel spreadsheet, when I had age and I didn't enter it, it was totally blank. When
it comes into SPSS, it puts this little dot there. Now it is not a decimal place. It's
the system missing dot. Um, and so you don't wanna mess with it, you just leave it right
there and everything's cool. Now, in another tutorial, I'm gonna show— oh. You know
what? Always, always save. Um, let's put this "Sample Data in SPSS." And I'll just save
that to the desktop and I save it as an SPSS file and save. By the way, I would save constantly
because, uh, in my experience SPSS crashes all the time, um, so you wanna save after
almost every step. And by the way, so you know in SPSS, you have one sheet for the data,
you have another sheet for something called the syntax— it keeps a record of what's
done. This means that I opened it. This means how I saved it. And then you can have another
one, I'm sorry, this is the syntax file; the other one was the output. Anyhow, I'll talk
more about those in a minute, but here's the data. The data are entered; the data are in
SPSS; I have saved 'em. I'm gonna show how to create what are called labels, uh, so,
for instance, the "Female" shows up as male and female instead of the 0's and 1's and
the relationship shows up as, you know in a relationship or not. And then, then it can
code some of these variables. Anyhow, I'll save that for next one. And I will see you
there.