asked on

Import from Excel but limit records to import

Experts,

I am importing data from excel (file name is ImportToAccess.xlsx)
It works fine.
I now only need to import certain records.
The records I would not import would be if there is a match on certain fields.

The fields are:
[Trade #] property is short text
[Buy CP] property is short text
[Quantity BBLS] property is number
[Batch] property is short text

How could I modify the attached code to only import records that do not match between ImportToAccess.xlsx and the file in access that is named the tblImportToAccess.
I am currently deleting all data from tblImportToAccess before I import and I think that part would have to be modified.
I hope that makes sense.
Let me know if you require more information. FYI: I am a novice user of Access and do not have much coding experience.

below please find the code:

Private Sub btnImport_Click_Click()

  
    If MsgBox("Do you want to Import and Delete data?", vbYesNo) <> vbYes Then

       Exit Sub
    End If
       
 CurrentDb.Execute "delete * from [tblImportToAccess]"
    Dim strName As String
    Dim xlApp As Object
    Dim xlWB As Object
    Set xlApp = New Excel.Application
    With xlApp
        .Visible = False
        Set xlWB = .Workbooks.Open("C:\Users\tjohnson\Documents\Bomin\ImportToAccess.xlsx", , False)
       
   strName = "ToImport"
    End With
    Set xlWB = Nothing
    Set xlApp = Nothing

    DoCmd.TransferSpreadsheet acImport, , "tblImportToAccess", "C:\Users\tjohnson\Documents\Bomin\ImportToAccess.xlsx", True, strName & "!"
    MsgBox "Data has been imported.  The table will now appear and need to check for accuracy.", vbInformation
    DoCmd.OpenTable "tblImportToAccess"

On Error GoTo EH
         
EH:
If Err.Number = 2450 Then
    Exit Sub
    'resume next  'don't know what this is for.
ElseIf Err.Number <> 0 Then
   
    MsgBox "Error " & Err.Number & ": " & Err.Description
End If


End Sub

Open in new window

John Tsioumpris

You have 2 options
1. Continue to use this code and just delete the "excessive" records
2. "Continue" the part of the Excel Automation to iterate cell- by cell and get what you need. Take a look here:https://www.access-programmers.co.uk/forums/threads/simple-loop-through-excel-cells.261290/

Dale Fye

The question is, what are you doing with it once you get it into Access, your code doesn't give any indication of that.

Generally, when I link the spreadsheet from Excel rather than importing it, and name it XLImportToAccess

Then I run several queries against the data that was import to make sure that all required fields have data and that the data is of the appropriate values. If not, I present that information to the user and have them fix it, and try again. Sometimes I allow them to fix it in Access, other times I require them to fix it in Excel, before importing to Access. For repeatability, fixing it in Excel is preferable.

Then I import the data into my production table. Since you don't want to import records which already exist, your query might look something like:

INSERT INTO tblImportToAccess (field1, field2, field3, ... )
SELECT XL.Field1, XL.Field2, XL.Field3, ...
FROM XLImportToAccess as XL
LEFT JOIN tblImportToAccess as I2A ON XL.[Trade #] = I2A.[Trade #] AND XL.[Buy CP] = I2A.[Buy CP] AND
XL.[Quantity BBLS] = I2A.[Quantity BBLS] AND XL.[Batch] = I2A.[Batch]
WHERE I2A.ID IS NULL

Open in new window

This is what is known as a frustrated outer join. It lets you identify records in XLImportToAccess which are not in tblImportToAccess, and assumes that tblImportToAccess contains an identity field (autonumber) named ID.

Disclaimer: Always work with a copy of your database when developing, so that you don't overwrite critical data inadvertently.

pdvsa

ASKER

Thank you for your advices John.

Dale:appreciate your response. I might link it. I will try to test later tonight. Thank you

pdvsa

ASKER

Hi Dale, I am working on your suggestion. I have developed the query. Its pretty neat and surprised I got as far as I did.

I just need to confirm what the query is doing:

tblImportToAccess is my table I am working with (think its called production table as you say)

I do not need to append records from XL where there is a match between XL and tblImportToAccess on those various fields (bc that means its already in my production table and do not want dupes). I should only be appending records where there is not a match. Is this what the query is doing?

One other fact is that my XL will not have an ID autonumber because the excel file is just the raw data (assume this is ok). My tblImportToAccess does have an ID as PK and autonumber.

here is my code: (I might not be able to respond until tomorrow though)

INSERT INTO tblImportToAccess (ID,[Trade #], [Buy CP], [Quantity BBLS], [Data], Batch, [Origin / Deal], [Date], Grade, State, [Trade #1], [Sale CP], [Operation #], WorkingDate, SentAwayDate, Notes)
SELECT XL.ID, XL.[Trade #], XL.[Buy CP], XL.[Quantity BBLS], XL.[Data], XL.Batch, XL.[Origin / Deal], XL.Date, XL.Grade, XL.State, XL.[Trade #1], XL.[Sale CP], XL.[Operation #], XL.WorkingDate, XL.SentAwayDate, XL.Notes
FROM XLImportToAccess as XL
LEFT JOIN tblImportToAccess as I2A ON XL.[Trade #] = I2A.[Trade #] AND XL.[Buy CP] = I2A.[Buy CP] AND
XL.[Quantity BBLS] = I2A.[Quantity BBLS] AND XL.[Batch] = I2A.[Batch] AND XL.[Date] = I2A.[Date]
WHERE I2A.ID IS NULL

Open in new window

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pdvsa

ASKER

Hi Dale...thank you for the response and advices.

One thing I didnt mention was that if there is a match between XL and tblImportToAccess on the fields i would need to use the XL.[Date] in every case in tblImportToAccess...much like overriding the tblImportToAccess.Date to the value in XL.Date. I am not sure if that is what the code is doing now as stands. Sincerely appreciate your expert assistance.

pdvsa

ASKER

Its also possible that I remove only the initial match criteria on XL.Date and tblImportToAccees.Date and have a separate query to update to use the XL.Date. I hope I am not confusing this. So sorry if I am. Grateful for your help.

Dale Fye

Not quite sure I understand what you are saying in these last two. Might be helpful if you could provide an example.

If you want to import records where there is no match across all 5 fields, then the SQL I provided should work.

If you want to update some fields in a record based on some other combination of fields, you will need another query.

pdvsa

ASKER

Hi Dale, just letting you know I read your message and once I get a second I will provide a more detailed example. Possibly later on tonight

pdvsa

ASKER

Dale:

<If you want to import records where there is no match across all 5 fields, then the SQL I provided should work.

Sorry but i DO NOT want to import those records to tblImportToAccess where there is a match because those recods would already be in tblImportToAccess. I need to import the records that areNOT IN or essentially the records where there is not a match. Sorry for any confusion. Let me know if that changes the code.

Dale Fye

The query I provided will add records to tblImportToAccess that are in the XL spreadsheet, but which are not already in tblImportToAccess.

Dale

pdvsa

ASKER

Dale, I think that looks good. I have added records and seems to be doing what I want it to do! Yes! I do have a tweak on it though on the [DATE]. I planned to take it in steps and I will post another question.

Dale Fye

glad to help.