+ Reply to Thread
Results 1 to 10 of 10

Thread: Exporting a MS Access database into SPSS

  1. #1
    Points: 812, Level: 15
    Level completed: 12%, Points required for next Level: 88

    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exporting a MS Access database into SPSS




    Iím new at using SPSS but Iíve been reading a wonderful book with basic statistic principles and so far have been getting wonderful results.

    Right now, I'm stuck though

    I've got a database with different tables in MS Access 2007 which Iíve been using for the last 5 months. This database contains loads of medical information, and itís now required to analyse all this data (epidemiological analysis) in SPSS v19.

    My database contains multiple tables but I was just wondering if I have to treat all information separately? Will I be able to do correlations if all the information is in different tables? Or, do I have to put all the information together in a single table?

    I would be so pleased if you could advise what the best way is to analyse all this information. Should I put all the information into a single large table and then export to SPSS?

    BTW, I exported a single table into SPSS and it worked wonderfully!

    Any reply is a supply. Thanks so much!

  2. #2
    Points: 4,082, Level: 40
    Level completed: 66%, Points required for next Level: 68

    Posts
    217
    Thanks
    6
    Thanked 26 Times in 25 Posts

    Re: Exporting a MS Access database into SPSS

    It depends on how your Access tables are set up. I had a database that had multiple tables with a single key variable (so you could follow a single case through across multiple tables). Matching those multiple Access tables up into a single SPSS file was a nightmare. I eventually abandonded the effort. It was dreadful.

    In my experience, it would be best to create a new table in Access that combines the necessary tables/fields. Then import that new table into SPSS.

    If you run into the error message about having too many fields, make sure you "Compact and Repair" the database. The 255 field limit includes any fields you create and delete or create and change (meaning it counts those fields twice). Compacting will reset the field counts so that each field is only counted once.

    If you still have more than 255 fields, then you're going to have to get creative about importing cases. Hopefully, you won't have to deal with that issue.

  3. #3
    Points: 8, Level: 1
    Level completed: 15%, Points required for next Level: 42

    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting a MS Access database into SPSS

    Quote Originally Posted by mariosa View Post
    I’m new at using SPSS but I’ve been reading a wonderful book with basic statistic principles and so far have been getting wonderful results.


    BTW, I exported a single table into SPSS and it worked wonderfully!

    Any reply is a supply. Thanks so much!
    Would mind explaining how you exported to SPSS? I am having trouble with a table in Access, it has 138 fields, some of them with text and I when I export into an excel sheet that I then save (inside Excel) as text file, I get a text file with junk in it.
    I only how to import foreign data as text files in SPSS that have the format of a spreadsheet.
    Thanks

  4. #4
    Points: 8, Level: 1
    Level completed: 15%, Points required for next Level: 42

    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting a MS Access database into SPSS

    Quote Originally Posted by p.mulongeni View Post
    Would mind explaining how you exported to SPSS? I am having trouble with a table in Access, it has 138 fields, some of them with text and I when I export into an excel sheet that I then save (inside Excel) as text file, I get a text file with junk in it.
    I only how to import foreign data as text files in SPSS that have the format of a spreadsheet.
    Thanks
    I have a found a way to export from ACCESS 2007 my large table referred to above into spss20:
    1. Right click on the Table and choose export to xlsx. Please do NOT click on any boxes that ask to preserve formating and layout.
    2. In SPSS go to open data, choose .xls,xlsx from they type of file. Say Ok. The datafile will open. You can do a quick check to see if the number of records is the same as in the original access table and by Goly it is. However, the order of the records may not be the same as in the access table i.e record with Primarykey number 1 may actually sit at row not equal to 1 in the spss datafile.

    This is the only way I have figured it out for this particular data file which is huge.

  5. #5
    Points: 8, Level: 1
    Level completed: 15%, Points required for next Level: 42

    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting a MS Access database into SPSS

    Quote Originally Posted by p.mulongeni View Post
    I have a found a way to export from ACCESS 2007 my large table referred to above into spss20:
    1. Right click on the Table and choose export to xlsx. Please do NOT click on any boxes that ask to preserve formating and layout.
    2. In SPSS go to open data, choose .xls,xlsx from they type of file. Say Ok. The datafile will open. You can do a quick check to see if the number of records is the same as in the original access table and by Goly it is. However, the order of the records may not be the same as in the access table i.e record with Primarykey number 1 may actually sit at row not equal to 1 in the spss datafile.

    This is the only way I have figured it out for this particular data file which is huge.
    POSTSCRIPTUM
    >Error. Command name: GET DATA
    >(2063
    I obtained this error in SPSS when I tried to open the .xlsx file.
    I saved it then as .xls and it worked.
    Does someone know how to get access 2007 to export table as .xls and not .xlsx?
    Thanks

  6. #6
    Points: 4,082, Level: 40
    Level completed: 66%, Points required for next Level: 68

    Posts
    217
    Thanks
    6
    Thanked 26 Times in 25 Posts

    Re: Exporting a MS Access database into SPSS

    Quote Originally Posted by p.mulongeni View Post
    POSTSCRIPTUM
    >Error. Command name: GET DATA
    >(2063
    I obtained this error in SPSS when I tried to open the .xlsx file.
    I saved it then as .xls and it worked.
    Does someone know how to get access 2007 to export table as .xls and not .xlsx?
    Thanks
    Open the .xlsx file and "Save As" an .xls file instead (97-2003 format). Then you can simply open in SPSS if you like.

  7. #7
    R must die
    Points: 24,068, Level: 94
    Level completed: 72%, Points required for next Level: 282
    Awards:
    Activity Award
    noetsi's Avatar
    Posts
    4,404
    Thanks
    261
    Thanked 697 Times in 669 Posts

    Re: Exporting a MS Access database into SPSS

    Can't you simply export them as a CSV file and pull it into SPSS that way?
    My statements reflect my own experience/education/memory only and could well be wrong. Julia will still beat the crap out of R

  8. #8
    Points: 4,082, Level: 40
    Level completed: 66%, Points required for next Level: 68

    Posts
    217
    Thanks
    6
    Thanked 26 Times in 25 Posts

    Re: Exporting a MS Access database into SPSS

    I believe you can export from Excel as CSV, but it's not necessary. SPSS reads Excel files. You don't even need to "import" it. Just open the file. SPSS will automatically convert for you. Like opening a WordPerfect file in Word. Just select the "Open Another Type of File" option.

    Access does not cleanly export to CSV. It will export to Excel, Word, PDF, or TXT, but others are problematic. So the easy way is to select the "External Data" tab in your Access db, Export to Excel, and the open the Excel file in SPSS. At least that's the way I've done it previously with success.

  9. #9
    Points: 3, Level: 1
    Level completed: 5%, Points required for next Level: 47

    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exclamation Re: Exporting a MS Access database into SPSS

    In Access you can create fields where there are dropdown boxes to select a response to the question/topic (i.e., Breakfast: egg or bacon or toast). You can also have dropdown boxes where you can select multiple responses (i.e., Breakfast: egg and bacon and toast and coffee). How do these fields get stored - are they easy to transport into SPSS?

    E.g. in SPSS you would have to manually code the variable egg (1), bacon (2), toast (3) etc. How does Access code variables from drop down boxes?

    Thanks

  10. #10
    Points: 4,082, Level: 40
    Level completed: 66%, Points required for next Level: 68

    Posts
    217
    Thanks
    6
    Thanked 26 Times in 25 Posts

    Re: Exporting a MS Access database into SPSS


    Access stores information for drop down boxes in a (usually separate) lookup table. When you import it to SPSS, it *probably* would import it as a string variable and just record the answer selected. I believe that's how it worked last time I did it. Yes, you'd need to set the values separately (1, 2, 3).

+ Reply to Thread

           




Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts






Advertise on Talk Stats