Share via

I need to get data out from an ancient Access database (2007) into (preferably) a flat file I can work with.

Anonymous
2023-11-23T10:33:16+00:00

I'm working on an academic research project (MPhil stage) and have inherited a set of Access files (consisting of multiple tables) with 2000 names and associated data (home town, European city studied at, eventual profession (in some cases multiple) and eventual location (again, multiple), plus dates descriptive/free text fields, all culled from 17th century records. It was a monumental effort to originally collect all this, from archive sources all over the continent, and the original researcher has now retired.

I'd like to get the data out into a flat file in Excel (with as complete data association as possible) so that I can import particular data points into a social network analysis tool. I don't need it to be a working relational databse any more as such so I don't need programming, field protection etc to transfer across, just so long as the data comes across reasonably complete with linked data shown as columnar rather than in linked tables.

I'm 100% Mac/Office 365 and reasonably competent in Excel though there's no way as I understand it for me to fire up an old version of Access on my Macbook to run an export, and I'm not sure whether it can be opened in SQL either (is there a Mac version of that?).

Would appreciate any suggestions - even as to what's possible or the right direction to give the university ID department should it be something I can't do on the Mac, or requring specialist knowledge beyond that of a reasonably competent Excel user?

Microsoft 365 and Office | Access | For education | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-11-23T11:00:09+00:00

    Given your situation, one approach is to use Microsoft Excel itself to import the Access data and then manipulate it as needed. Here are the general steps:

    1. Importing Access Data into Excel:
      • Open Excel and go to the "Data" tab.
      • Choose "Get Data" or "Get External Data," and then select "From Access."
      • Locate and select your Access file. Follow the prompts to import the relevant tables or queries.
    2. Data Cleaning and Transformation:
      • Once the data is in Excel, you may need to clean it up. Ensure data types are correct, handle any missing values, and address inconsistencies.
      • Use Excel functions or Power Query to reshape the data if needed. This may involve merging columns, splitting data, or creating new columns for specific data points.
    3. Exporting to Flat File:
      • After organizing the data in Excel, you can save it as a flat file (e.g., CSV).
      • To do this, go to "File" > "Save As" and choose the CSV format. This will give you a file that you can use for social network analysis.
    4. Importing into Social Network Analysis Tool:
      • Import the CSV file into your chosen social network analysis tool. Most tools have a straightforward import process for CSV or Excel files.

    Regarding your concerns about Access and SQL on a Mac, Microsoft Access is not available for Mac, and using SQL Server directly might be challenging. However, you can use tools like DBVisualizer, DBeaver, or SQLite to work with SQL databases on a Mac. For this specific scenario, the Excel approach seems more straightforward.

    4 people found this answer helpful.
    0 comments
  2. Anonymous
    2023-11-23T11:40:38+00:00

    Thank you this is really helpful, will have a go at it tonight.

    0 comments
  3. Anonymous
    2023-11-23T12:11:00+00:00

    Unfortunately there appears to be no driver for the Mac version of Excel to open Access files.

    It sends me off to this page:

    https://support.microsoft.com/en-us/office/odbc-drivers-that-are-compatible-with-excel-for-mac-9fa6bc7f-d19e-4f7f-9be4-92e85c77d712?ui=en-us&rs=en-gb&ad=gb

    In turn the option seems to be this - https://www.actualtech.com/product_access.php

    before I fork out cash would you happen to know if this is likely to achieve what I want in terms of bringing it into a single Excel file? Or perhaps there's a Microsoft option after all that's not obvious?

    0 comments
  4. ScottGem 68,775 Reputation points Volunteer Moderator
    2023-11-23T12:52:46+00:00

    MAC OS does not support Access. So there is no way on a MAC to use the accdb/mdb file.

    I would suggest finding a Windows based PC to do the import or someone who will do the import for you.

    0 comments
  5. George Hepworth 22,220 Reputation points Volunteer Moderator
    2023-11-23T14:26:26+00:00

    Your IT department should have tools and expertise to help you export the data from an accdb or mdb to either .csv or .xlsx files. It's not difficult to do if you have the proper applications and experience.

    Also any developer who has a Windows computer with Access and Excel installed on it could do this for you. You didn't specify how many tables you have exactly, but with only 2,000 records in the names table, this is a pretty small amount of data to export.

    So, you'll either need to install a Windows emulator, like Parallels, on your Mac and obtain a Windows license and an M365 license to do the export yourself, or find someone to do it. If your IT department can't, you could search for Access developers who would complete the export.

    The main issue, I would think, is the confidentiality or proprietary nature of the data itself. Professional developers would sign an NDA before doing it.

    1 person found this answer helpful.
    0 comments