Import Excel to existing table without duplicates (1 Viewer)

Hi Everyone,

I have a an excel sheet of member details pulled from another system. When new members are added I want to import these into the same table.

If I do it creates duplicate values for members that were already existing. If I link to the excel sheet I get a criteria mismatch in my forms and I'm not sure why.

If I run a makro to delete the current table and import the sheet to a new table of the same name, the new table has loads of blank records with the imported data at the end.

Can anyone advise the best way to import this data into the table?

Many thanks
C.

Try importing the excel data into a temporary table, and then use an unmatched query to import the new data into your existing table.

Here's an example of a working DB that I use on a daily basis. Copy the DB and excel file to the same location.

Hi John,

Thanks for you help, I'm not very familiar with working with DB's and a bit of a newbie.

Can you attach the DB and Spreadsheet you mentioned in your post?

I really appreciate your help.

C.

Doh!!!

It needed to be compacted a little

NOTE:
This DB sample contains date criteria, and date sensitive events, which will need to be removed, if this DB is downloaded more than three months from the date of posting.

Hey John Big Booty,
Thank you for the sample Access file - *I have created an access database which is modelled on this. So far, my Import command in FRM_import is not recognising the file. I receive the message to indicate that no file is present - even though I have an excel file named "Import" saved in the path location (desktop). I have attempted changing the path to a folder with the same result.
Below is my Command string - I am not sure where I have gone wrong, and would be grateful for any advice:



DoCmd.SetWarnings False
* * path = Application.CurrentProject.path & "\Import"

* * 'Loop through the folder & build file list
* * strFile = Dir("T:\AdhocReporting\Corporate\User\Import.xls")

* * While strFile <> ""
* * * * 'add files to the list
* * * * intFile = intFile + 1
* * * * ReDim Preserve strFileList(1 To intFile)
* * * * strFileList(intFile) = strFile
* * * * strFile = Dir()
* * Wend

* * 'see if any files were found
* * If intFile = 0 Then
* * * * MsgBox "Could Not Find File 'Import.XLS'"
* * * * DoCmd.Close acForm, "Data_Import"
* * Exit Sub
* * End If

* * 'cycle through the list of files
* * For intFile = 1 To UBound(strFileList)
* * * * filename = Application.CurrentProject.path & "" & strFileList(intFile)
* * * * 'DoCmd.TransferText acImportDelim, , "TBL_OPApptData", filename, False
* * * *
* * * * DoCmd.TransferSpreadsheet acImport, , "TBL_OPApptData", filename, False
* * * *
* * Next intFile


Does anybody have any suggestions?

Best wishes

John

maybe it's not .xls that is present, maybe .xlsx:

Welcome aboard John. Use the code tags symbol (#) at the right edge of the icons across the top of the typing window to keep code aligned. But, thanks for doing it with *'s. Most people don't bother.

If there is a unique identifier in the spreadsheet, make that a unique index on your table. Most of us agree that autonumbers are best used as the primary key for reasons we won't go into at this point but sometimes you need to enforce business rules such as this one and you can do that by creating a unique index. With the table in design view, press the "indexes" icon on the ribbon. If this is a one field index, then on the first available line, type in a name for the index, choose the field and set the property to unique. If you need more than one field to ensure uniqueness such as FirstName, LastName, BirthDate, you can make a multi-field index (Access supports up to 10 columns in any single index). On the next free line, skip the index name field and choose the second column. For a third field, go to the next like, skip the index name field and choose the third column. To add a second index, start again by filling in the index name and following with selecting the columns and properties.


Once you create a unique index on whatever makes an entry unique, when you append data from the spreadsheet, it will be rejected if it would cause a duplicate.

BTW, Excel has a memory of cells that contained data so frequently when you import a spreadsheet you get a bunch of columns and rows that Access wants to import but which contain nothing, you can eliminate the rows by using selection criteria on a column that is always populated.

Where SomeField Is Not Null

To fix the problem in Excel, select the rows or columns you want to delete and use the delete option from the ribbon. Just pressing the delete key leaves them still active but empty.

John, you are getting answers so you know you are not being ignored or neglected. However, may I offer a suggestion?

When looking at older threads, it MIGHT be better if you opened a new thread and just did a copy of the thread and paste in the hyperlink to it. You were appending to a nine-year-old thread. You never know when the member in question has moved on to become inactive on the forum - or has passed on. Big John Booty has been inactive on this forum since 2017 (which you could determine from his profile if you looked at it.)