We help IT Professionals succeed at work.

Import from Excel but limit records to import

pdvsa
pdvsa asked
on
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

Comment
Watch Question

John TsioumprisSoftware & Systems Engineer
Distinguished Expert 2019

Commented:
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 FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:

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
 

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.


pdvsaProject finance

Author

Commented:

Thank you  for your advices John.  


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

pdvsaProject finance

Author

Commented:

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
 


Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:

@pdvsa,


I think you are on the right track, but not quite there. Since you indicated that the XL worksheet does not have an ID, and tbtImportToAccess.ID is an auto number column you should leave those to values out of the insert and the Select.


Yes, this is called a frustrated outer join, it select records which exist in one table but not the other.  Because you have used the Left Join between XL and I2A, you will get all of the records from XL and the matching records from I2A.  The WHERE clause will exclude those records which don't match because where there is no match, the I2A.ID column will be NULL.

 INSERT INTO tblImportToAccess ([Trade #], [Buy CP], [Quantity BBLS], [Data], Batch, [Origin / Deal], [Date], Grade, State, [Trade #1], [Sale CP], [Operation #], WorkingDate, SentAwayDate, Notes)
SELECT 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
 



pdvsaProject finance

Author

Commented:

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.  

pdvsaProject finance

Author

Commented:

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 FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:

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.

pdvsaProject finance

Author

Commented:

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 

pdvsaProject finance

Author

Commented:

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 FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:

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


Dale

pdvsaProject finance

Author

Commented:

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 FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:

glad to help.