Importing data and complex data structures

[gilbertvb3]

Having trouble designing a Book Collection database with Access 2013. I am importing data from an excel spreadsheet that holds my book collection data. I already built the main structure of the database. There are many tables and relationships that are very straight forward and easy to work with. However, there are a few tables that have the potential of making my life miserable. For example, the Authors table is more complex. Authors is a one-to-many relationship with a joining table to link them up. Each book has the capacity to hold more than one author through the join table. The excel file that holds the book data lists each author in a single cell with the author separated by semicolons. Is there an easy way of importing the data, split the authors separated by the semicolon, and putting them in the authors table while not being relegated to reenter data for 2300 books?

Basic Table Structure:



Books
Authors
BookAuthorJOIN

[June7]

Short answer, no. This will probably require sophisticated code.

One method would involve setting links to the Excel sheets and running a series of INSERT and UPDATE sql actions.

Another method opens Excel object and manipulates the data for import. Review this site for ideas http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

Further complicated if you are using autonumber as PK for linking author and book records.

[keviny04]

Having trouble designing a Book Collection database with Access 2013. I am importing data from an excel spreadsheet that holds my book collection data. I already built the main structure of the database. There are many tables and relationships that are very straight forward and easy to work with. However, there are a few tables that have the potential of making my life miserable. For example, the Authors table is more complex. Authors is a one-to-many relationship with a joining table to link them up. Each book has the capacity to hold more than one author through the join table. The excel file that holds the book data lists each author in a single cell with the author separated by semicolons. Is there an easy way of importing the data, split the authors separated by the semicolon, and putting them in the authors table while not being relegated to reenter data for 2300 books?

Basic Table Structure:

Books
Authors
BookAuthorJOIN

Like June7 said, this involves advanced topics, although the actual amount of work may be quite small. Small enough that I feel I can describe the steps below to give you an idea:

Let's say your Excel sheet has these columns: BookTitle, Authors, Publisher, DatePublished, Pages. As you said, the Authors column may have multiple authors separated by semicolons:



First, import your Excel sheet to Access exactly as it is, and name it the "Books Excel" table. Add an autonumber primary key field named BookID.

Second, copy this table and paste it as a new table named "Books". Open it in table view and remove the Authors field. Now you have a normalized Books table:




Third, from the "Books Excel" table, you need to create a temporary book-author junction table; which we call BookAuthorJoinTemp. You need the following code to do this:

Sub Fill_BookAuthorJoinTemp_Table()
    Dim db As Database, r As Recordset, r2 As Recordset, i As Integer, n As Integer
    Dim AuthorsArray As Variant
    
    Set db = DBEngine(0)(0)
    Set r = db.OpenRecordset("Books Excel")
    Set r2 = db.OpenRecordset("BookAuthorJoinTemp")
    
    r.MoveFirst
    Do While Not r.EOF
        AuthorsArray = Split(r!Authors, ";")
        n = UBound(AuthorsArray)
        For i = 0 To n
            r2.AddNew
            r2!BookID = r!BookID
            r2!AuthorName = AuthorsArray(i)
            r2.Update
        Next i
        r.MoveNext
    Loop
End Sub

The above code copies only the BookID and Authors fields from the "Book Excel" table to the BookAuthorJoinTemp table. When the Authors field has mutiple authors, the code splits them up and put the authors into separate rows. The Split() function above looks for semicolons in the Authors field and performs the split-up. Result:




Fourth, you create a blank, normalized Authors table (fields: AuthorID - autonumber primary key; AuthorName - text).

Fifth, you populate the Authors table by running an action query on the BookAuthorJoinTemp table. The SQL is:

INSERT INTO Authors ( AuthorName )
SELECT BookAuthorJoinTemp.AuthorName
FROM BookAuthorJoinTemp
GROUP BY BookAuthorJoinTemp.AuthorName;

This SQL creates a unique (i.e. non-repeating) list of author names, and inserts them into the Authors table. The result is a populated, normalized Authors table:



The sixth step: create a blank, normalized BookAuthorJoin table (fields: BookID - number; AuthorID - number; primary key: BookID, AuthorID). Note that neither field can be autonumber, since you will be copying values from other tables to them.

The 7th and final step: populate the BookAuthorJoin table (fields BookID, AuthorID) with data from the BookAuthorJoinTemp table (fields BookID, AuthorName). Essentially, you need to convert AuthorName to its corresponding AuthorID value. The code below copies the BookID value, uses the Dlookup() function to look up the AuthorID value for each AuthorName and puts it into the new table:

Sub Fill_BookAuthorJoin_Table()
    Dim db As Database, r As Recordset, r2 As Recordset
    Set db = DBEngine(0)(0)
    Set r = db.OpenRecordset("BookAuthorJoinTemp")
    Set r2 = db.OpenRecordset("BookAuthorJoin")
    r.MoveFirst
    Do While Not r.EOF
        r2.AddNew
        r2!BookID = r!BookID
        r2!AuthorID = DLookup("[AuthorID]", "Authors", "[AuthorName]='" & r!AuthorName & "'")
        r2.Update
        r.MoveNext
    Loop
End Sub

The result is a populated, normalized BookAuthorJoin table:



Now, all 3 tables - Books, Authors, and BookAuthorJoin - are normalized and properly related.

Here is the database with the final result: Book Collection.zip