Importing from MS Access: How to get Description to become Variable Label

[Matt Wallaert]

All-

Apologies if this has been answered; I've done my best to search the
archives, but can't quite find an answer.

Essentially, what I have is a number of tables in MS Access that I would
like to import into SPSS, retaining both the name and description fields
and mapping them onto the variable name and variable label fields in
SPSS.  The database wizard seems to do the trick, except that the
description field in Access does not get mapped into the variable label
field in SPSS.  I could manually copy and paste each one, but that
certainly isn't my preferred option; is there an automated way to do
this?

Thanks-
Matt Wallaert

__________________________________

Researcher
Drexel Center for Weight Control
under Dr. Michael Lowe
Drexel University
Bellet 314F
[phone]

Researcher
Social Psychology
under Dr. Andrew Ward
Swarthmore College
Papazian 313
[phone]

[Boer, CPJ de]

Hello Matt,

If you are familiar with the Visual Basic of Access then here is a quick-and-dirty-routine that reads the fieldproperties of a given table and spits out syntax text in the debug window that you can copy and paste into the syntax window of SPSS and then run it.
I goes like this:

'========================================================================
Public Sub VarLabels(ByVal strTableName As String)
Dim db As DAO.Database
Dim oTableDef As Object
Dim oField As DAO.Field

On Error GoTo VarLabels_Error
Set db = CurrentDb

Set oTableDef = db.TableDefs(strTableName)
Debug.Print " VAR LABELS"

For Each oField In oTableDef.Fields
    Debug.Print "                " & oField.Name & "          '" & oField.Properties("Caption") & "'"
Next

Debug.Print "."

VarLabels_exit:
    Set oTableDef = Nothing
    Set db = Nothing
    Exit Sub

VarLabels_Error:
    If Err = 3270 Then ' Con't find property.
        Err.Clear
        Resume Next
    End If
    Resume VarLabels_exit

End Sub
'========================================================================

You call it from the debug window with:

VarLabels("tablename")

This is what it produced with one of our tables (the table is called 'patient'.

===============================================================================
 VAR LABELS
                Patcode          'Patiëntcode'
                T0datum          'Datum T0'
                T0verzond          'Datum T1, KD2 verzonden'
                T0binnen          'Datum T1 binnengekomen'
                T0reminder          'Datum T2 reminder gestuurd'
                T0telreminder          'Datum T1 tel reminder'
                T1KD2verzond          'Datum T1, KD2 verzonden'
                T1binnen          'Datum T1 binnengekomen'
                T1reminder          'Datum T2 reminder gestuurd'
                T1telreminder          'Datum T1 tel reminder'
                T2KD3verzond          'Datum T2, KD3 verzonden'
                T2binnen          'Datum T2 binnengekomen'
                T2reminder          'Datum T2 reminder gestuurd'
                T2telreminder          'Datum T2 tel reminder'
                T3verzond          'Datum T3 verzonden'
                T3binnen          'Datum T3 binnengekomen'
                T3reminder          'Datum T3 reminder gestuurd'
                T3telreminder          'Datum T3 tel reminder'
                T4verzond          'Datum T3 verzonden'
                T4binnen          'Datum T3 binnengekomen'
                T4reminder          'Datum T3 reminder gestuurd'
                T4telreminder          'Datum T3 tel reminder'
                T5verzond          'Datum T3 verzonden'
                T5binnen          'Datum T3 binnengekomen'
                T5reminder          'Datum T3 reminder gestuurd'
                T5telreminder          'Datum T3 tel reminder'
                Uitvaldat          'Datum uitval'
                Uitvalreden          'Reden uitval'
.
===================================================================

Hope this is what you meant.

Regards and all the best for 2007

Kees de Boer
________________________________________
Ing. C.P.J. de Boer
EMGO, VUmc
afd. Datamanagement & Systeembeheer
BS-7 D-451
(020) 44 49828
In theorie is er geen verschil tussen theorie en praktijk. De praktijk is echter anders...

> -----Oorspronkelijk bericht-----
> Van: SPSSX(r) Discussion [mailto:[hidden email]]
> Namens Matt Wallaert
> Verzonden: donderdag 4 januari 2007 22:13
> Aan: [hidden email]
> Onderwerp: Importing from MS Access: How to get Description
> to become Variable Label
>
> All-
>
> Apologies if this has been answered; I've done my best to
> search the archives, but can't quite find an answer.
>
> Essentially, what I have is a number of tables in MS Access
> that I would like to import into SPSS, retaining both the
> name and description fields and mapping them onto the
> variable name and variable label fields in SPSS.  The
> database wizard seems to do the trick, except that the
> description field in Access does not get mapped into the
> variable label field in SPSS.  I could manually copy and
> paste each one, but that certainly isn't my preferred option;
> is there an automated way to do this?
>
> Thanks-
> Matt Wallaert
>
> __________________________________
>
> Researcher
> Drexel Center for Weight Control
> under Dr. Michael Lowe
> Drexel University
> Bellet 314F
> [phone]
>
> Researcher
> Social Psychology
> under Dr. Andrew Ward
> Swarthmore College
> Papazian 313
> [phone]
>

[Peck, Jon]

We have updated the module tables.py on SPSS Developer Central (www.spss.com/devcentral) with some new functionality that we hope you will find useful.  This module requires at least SPSS 14 and the programmability Plug-In.  Other requirements are in the comments in the module.

The module has functions that can merge tables together.  These are useful, for example, in order to merge the significance tests from a Ctables column proportions test table into the main table.

There is now a function called censorLatest that can be used to blank out cells in a table based on statistics in the table.  A typical usage would be to remove statistics for categories with small cell counts.  The program below runs CTABLES and then removes statistics for the cells where the cell count is less than 100.

begin program.
import spss, tables
cmd=r"""CTABLES
  /TABLE num_item [MEAN, COUNT  MINIMUM, MAXIMUM] BY sex + store
  /SLABELS POSITION=ROW"""

tables.censorLatest(cmd=cmd, critvalue=100, neighborlist=[-1, 1, 2],direction='col')
end program.

Of course, this is not enough by itself to guarantee privacy, but it can be quite useful.

The censorLatest function is quite flexible.  Here is another example.  It blanks out cells in a correlation table when the significance level is greater than .01.

begin program.
import spss, tables
cmd=r"""CORRELATIONS  /VARIABLES=availblt avg_purc chckout"""

tables.censorLatest(cmd=cmd, critvalue=.01, critfield="Sig. (2-tailed)",
        testtype=">", neighborlist=[0,1,-1], direction='col')
end program.

You can read about other bells and whistles in the module documentation.

Regards,
Jon Peck