asked on Import from Excel but limit records to import
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
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.
Thank you for your advices John.
Dale:appreciate your response. I might link it. I will try to test later tonight. Thank you
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
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.
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.
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.
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
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.
The query I provided will add records to tblImportToAccess that are in the XL spreadsheet, but which are not already in tblImportToAccess.
Dale
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.
glad to help.
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/