Results 1 to 4 of 4

Merge two Excel Files

This is a discussion on Merge two Excel Files within the Microsoft Access forums, part of the Question Forums category; My first Excel file is 17,000 lines. Columns a-h have patient data. Columns i-j have comments added when the data ...

  1. #1
    New Member
    Join Date
    Feb 2006
    Location
    Durham, NC
    Posts
    7

    Default Merge two Excel Files

    My first Excel file is 17,000 lines. Columns a-h have patient data. Columns i-j have comments added when the data is reviewed (its a list of changes).

    Second Excel file has 44,000 lines. Its cumulative from the first file.

    I want to compare columns a-h of both files. If they are identical, I want to transfer the comments from columns i-j of the first file over to the second file.

    I believe I should end up with a table of 44,000 lines and it will show the comments that were made on the original file (columns i-j). That way I can see which changes have already been reviewed.

    I'm just getting to the intermediate lever in Access, and barely starting to know how to play around with SQL.

  2. #2
    Board Regular Trevor G's Avatar
    Join Date
    Jul 2008
    Location
    Tamworth, Staffordshire
    Posts
    5,782

    Default Re: Merge two Excel Files

    What you could consider doing is upload the files into 2 separate tables in Access (DoCmd.TransferSpreadsheet) will work then you can run some SQL code to update the comments. That would work something like this:

    Function sqlUWRCode()
    DoCmd.SetWarnings False
    sql = "UPDATE [XIAP Stub dataset New] INNER JOIN [Underwriter Codes] ON [XIAP Stub dataset New].[Uwr ID Code] = [Underwriter Codes].[System Code]"
    sql = sql & "SET [XIAP Stub dataset New].[Uwr ID Code] = [Underwriter Codes]![XIAP Code];" ' The table to update 'the field and content to add
    Debug.Print sql
    DoCmd.RunSQL sql
    DoCmd.SetWarnings True
    End Function
    Trevor
    I am on a learning curve of life, I know a little but like to share what I have learnt with others.
    I am using Microsoft Office 2003 to 2013
    Please remember everyone here is a volunteer, so if you have had a reply to your thread be courteous and acknowledge this.

  3. #3
    Board Regular
    Join Date
    Dec 2011
    Posts
    536

    Default Re: Merge two Excel Files

    Upload the 2 spreadsheet onto 2 tables in Access. In query design view, use outer joins to link the two tables (show all records from the 2nd, bigger table). Make sure you have a join for ever column in A through H. Select spreadsheet 2's columns A through H to be displayed. Then select spreadsheet 1's columns I through J to be displayed after spreadsheet 2's columns A through H. The results will show all the records in spreadsheet 2 and the comments from spreadsheet 1 associated with those records

  4. #4
    New Member
    Join Date
    Feb 2006
    Location
    Durham, NC
    Posts
    7

    Default Re: Merge two Excel Files

    Thanks everyone for your help. I basically used the suggestion of outer join (I did a union). Now the problem I'm running in to is that the updated file has duplicate lines. Apparently when the patient data is updated, it sends over the entire record, even fields that did not change!

    So the solution works, now I have to work on convincing the end user that they don't need all those duplicate unchanged fields. Because, Union works but eliminates duplicates. Outer join does not work because, since there are duplicate lines, Access does not know which line to put the comments from columns I, J.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com