Importing Access data to SPSS
Importing Access data to SPSS
(OP)
I want to be able to import data from an Access 2000 table directly into an SPSS 11.5 file (called Survey.sav). (The field names in the Access table are identical to the variable names in the SPSS file.) Since I will need to do repeated imports (to import new data or changed data entered in the Access table overtime), I thought the best way would be to establish an ODBC connection, set up and save a query in SPSS, and then reuse it as needed to update my SPSS file.
I used the Database Capture Wizard to establish an ODBC connection between the Access table and the SPSS 11.5 file. I followed the steps from these documents:
www.ncl.ac.uk/ucs/docs/pdf/AD04.pdf
http://www.childrens-mercy.org/stats/data/access.asp
I created and saved a query so that I can re-use it to update the Survey.sav file. But I'm having a problem. When I open up Survey.sav and then run the query (File > Open Database > Run Query . . .), SPSS creates a *new* file with the updated data instead of updating the original Survey.sav file. The new file is named "Untitled" and because it’s new it does not have the variable labels and values assigned in Survey.sav.
How can I set up the query so that my working file, Survey.sav, is updated (with both new cases and possibly original data that has been changed) instead of a new .sav file being created?
Someone suggested using "APPLY DICTIONARY FROM c:\path\Survay.sav" and/or UPDATE commands. Not being very familiar with using syntax in SPSS, I wouldn't know how to proceed, or even if these are good suggestions.
Any help is greatly appreciated. Thank you.
- Hoving
I used the Database Capture Wizard to establish an ODBC connection between the Access table and the SPSS 11.5 file. I followed the steps from these documents:
www.ncl.ac.uk/ucs/docs/pdf/AD04.pdf
http://www.childrens-mercy.org/stats/data/access.asp
I created and saved a query so that I can re-use it to update the Survey.sav file. But I'm having a problem. When I open up Survey.sav and then run the query (File > Open Database > Run Query . . .), SPSS creates a *new* file with the updated data instead of updating the original Survey.sav file. The new file is named "Untitled" and because it’s new it does not have the variable labels and values assigned in Survey.sav.
How can I set up the query so that my working file, Survey.sav, is updated (with both new cases and possibly original data that has been changed) instead of a new .sav file being created?
Someone suggested using "APPLY DICTIONARY FROM c:\path\Survay.sav" and/or UPDATE commands. Not being very familiar with using syntax in SPSS, I wouldn't know how to proceed, or even if these are good suggestions.
Any help is greatly appreciated. Thank you.
- Hoving
Talk To Other Members
RE: Importing Access data to SPSS
Did you get this sorted?
Richard
RE: Importing Access data to SPSS
> Richard
Nope. Still haven't figured it out. I did find a VB program which does what I'm trying to do, but I'm not sure where to place this program in my database (I tried making it a Module and was prompted for a Macro Name). Here's the link:
http://pages.infinit.net/rlevesqu/Scripts/ImportExport/ExportLabelsFromAccessToSPSS.txt
###
I would think that being able to *update* - not create -a .sav file with data from an Access (or Excel) file would be a common process by users. But I can't find any information about how to do it. Everything I've come across is about importing files into SPSS to create a new SPSS file with the data. If you have to do repeated imports, because the data entry takes place in Excel or Access, this means reentering the SPSS variable and value labels each time. Not very efficient if you have 100+ variables.
Thoughts?
- Hoving
RE: Importing Access data to SPSS
You can find Stata at http://www.stata.com. There is an active online community who are very helpful at solving pronlems and supporting other users.
RE: Importing Access data to SPSS
http://www.cmh.edu/stats/data/access.asp