Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2011
    Posts
    23

    Unanswered: Merging Data from Excel with Table in Access

    I have an Excel ss that has data that needs to be merged into a table in Access.

    I have created the excel ss to match all colums and only 4 columns have data: Primary Key, + 3 new fields that are added to the table.

    The data on the Excel ss is new data that is being collected and needs to be added to the record it corresponds with in the table (ID# are unique).

    When I try to Import, i get an error saying "Unable to append all data" "0 Records were deleted, 109 records were lost due to key violations."

    There are 109 records I'm importing. What am I doing wrong?

    Thanks for the help

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This probably means that you try to import rows of data with ID values duplicating existing values into the table. Also, if you have set the data type of the ID colummn to AutoNumber, do not try to import it.

    A more precise answer would require to need how you import the data from Excel to Access, which you do not explain.
    Have a nice day!

  3. #3
    Join Date
    Aug 2011
    Posts
    23
    Quote Originally Posted by Sinndho View Post
    This probably means that you try to import rows of data with ID values duplicating existing values into the table. Also, if you have set the data type of the ID colummn to AutoNumber, do not try to import it.

    A more precise answer would require to need how you import the data from Excel to Access, which you do not explain.
    Yes I have about 423 rows in an excel spreadsheet that contain three new fields (columns) of data that is not currently in the database. I created the three new fields in the table and there is a record in the database for each of the 423 rows in the excel spreadsheet. The data type of the ID is Autonumber, so is there a way to bring that data from excel into Access with this field being autonumber and get the three new fields of data into their current record?

    I have never imported data from Excel to Access with this DB so your secord question I don't have an explanation for.

    Thanks

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If I understand correctly, you want to update three columns of an existing table with data you want to import from an Excel spreadsheet. The table has an Autonumber column (ID) that identifies each of its row, while there is a column in the spreadsheet with an ID number also identifying each row. The update must be performed based on the relationship: Table.ID = Sheet.ID.

    1. Import (link) the sheet into Access. You can use the interface (in Access 2003: Files --> Get External Data --> Link) or you can use the following code:
    Code:
    Function ImportSheet(ByVal FileName As String, Optional ByVal HasFieldNames As Boolean)
    
        If Len(Dir(FileName)) > 0 Then
            DoCmd.TransferSpreadsheet acLink, , "Tbl_ImportSheet", FileName, HasFieldNames
        Else
            MsgBox "The file: " & FileName & "cannot be found", vbExclamation, "ImportSheet"
        End If
        
    End Function
    Whith:
    - FileName = the name of the .xls file (full path).
    - HasFieldNames = True if the first line of the spreadsheet contains the names of the columns.

    2. Create an update query similar to this and run it:
    Code:
    UPDATE DestinationTable INNER JOIN SourceTable ON DestinationTable.ID = SourceTable.ID
    SET DestinationTable.Column1 = SourceTable.Column1, 
        DestinationTable.Column2 = SourceTable.Column2, 
        ..., etc.
        DestinationTable.ColumnN = SourceTable.ColumnN;
    With:
    - SourceTable = The name of the imported (linked) table from Excel ("Tbl_ImportSheet" if you used the function to import the table).
    - DestinationTable = The name of the table to update.
    - ID = The name of the ID column
    - Column1 to ColumnN = the names of the columns to uptate.

    Note: the names of the columns can be different in both tables.

    3. Delete the imported (linked) table.
    Have a nice day!

  5. #5
    Join Date
    Aug 2011
    Posts
    23
    Quote Originally Posted by Sinndho View Post
    If I understand correctly, you want to update three columns of an existing table with data you want to import from an Excel spreadsheet. The table has an Autonumber column (ID) that identifies each of its row, while there is a column in the spreadsheet with an ID number also identifying each row. The update must be performed based on the relationship: Table.ID = Sheet.ID.

    1. Import (link) the sheet into Access. You can use the interface (in Access 2003: Files --> Get External Data --> Link) or you can use the following code:
    Code:
    Function ImportSheet(ByVal FileName As String, Optional ByVal HasFieldNames As Boolean)
    
        If Len(Dir(FileName)) > 0 Then
            DoCmd.TransferSpreadsheet acLink, , "Tbl_ImportSheet", FileName, HasFieldNames
        Else
            MsgBox "The file: " & FileName & "cannot be found", vbExclamation, "ImportSheet"
        End If
        
    End Function
    Whith:
    - FileName = the name of the .xls file (full path).
    - HasFieldNames = True if the first line of the spreadsheet contains the names of the columns.

    2. Create an update query similar to this and run it:
    Code:
    UPDATE DestinationTable INNER JOIN SourceTable ON DestinationTable.ID = SourceTable.ID
    SET DestinationTable.Column1 = SourceTable.Column1, 
        DestinationTable.Column2 = SourceTable.Column2, 
        ..., etc.
        DestinationTable.ColumnN = SourceTable.ColumnN;
    With:
    - SourceTable = The name of the imported (linked) table from Excel ("Tbl_ImportSheet" if you used the function to import the table).
    - DestinationTable = The name of the table to update.
    - ID = The name of the ID column
    - Column1 to ColumnN = the names of the columns to uptate.

    Note: the names of the columns can be different in both tables.

    3. Delete the imported (linked) table.
    Great! Thanks, I will try this... just a few clarifying questions:

    In step 1 am I importing the data from Excel into a new table?
    In step 2 then the query is on the new table and my table I want the info to go into?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    That's it. Step 1 "imports" the data into Access (more accurately: it links the external data to the database, the actual data remain in the excel file).
    in step 2 the query updates the existing (local) table with the data from the linked Excel file.
    Have a nice day!

Posting Permissions

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