Conditional Import - skip rows based on criteria

[RealmOfConfusion]

I have a text file that I import every month that comes from a supplier and contains over 3,500,000 records.

Once imported, I only need the records where the product_code field ends with the letters "PE". The current data file has about 600,000 "PE" records (that I need to keep) and 2,900,000 other records that I don't need.



I have been running a query once the entire text file has been imported to Access which deletes the records that do not end with "PE". This is simple enough and only takes a minute or so to run, but I was wondering if there was any way to import records from the text file, but to "skip" any where the product_code field does not end with "PE" - some sort of 'conditional import' for want of a better phrase so that only the "PE" records from the text file are actually imported in the first place.

[CJ_London]

rather than importing the text file, create a linked table. If you keep the file name and location the same, you don't need to refresh this each time - my analogy is the linked table is the microscope and the files are the slides

e.g. filenames=Jan Data.txt, Feb Data.txt etc
copy to a file called say DataImport.txt (e.g. copy Jan Data)
create your linked table on DataImport.txt
run the query below
next month, copy Feb Data.txt to DataImport.txt to replace it
etc

query then becomes a simple append query, something like

INSERT INTO destinationTable
SELECT DataImport.*
FROM DataImport
WHERE ProductCode LIKE "*PE"

[ItsMe]

I don't have any examples, others here parse text files more often than I do. Rather than Importing many records, you could open the text file and Parse the data before Importing or Linking. I am not sure there is a performance gain with any particular approach.

To open a text file and parse it, read it, append it, etc. Reference, "Microsoft Scripting Runtime" (I think that is what it is called). Then use filesystemobject, getfile, and OpenAsTextStream.

https://msdn.microsoft.com/en-us/lib...ffice.14).aspx

If I was trying to resolve a performance issue, I would probably use a separate machine to Import or link the entire text file. Then, I would simply link my production DB to the DB file/machine that is doing all of the Import work, linking the production DB to the table that has the parsed data.

[orange]

I agree with Ajax and ItsMe in that you do not need to import all data then delete the unwanted.
I would probably go the parse the text file route, but either will do the job.
Here's a sample using a txt file

'---------------------------------------------------------------------------------------
' Procedure : ImportText
' Author    : mellon
' Date      : 22/04/2015
' Purpose   : Routine to take an input text file, parse it to find
'             those records ending with "PE". For demo just output the records
'             of interest to the immediate window.
'---------------------------------------------------------------------------------------
'
Sub ImportText()
          Dim intFile As String      'variable to get freefile number
          Dim strBuffer As String    'record within incoming file
          Dim strFile As String      'incoming text file

10       On Error GoTo ImportText_Error

20        strFile = "C:\users\mellon\documents\TestDataPE.txt"   ' Incoming file
30        intFile = FreeFile()
40        Open strFile For Input As #intFile

50        Do While EOF(intFile) = False
60            Line Input #intFile, strBuffer
70            Select Case Right(strBuffer, 2)
              Case "PE"
                  ' This is a row of interest to you...
                  'For demo -- just output the record of interest to the immediate window
80              Debug.Print "Keeper   " & strBuffer
90            Case Else
                  ' Ignore
100           End Select
110       Loop

120       Close #intFile

130      On Error GoTo 0
140      Exit Sub

ImportText_Error:

150       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ImportText of Module Q"
End Sub

Here is the sample output

Keeper   14,Miami, FL,2345PE
Keeper   922,Roanoke, VA,9550-PE
Keeper   3014,Brunswick, GA,w5337PE

Here is the sample txt file which is "C:\users\mellon\documents\TestDataPE.txt" on my computer.

CustomerId,City,State,ProductCode
10,Laredo, TX,12345TS
14,Miami, FL,2345PE
354,Orlando, FL,54995PF
922,Roanoke, VA,9550-PE
2941,Oswego, NY,44485-WD
3014,Brunswick, GA,w5337PE

Hope it's useful to you. Good luck.