Tuesday 24 July 2012

Data transfer (migration) from Access to SPSS

Problem:

Your data has been entered in MS Access, where all the variables (fields) have been defined names, width, type etc. and there are look up arrays/tables linked with each of the fields to describe the Response Values. But then you need to run some stats in SPSS. So, you basically EXPORT the file to some data analysis software like SPSS. One way to do it is export to MS Excel format and open/Import the Excel sheet into SPSS, which is pretty straightforward and simple. But then you examine the file and you will notice that in this transition, all the nifty labels of fields and values are gone and you will have to either make guesses or look at your data collection instruments to make sense of the numbers.

So, the choice you have is either to keep doing that or manually assign all the labels in SPSS. It is fine if you have only a handful of variables, but if you have a long list it is a lot of work!!!

What do you do? I have been trying to get around this problem for months now with no success. There is a Script on my favorite SPSS site:
http://www.spsstools.net/Scripts/ImportExport/ExportLabelsFromAccessToSPSS.txt

which should do the needful but I am certainly not doing it right. Need help. All the google search and various discussion groups have proven to be of no use also. Apparently I can create a link through ODBC but I am too lame to figure that out...

Any help?

Update 1: No luck with VB or Python or ODBC etc. because I am too dumb to learn them on my own! However, I learnt that if you have to do that a lot, there is a handy program that can do it for you. It is called Stat/Transfer. One caveat is that it is not FREE. The student version costs $59. In future I would buy it if I am stuck with multiple transfers between various databases. In the past they also used to have DBMS copy for such things but it does not exist anymore. I have tried to search for a Open Source version for Stat/Transfer but no luck yet!!

No comments: