Check for duplicates when importing excel into existing table (1 Viewer)

I have an access database that has a button to import data from an specific excel document into a table. The table had Name, and Date as primary keys.

When importing the doc this obviously stops duplicate name/date entries from being added. This is probably a really bad way of doing it but was the way I thought of getting it to work at the time.

I now need to upload my tables to sharepoint. Access isn't able to map to a backend which is hosted on sharepoint so I have created sharepoint lists to hold the table data. Unfortunately sharepoint lists only allow ID auto number as primary key.

What I need to do now is perform some sort of error checking on the import action and I really don't know where to start. I've done some google searching but I've not found a similar question or any tutorial to get started. Could someone please give some advice or point me in the right direction.

Thanks in advance.

Does not have to be a primary key, just a unique key?

Gasman said:

Does not have to be a primary key, just a unique key?

Click to expand...

I'm not sure SharePoint lists allow specified unique keys either. I'll do some research.

I've very recently had to write an Access database that imports a large volume of data from a Cobalt system so that it can then be processed. Not saying this is the best way but it worked very nicely and might give you a few ideas.

The file being imported is a CSV with over 450,000 entries consisting of 115 fields. I didn't need all of the fields so importing all of that data on a daily basis into my main Access database would cause it to grow in size very quickly and require compacting often which I don't like to do unless necessary. So, I created a separate database called Staging with only one table and enough fields called F1 to F115 (the name was a prerequisite of the import procedure). I added the correct datatypes to the fields and also indexes (become relevant later).

I wrote VBA on the button click in my main application. This copied the Staging database to a temporary location. It then creates a linked table to the table in the temporary Staging database. Next it imports the CSV into the linked table. I wrote a query against the linked table mapping against data in my main application to pull out all the relevant data I needed and append to whichever table accordingly. By setting the correct data types and indexes in the staging database table I can use JOINs in the query.

In the live environment my VBA will remove the linked table and delete the temporary staging database (just to keep things tidy).

So back to your question, the query you could use would check your existing tables against the temporarily imported data and identify duplicates/errors.

It seems Events might be an option?

https://social.technet.microsoft.com/Forums/office/en-US/94a16faa-74ea-4984-aa13-cbd96e6e993a/how-to-make-a-custom-column-unique-in-sharepoint?forum=sharepointadminlegacy

Ok, so I've gone with another simple and unsexy solution. I've created a new field that concatenantes the company and date fields and set that as requiring unique values in the SharePoint list.

That will have to do for now as many of the other options are over my head.