Results 1 to 5 of 5
  1. #1
    MarkMeer is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    4

    Question How to Import One-to-Many Relationships from Excel

    I'm wondering if there is a way to import a one-to-many relationship from an Excel spreadsheet into an Access database.



    Presently, the data is represented in Excel by a semi-colon separated list in the cells of a column, representing the OtM relationship.
    For example, a cell in the Excel spreadsheet might contain "3;20;23", indicating a relationship to another table's rows with IDs (primary keys) of 1, 20, and 23.
    NOTE: this appears to be the same format when an existing Access one-to-many relationship is exported from Access to Excel.

    I'm struggling to find a way to import this Excel data into Access.

    Can anyone kindly advise (or link to documentation) as to how I would setup my Access database, and format the Excel data to import such data into Access?

    Many thanks in advance.
    MM.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    import the master table data,
    then import the child table data.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Hmmmmm, you might be able to use VBA code, but you have not provided enough info to be able to be confident about using code.

    I have never seen a spreadsheet with this format.
    From the spreadsheet, what is the one table - name, structure, data??
    From the spreadsheet, what is the many table - name, structure, data??


    Quote Originally Posted by MarkMeer View Post
    a cell in the Excel spreadsheet might contain "3;20;23", indicating a relationship to another table's rows with IDs (primary keys) of 1, 20, and 23.
    To me "3;20;23" would represent the one table PK of 3 and the many table FK records of 20 and 23. But how do you know that PK record 3 is related to FK records 20 and 23??


    Maybe post the Access dB and spreadsheet?

  4. #4
    MarkMeer is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    4
    Thank you for the replies.

    The format I'm describing comes from exporting from a multi-select list item in Access.

    For example, you might have an "Instruments" table with rows Piano (pk:1), Guitar (pk:2), Bass (pk:3), Drums (pk:4)...

    In an Excel "Musicians" table, there is a field "InstrumentsPlayed", which might contain "1;3;4", indicating the musician plays piano, bass, and drums.

    As far as I can tell, the only way to import such data is to first explode the field into separate rows (essentially the data for a junction table). Was just hoping there was an easier way for Access to automatically parse multiple values in a field during import.

    NOTE: I realize now that I'm describing a many-to-many relationship (not one-to-many as the original post and thread title suggests), but the import challenge described is the same. Sorry for the confusion.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    OK, so I do not know what design you have for the Access dB, what the data looks like in the spreadsheet or if this is a recurring process.

    With these disclaimers, I would go the VBA code path.

    You could import the spreadsheet to a temporary table (the data is temporary, not the table), then use VBA to transfer the data to the appropriate tables.
    Or I might save the spreadsheet as a CSV text file, the read the file line by line, then writing to the appropriate tables.

    Not difficult to do, just takes an understanding of the relationships and the ability to code...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 09-08-2019, 03:18 PM
  2. Replies: 2
    Last Post: 02-28-2017, 01:34 AM
  3. Replies: 5
    Last Post: 07-18-2016, 11:10 AM
  4. Replies: 10
    Last Post: 06-16-2014, 08:37 AM
  5. Saved Import for Table with Relationships
    By garygoodguy in forum Import/Export Data
    Replies: 3
    Last Post: 04-21-2013, 08:07 PM

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
  •  
Other Forums: Microsoft Office Forums