Problem with Database Wizard

[Allen Cornelius]

SPSS gurus,

I am trying to import data from an Access database using the SPSS Database
Wizard and am having problems. I select Open Database, New Query, and from
ODBC Data Sources select MS Access Database. I then browse to the database
containing the data I want to import. The next screen lists all the tables
and queries in that database, but when I try to select a table or query to
import, I get a message "Table has no columns and will not be shown". I
know there are fields in the Access database.

Any help or ideas would be appreciated.

Allen

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Albert-Jan Roskam]

Hi,

Do you have (at least) read rights for the table that
you want to access? You may have to consult your
database manager.

Cheers!!
Albert-Jan


--- Allen Cornelius <[hidden email]> wrote:

> SPSS gurus,
>
> I am trying to import data from an Access database
> using the SPSS Database
> Wizard and am having problems. I select Open
> Database, New Query, and from
> ODBC Data Sources select MS Access Database. I then
> browse to the database
> containing the data I want to import. The next
> screen lists all the tables
> and queries in that database, but when I try to
> select a table or query to
> import, I get a message "Table has no columns and
> will not be shown". I
> know there are fields in the Access database.
>
> Any help or ideas would be appreciated.
>
> Allen
>
> =====================
> To manage your subscription to SPSSX-L, send a
> message to
> [hidden email] (not to SPSSX-L), with no
> body text except the
> command. To leave the list, send the command
> SIGNOFF SPSSX-L
> For a list of commands to manage subscriptions, send
> the command
> INFO REFCARD
>

      ____________________________________________________________________________________
Looking for last minute shopping deals?
Find them fast with Yahoo! Search.  http://tools.search.yahoo.com/newsearch/category.php?category=shopping

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Allen Cornelius]

Thanks to all the folks who responded on and off list. I think I figured it
out - it was a moronic file naming issue. The Access file name was of the
form "xxxx_yyyy_3.0_xx.mdb. Apparently, SPSS initially reads the file OK,
but then can't import the data. Changing the file name to something simpler
seems to have fixed the problem. Lesson learned.

Allen

>SPSS gurus,
>
>I am trying to import data from an Access database using the SPSS Database
>Wizard and am having problems. I select Open Database, New Query, and from
>ODBC Data Sources select MS Access Database. I then browse to the database
>containing the data I want to import. The next screen lists all the tables
>and queries in that database, but when I try to select a table or query to
>import, I get a message "Table has no columns and will not be shown". I
>know there are fields in the Access database.
>
>Any help or ideas would be appreciated.
>
>Allen

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Albert-Jan Roskam]

Hi all,

