Tool to transfer SPSS variable view between SPSS and Access or SQL Server?

Posted on 2014-12-10
Last Modified: 2015-01-20
We're looking for a tool that can take an SPSS file and create an Access (or SQL Server) database that models the SPSS view including SPSS's variable view.

SPSS's native export feature allows us to export the data but we have a problem with the Values column in variable view since that column can contain what is essentially a sub-table of valid values for that field.

So we're wondering if there is a tool that would export the data to a table in an Access (or SQL Server) database but then also create and link the necessary support tables for the entries in the Values column that contain such lists.

Secondly, is there a tool that can allow us to create an SPSS file from Access?

Thanks in advance.
Question by:jrmcanada2
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 37

Expert Comment

ID: 40492091
Access uses a relational database as its data store and what you are describing violates first normal form and so would not be supported by a relational database including Jet, ACE, SQL Server, Oracle, etc.

What format does SPSS export data in?  If it exports to Excel, the sub table would be contained within a single cell.  If it is a .csv format, the sub table would be within a single field and would have to be either enclosed in quotes or delimited with something other than commas.

In either case, Access can import the file and stuff the sub table into a single column.  Of course working with it that way would be problematic so you might want to write a program that pulls the mushed data out of the column and appends it to a child table.

If SPSS can export as XML, Access can import that format.

I have never seen a tool that converts SPSS files to Jet/ACE but that doesn't mean that one doesn't exist.

Author Comment

ID: 40492114
SPSS has a variety of native export options (to Access, Excel, etc.) but none of them export the sub tables.

SPSS visually presents a table within a cell and allows the user to edit that table or select a value from it. I have no idea how it stores it in its proprietary data file. When it does an export, it just exports the specific value for that record in that field and does not export the table (i.e. the various possible values).

To model this in Access, we'd essentially need sub tables joined to the main table where each sub table contains the embedded tables from SPSS.

I realize this is HIGHLY optimistic of me :), but I was hoping that a tool exists to do that.
LVL 37

Expert Comment

ID: 40492223
So the structure is normalized.  It only contains a single value.  From your original description, it sounded like Pick which actually embedded tables into fields.  What you are describing is like the RowSource of a combo box.  You would need to look at the table definition.  There may not be an automated way to export the list but you might at least be able to see it.
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.


Author Comment

ID: 40492319
Sadly, it DOES embed a table in a field. It's just that it's built-in export command doesn't replicate that so it only exports the actual value for each record. But we need to be able to export the embedded table, too, so that the created Access database has a list of all the possible values for each field - not just a list of the values that have actually been used up to this point.
LVL 37

Expert Comment

ID: 40492379
so it only exports the actual value for each record
Now you're confusing me.  Is it ONE value or MANY values in one field?  Possible has a different meaning from actual so again I am confused.  Possible values are the RowSource of a combo or list box.  The RowSource is a list of all POSSIBLE values.  The actual value will be ONE of those.  Sounds like with SPSS, you could choose several values.  Actual values are what is stored in the table and in the case of Access, that would normally be one attribute per column.  But a database isn't smart enough to evaluate what you stuff in a column so if you want to stuff a string of values all together in one column, you can.  It is just that the database engine provides NO tools to work with that kind of data since is it is officially unsupported.

What EXACTLY gets exported?  Will you see a comma separated list like - blue, red, orange?

Author Comment

ID: 40492441
Sorry for the confusion.

Let me try to clarify.

When they are setting up the database in SPSS for each field you can specify the following parameters:


If that's all there was, then we could model it easily in Access. However, they also have an extra parameter you can set for each field:


And this is our problem. Because the "Values" parameter is actually an embedded table of options that are valid entries for that field. So, for example, they have a field for marital status and it is set up in SPSS with these parameters:

Name: MS
Label: Marital status
Type: Numeric

But when you click on the Values parameter, it pops up a table (I'm going to call it a value table from now on for clarity):

1 = Married
2 = Single
3 = Divorced
4 = Widowed

This table is entirely contained in the Values parameter for the marital status field.

When I uses SPSS to export to Access, it creates an Access database that is populated with the actual data for each person in the database. The marital status field on each record will just have the number that corresponds to the marital status of that person. So we end up with a table in Access that has 1, 2, 3, or 4 in the marital status field for each record. But nowhere in exported Access database do we get an explanation of what those numbers mean. Even though the value table (1 = Married, 2 = Single, etc.) is embedded in the SPSS database, it doesn't export this table to Access.

They have dozens of SPSS files (and are getting more all the time) that they want to export into an Access database. Each of those SPSS files might have dozens or even hundreds of fields per record. And most of those fields have their own value table.

So I'm looking for a way to export these value tables because otherwise, I get an exported Access database that is full of numbers but we don't know what any of those numbers mean.

I hope I haven't further confused things.
LVL 37

Expert Comment

ID: 40492585
Access has the same feature.  It's called Lookup and you can specify a RowSource right in the table definition.  when Access tables are exported, the "lookup" values are not exported; only the chosen value.  To get the "lookup" values, you need to examine the table definition so that is probably the case for SPSS also but you'll probably get a better answer in an SPSS forum since this really has nothing to do with Access.

Accepted Solution

slubek earned 500 total points
ID: 40493595
Secondly, is there a tool that can allow us to create an SPSS file from Access?
You have to use some external tools, like Stat/Transfer.

Featured Post

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question