Solved

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

Posted on 2014-12-10
8
295 Views
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.
0
Comment
Question by:jrmcanada2
  • 4
  • 3
8 Comments
 
LVL 34

Expert Comment

by:PatHartman
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.
0
 

Author Comment

by:jrmcanada2
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.
0
 
LVL 34

Expert Comment

by:PatHartman
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.
0
 

Author Comment

by:jrmcanada2
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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 34

Expert Comment

by:PatHartman
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?
0
 

Author Comment

by:jrmcanada2
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:

Name
Label
Type

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:

Values

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.
0
 
LVL 34

Expert Comment

by:PatHartman
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.
0
 
LVL 7

Accepted Solution

by:
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.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Healthcare organizations in the United States must adhere to the guidance of both the HIPAA (Health Insurance Portability and Accountability Act) and HITECH (Health Information Technology for Economic and Clinical Health Act) for securing and protec…
The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now