I am making some code that checks whether date fields
(ddmmyy) are correct. First, it scans for
alphanumerical signs, then for impossible dates such
as 66-02-74. I have a problem with flagging illegal
leap days. The date 290200 is incorrect is '00' stands
for 2000 (because it's a millennium), but correct if
is stands for 1900 (because it's divisable by four).

In the rather large chunk of code below, the following
line is probably not correct, but I don't see why not:
* if month = 2, and divisable by 4, and not year =
2000 ==> day fields > 28 are incorrect.
else if (number(substr(!date_var,3,2),n2) = 2 and
(mod((number(substr(#ddmmyyyy,5,4) ,f4)/4), 1) = 0 and
substr(#ddmmyyyy,5,4) ne "2000")).
+ if ( not(
range(number(substr(!date_var,1,2),n2),1,28)) ) flag2
= 1.
*(flag2 = 1 indicates an illegal field)

Any suggestions? I considered using Spss DATE format,
but didn't use that because it'd yield many warnings
or maybe even errors. A Python solution would be
welcome, though. (I can imagine that somebody already
created a module for this).

Cheers!!
Albert-Jan


* test data.
data list free / mydate (a6) comment (a10).
begin data
111202 correct
a11202 incorrect
660202 incorrect
290200 incorrect*
end data.

* macro to check dates.
define checkdate (date_var = !charend ('/') /
year_prefix = !tokens(1)).

dataset copy !date_var.
dataset activate !date_var window = front.

compute flag = 0.
compute flag2 = 0.
compute casenum = $casenum.

**scan for alphanumerical signs.
loop #i =1 to 6.
+ if not(any(substr(!date_var,#i,1),'0', '1', '2',
'3', '4', '5', '6', '7', '8', '9')) flag = 1.
end loop.
exe.

string #ddmmyyyy (a8).
compute #ddmmyyyy = concat(substr(!date_var,1,4),
!quote(!year_prefix), substr(!date_var,5,2) ).

** if there are no alphanumerical signs, then mark
illegal date fields (eg. 31 feb, or 26-15-00).
do if flag = 0.

* day fields - 31-day months.
do if any(number(substr(!date_var,3,2),n2),1, 3,  5,
7, 8, 10, 12).
if ( not(
range(number(substr(!date_var,1,2),n2),1,31)) ) flag2
= 1.

* day fields - february in leap year.
* if month = 2, and divisable by 4, and not year =
2000 ==> day fields > 28 are incorrect.
else if (number(substr(!date_var,3,2),n2) = 2 and
(mod((number(substr(#ddmmyyyy,5,4) ,f4)/4), 1) = 0 and
substr(#ddmmyyyy,5,4) ne "2000")).

if ( not(
range(number(substr(!date_var,1,2),n2),1,28)) ) flag2
= 1.

* day fields - february in non-leap year.
* if month = 2, and NOT divisable by 4, and year =
2000 ==> day fields > 28 are incorrect.
else if (number(substr(!date_var,3,2),n2) = 2 and
(mod((number(substr(#ddmmyyyy,5,4) ,f4)/4), 1) ne 0 or
substr(#ddmmyyyy,5,4) = "2000")).

if ( not(
range(number(substr(!date_var,1,2),n2),1,29)) ) flag2
= 1.

* day fields - 30-day months.
else if (any(number(substr(!date_var,3,2),n2), 4, 6,
9, 11)).
if ( not(
range(number(substr(!date_var,1,2),n2),1,30)) ) flag2
= 1.

*month fields.
else if ( not(
range(number(substr(!date_var,3,2),n2),1,12)) ).
compute flag2 = 1.

* year fields.
else if ( not(
range(number(substr(!date_var,5,2),n2),0,99)) ).
compute flag2 = 1.
end if.
end if.
exe.
compute flag3 = max (flag, flag2).
exe.

filter by flag3.
echo !quote(!concat("*** The following date fields of
variable ", !date_var, " contained error(s):")).
list casenum !date_var.
filter off.

formats flag flag2 flag3 (f1).
value labels flag flag2 flag3 1 'Impossible date
value(s)'  0 'Correct date field' .
variable label flag "illegal sign in date"  / flag2
"illegal number in date, e.g. 66-02-1974" / flag3
"illegal date".

fre flag flag2 flag3.

!enddefine.

* macro call.
checkdate date_var = mydate / year_prefix = 20.



      ____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile.  Try it now.  http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Peck, Jon]

Here is a simple Python program that creates a new variable based on input string variable mydate.  Output values will be sysmis if the date is invalid.  It requires the spssdata and extendedTransforms modules from SPSS Developer Central (www.spss.com/devcentral) and SPSS 15 or later plus plugin.  Many variations on the pattern string below (%d%m%y) are possible.

begin program.
import spss, spssdata, extendedTransforms
from spssdata import vdef

curs = spssdata.Spssdata(accessType='w')
curs.append(vdef("converteddate", vfmt=("DATE", 12)))
curs.commitdict()
for case in curs:
  cvt = extendedTransforms.strtodatetime(case.mydate, "%d%m%y")
  curs.casevalues([cvt])
curs.CClose()
end program.

The program assumes that two-digit years refer to the 21st century.

HTH,
Jon Peck

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Albert-jan Roskam
Sent: Wednesday, February 20, 2008 1:37 AM
To: [hidden email]
Subject: [SPSSX-L] Leap years

Hi all,

I am making some code that checks whether date fields
(ddmmyy) are correct. First, it scans for
alphanumerical signs, then for impossible dates such
as 66-02-74. I have a problem with flagging illegal
leap days. The date 290200 is incorrect is '00' stands
for 2000 (because it's a millennium), but correct if
is stands for 1900 (because it's divisable by four).

In the rather large chunk of code below, the following
line is probably not correct, but I don't see why not:
* if month = 2, and divisable by 4, and not year =
2000 ==> day fields > 28 are incorrect.
else if (number(substr(!date_var,3,2),n2) = 2 and
(mod((number(substr(#ddmmyyyy,5,4) ,f4)/4), 1) = 0 and
substr(#ddmmyyyy,5,4) ne "2000")).
+ if ( not(
range(number(substr(!date_var,1,2),n2),1,28)) ) flag2
= 1.
*(flag2 = 1 indicates an illegal field)

Any suggestions? I considered using Spss DATE format,
but didn't use that because it'd yield many warnings
or maybe even errors. A Python solution would be
welcome, though. (I can imagine that somebody already
created a module for this).

Cheers!!
Albert-Jan


* test data.
data list free / mydate (a6) comment (a10).
begin data
111202 correct
a11202 incorrect
660202 incorrect
290200 incorrect*
end data.

* macro to check dates.
define checkdate (date_var = !charend ('/') /
year_prefix = !tokens(1)).

dataset copy !date_var.
dataset activate !date_var window = front.

compute flag = 0.
compute flag2 = 0.
compute casenum = $casenum.

**scan for alphanumerical signs.
loop #i =1 to 6.
+ if not(any(substr(!date_var,#i,1),'0', '1', '2',
'3', '4', '5', '6', '7', '8', '9')) flag = 1.
end loop.
exe.

string #ddmmyyyy (a8).
compute #ddmmyyyy = concat(substr(!date_var,1,4),
!quote(!year_prefix), substr(!date_var,5,2) ).

** if there are no alphanumerical signs, then mark
illegal date fields (eg. 31 feb, or 26-15-00).
do if flag = 0.

* day fields - 31-day months.
do if any(number(substr(!date_var,3,2),n2),1, 3,  5,
7, 8, 10, 12).
if ( not(
range(number(substr(!date_var,1,2),n2),1,31)) ) flag2
= 1.

* day fields - february in leap year.
* if month = 2, and divisable by 4, and not year =
2000 ==> day fields > 28 are incorrect.
else if (number(substr(!date_var,3,2),n2) = 2 and
(mod((number(substr(#ddmmyyyy,5,4) ,f4)/4), 1) = 0 and
substr(#ddmmyyyy,5,4) ne "2000")).

if ( not(
range(number(substr(!date_var,1,2),n2),1,28)) ) flag2
= 1.

* day fields - february in non-leap year.
* if month = 2, and NOT divisable by 4, and year =
2000 ==> day fields > 28 are incorrect.
else if (number(substr(!date_var,3,2),n2) = 2 and
(mod((number(substr(#ddmmyyyy,5,4) ,f4)/4), 1) ne 0 or
substr(#ddmmyyyy,5,4) = "2000")).

if ( not(
range(number(substr(!date_var,1,2),n2),1,29)) ) flag2
= 1.

* day fields - 30-day months.
else if (any(number(substr(!date_var,3,2),n2), 4, 6,
9, 11)).
if ( not(
range(number(substr(!date_var,1,2),n2),1,30)) ) flag2
= 1.

*month fields.
else if ( not(
range(number(substr(!date_var,3,2),n2),1,12)) ).
compute flag2 = 1.

* year fields.
else if ( not(
range(number(substr(!date_var,5,2),n2),0,99)) ).
compute flag2 = 1.
end if.
end if.
exe.
compute flag3 = max (flag, flag2).
exe.

filter by flag3.
echo !quote(!concat("*** The following date fields of
variable ", !date_var, " contained error(s):")).
list casenum !date_var.
filter off.

formats flag flag2 flag3 (f1).
value labels flag flag2 flag3 1 'Impossible date
value(s)'  0 'Correct date field' .
variable label flag "illegal sign in date"  / flag2
"illegal number in date, e.g. 66-02-1974" / flag3
"illegal date".

fre flag flag2 flag3.

!enddefine.

* macro call.
checkdate date_var = mydate / year_prefix = 20.



      ____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile.  Try it now.  http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Richard Ristow]

At 03:36 AM 2/20/2008, Albert-jan Roskam wrote:

>I am making some code that checks whether date fields (ddmmyy) are correct.

 From your code, it looks like these are *string* variables. I'll assume that.

>I have a problem with flagging illegal leap days. The date 290200 is
>incorrect if '00' stands for 2000 (because it's a millennium), but
>correct if it stands for 1900 (because it's divisible by four).

To start with, it's other way around. A year is a leap year if,
. It's divisible by 4, but *not* 100, OR
. It's divisible by 400.

2000 was a leap year; 1900 wasn't.

>The following line is probably not correct, but I don't see why not:

[...]
* day fields - february in leap year.         .
* if month = 2, and [year is] divisible by 4, .
* and not year = 2000                         .
* ==> day fields > 28 are incorrect.          .
else if (number(substr(!date_var,3,2),n2) = 2
     and (mod((number(substr(#ddmmyyyy,5,4) ,f4)/4), 1) = 0
     and  substr(#ddmmyyyy,5,4) ne "2000")).
+ if ( not(range(number(substr(!date_var,1,2),n2),1,28)))
     flag2 = 1.
*(flag2 = 1 indicates an illegal field)       .
[...]

I see two bugs:
. It takes leap years as all years divisible by 4, except 2000. As
noted, 2000 was a leap year; 1900 wasn't.
. It tests for days 1-28 instead of 1-29; probably an oversight.

I'm not running a test, yet; see how far this takes you.

-Best of luck,
  Richard

P.S.  If efficiency is an issue,
. You make a copy of the dataset and a full data pass for every
variable. You could change this to a macro loop to check all your
date variables in one pass.
. You have two 'exe.' statements that'll slow your code down, to no purpose.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Albert-Jan Roskam]

Hi Jon and others who replied,

Thanks for your responses! My algorithm for
determining (non) leap years was wrong. A year that is
divisable by 400 is a leap year. I did not know that.

Python. Unfortunately, we're still working with SPSS
14.01 here, so I can't use the code you suggested.
Thanks anyway! Hope we'll upgrade soon, but I heard it
was rather costly. I'll also have a look at the
isleapyear method of the LocaleHTMLCalendar Python
class. Maybe that will work until I have v15 or
higher.

Cheers!!
Albert-Jan



--- "Peck, Jon" <[hidden email]> wrote:

> Here is a simple Python program that creates a new
> variable based on input string variable mydate.
> Output values will be sysmis if the date is invalid.
>  It requires the spssdata and extendedTransforms
> modules from SPSS Developer Central
> (www.spss.com/devcentral) and SPSS 15 or later plus
> plugin.  Many variations on the pattern string below
> (%d%m%y) are possible.
>
> begin program.
> import spss, spssdata, extendedTransforms
> from spssdata import vdef
>
> curs = spssdata.Spssdata(accessType='w')
> curs.append(vdef("converteddate", vfmt=("DATE",
> 12)))
> curs.commitdict()
> for case in curs:
>   cvt =
> extendedTransforms.strtodatetime(case.mydate,
> "%d%m%y")
>   curs.casevalues([cvt])
> curs.CClose()
> end program.
>
> The program assumes that two-digit years refer to
> the 21st century.
>
> HTH,
> Jon Peck
>
> -----Original Message-----
> From: SPSSX(r) Discussion
> [mailto:[hidden email]] On Behalf Of
> Albert-jan Roskam
> Sent: Wednesday, February 20, 2008 1:37 AM
> To: [hidden email]
> Subject: [SPSSX-L] Leap years
>
> Hi all,
>
> I am making some code that checks whether date
> fields
> (ddmmyy) are correct. First, it scans for
> alphanumerical signs, then for impossible dates such
> as 66-02-74. I have a problem with flagging illegal
> leap days. The date 290200 is incorrect is '00'
> stands
> for 2000 (because it's a millennium), but correct if
> is stands for 1900 (because it's divisable by four).
>
> In the rather large chunk of code below, the
> following
> line is probably not correct, but I don't see why
> not:
> * if month = 2, and divisable by 4, and not year =
> 2000 ==> day fields > 28 are incorrect.
> else if (number(substr(!date_var,3,2),n2) = 2 and
> (mod((number(substr(#ddmmyyyy,5,4) ,f4)/4), 1) = 0
> and
> substr(#ddmmyyyy,5,4) ne "2000")).
> + if ( not(
> range(number(substr(!date_var,1,2),n2),1,28)) )
> flag2
> = 1.
> *(flag2 = 1 indicates an illegal field)
>
> Any suggestions? I considered using Spss DATE
> format,
> but didn't use that because it'd yield many warnings
> or maybe even errors. A Python solution would be
> welcome, though. (I can imagine that somebody
> already
> created a module for this).
>
> Cheers!!
> Albert-Jan
>
>
> * test data.
> data list free / mydate (a6) comment (a10).
> begin data
> 111202 correct
> a11202 incorrect
> 660202 incorrect
> 290200 incorrect*
> end data.
>
> * macro to check dates.
> define checkdate (date_var = !charend ('/') /
> year_prefix = !tokens(1)).
>
> dataset copy !date_var.
> dataset activate !date_var window = front.
>
> compute flag = 0.
> compute flag2 = 0.
> compute casenum = $casenum.
>
> **scan for alphanumerical signs.
> loop #i =1 to 6.
> + if not(any(substr(!date_var,#i,1),'0', '1', '2',
> '3', '4', '5', '6', '7', '8', '9')) flag = 1.
> end loop.
> exe.
>
> string #ddmmyyyy (a8).
> compute #ddmmyyyy = concat(substr(!date_var,1,4),
> !quote(!year_prefix), substr(!date_var,5,2) ).
>
> ** if there are no alphanumerical signs, then mark
> illegal date fields (eg. 31 feb, or 26-15-00).
> do if flag = 0.
>
> * day fields - 31-day months.
> do if any(number(substr(!date_var,3,2),n2),1, 3,  5,
> 7, 8, 10, 12).
> if ( not(
> range(number(substr(!date_var,1,2),n2),1,31)) )
> flag2
> = 1.
>
> * day fields - february in leap year.
> * if month = 2, and divisable by 4, and not year =
> 2000 ==> day fields > 28 are incorrect.
> else if (number(substr(!date_var,3,2),n2) = 2 and
> (mod((number(substr(#ddmmyyyy,5,4) ,f4)/4), 1) = 0
> and
> substr(#ddmmyyyy,5,4) ne "2000")).
>
> if ( not(
> range(number(substr(!date_var,1,2),n2),1,28)) )
> flag2
> = 1.
>
> * day fields - february in non-leap year.
> * if month = 2, and NOT divisable by 4, and year =
> 2000 ==> day fields > 28 are incorrect.
> else if (number(substr(!date_var,3,2),n2) = 2 and
> (mod((number(substr(#ddmmyyyy,5,4) ,f4)/4), 1) ne 0
> or
> substr(#ddmmyyyy,5,4) = "2000")).
>
> if ( not(
> range(number(substr(!date_var,1,2),n2),1,29)) )
> flag2
> = 1.
>
> * day fields - 30-day months.
> else if (any(number(substr(!date_var,3,2),n2), 4, 6,
> 9, 11)).
> if ( not(
> range(number(substr(!date_var,1,2),n2),1,30)) )
> flag2
> = 1.
>
> *month fields.
> else if ( not(
> range(number(substr(!date_var,3,2),n2),1,12)) ).
> compute flag2 = 1.
>
> * year fields.
> else if ( not(
> range(number(substr(!date_var,5,2),n2),0,99)) ).
> compute flag2 = 1.
> end if.
> end if.
> exe.
> compute flag3 = max (flag, flag2).
> exe.
>
> filter by flag3.
> echo !quote(!concat("*** The following date fields
> of
> variable ", !date_var, " contained error(s):")).
> list casenum !date_var.
> filter off.
>
> formats flag flag2 flag3 (f1).
> value labels flag flag2 flag3 1 'Impossible date
> value(s)'  0 'Correct date field' .
> variable label flag "illegal sign in date"  / flag2
> "illegal number in date, e.g. 66-02-1974" / flag3
> "illegal date".
>
> fre flag flag2 flag3.
>
> !enddefine.
>
> * macro call.
> checkdate date_var = mydate / year_prefix = 20.
>
>
>
>
>

____________________________________________________________________________________
> Be a better friend, newshound, and
> know-it-all with Yahoo! Mobile.  Try it now.
>
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
>
> =====================
>
=== message truncated ===



      ____________________________________________________________________________________
Never miss a thing.  Make Yahoo your home page.
http://www.yahoo.com/r/hs

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Richard Ristow]

At 11:05 AM 2/21/2008, Albert-jan Roskam wrote, off-list:

>Thank you for your reply! I'll look into it.

Good! Good luck to you, with it.

>And yep, a macro loop would make it a lot more efficient. I am
>running it on two datasets of 180k and 300k records, so it'd save a
>lot of time!

A second thought, that struck me since I wrote: A DO REPEAT would
give the same effect, and be a good deal easier to write.

-Good luck,
  Richard

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD