Importing file only if it has "example" in the file name (1 Viewer)

carterlw12

Registered User.
Local time
Today, 07:29
Joined
Oct 2, 2018
Messages
25
Is there a way during my import that I can specify that it only imports if it matches a certain word in the file name?

I'm using:

Code:
Private Sub cmdImport_Click()
Dim FSO As New FileSystemObject
Dim strSQL As String

strSQL = "INSERT INTO PeopleSoft_Import_tbl (GlobalID, RecordEffDate, BirthDate, HireDate, ReHireDate, ServiceDate, SeniorityDate, TermDate, ShortName, FirstName, LastName, MI, Supervisor, EmpStatus, StatusEffDate, [FT/PT], [Reg/Temp], HomePhone, WorkPhone, PersonalEmail, WorkEmail, Region, Country, Division, Location, Dept, Job, JobLevel, [Union], FLSAInd, PayGroup, TipInd, EmployeeClass, BaseWageRate, BaseWageEffDate, WeeklyHours, ADPFile, PTOPrem, Language, Address, City, State, ZipCode, Country2, [Currency], PIS, LocalID, FileName) SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14, F15, F16, F17, F18, F19, F20, F21, F22, F23, F24, F25, F26, F27, F28, F29, F30, F31, F32, F33, F34, F35, F36, F37, F38, F39, F40, F41, F42, F43, F44, F45, F46, F47, F48 FROM PeopleSoft_staging_tbl;"


If Nz(Me.txtFilepath, "") = "" Then
    MsgBox "No file has been selected. Please select a file."
    Exit Sub
End If

If FSO.FileExists(Nz(Me.txtFilepath, "")) Then

    DoCmd.TransferText acImportDelim, , "PeopleSoft_staging_tbl", Me.txtFilepath, False

    CurrentDb.Execute "UPDATE PeopleSoft_staging_tbl SET F48 = '" & _
    Right(txtFilepath, 24) & "' WHERE F48 IS NULL", dbFailOnError
    
    

    CurrentDb.Execute strSQL, dbFailOnError
    
    

    CurrentDb.Execute "DELETE FROM PeopleSoft_staging_tbl"
    
    

    MsgBox "Import Successful!"
txtFilepath.Value = ""
    Exit Sub


End If

End Sub
 

carterlw12

Registered User.
Local time
Today, 07:29
Joined
Oct 2, 2018
Messages
25
So I've tried to get this working a couple different ways but I think my beginner status is showing through.. lol

I'm getting the MsgBox to show up even though my txtFilePath does have the word "rcokrons" in it.

Code:
Private Sub cmdImport_Click()
Dim FSO As New FileSystemObject
Dim strSQL As String

strSQL = "INSERT INTO ADP_Person_Import_tbl (GlobalID, RecordEffDate, BirthDate, HireDate, ReHireDate, ServiceDate, SeniorityDate, TermDate, ShortName, FirstName, LastName, MI, Supervisor, EmpStatus, StatusEffDate, FTPT, RegTemp, HomePhone, WorkPhone, PersonalEmail, WorkEmail, Region, Country, Division, Location, Dept, Job, OfficerCode, UnionCode, FLSAInd, ADPPayGroup, TipInd, SpecialGroup, BaseWageRate, BaseWageEffDate, WeeklyHours, ADPFile, PTOPrem, Language, Address, City, State, ZipCode, Country2, JobDept, FileName) SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14, F15, F16, F17, F18, F19, F20, F21, F22, F23, F24, F25, F26, F27, F28, F29, F30, F31, F32, F33, F34, F35, F36, F37, F38, F39, F40, F41, F42, F43, F44, F45, F46 FROM ADP_person_staging_tbl;"


If Nz(Me.txtFilepath, "") = "" Then
    MsgBox "No file has been selected. Please select a file."
    Exit Sub
End If

If FSO.FileExists(Nz(Me.txtFilepath, "")) Then

[B]If InStr(Me.txtFilepath, "rcokrons") <> Me.txtFilepath Then
    MsgBox "This file is incorrect for importing with this form. Please choose a .csv file with 'rcokrons' in the name."
    Exit Sub
End If[/B]

    DoCmd.TransferText acImportDelim, , "ADP_person_staging_tbl", Me.txtFilepath, False

    CurrentDb.Execute "UPDATE ADP_person_staging_tbl SET F46 = '" & _
    Right(txtFilepath, 26) & "' WHERE F46 IS NULL", dbFailOnError
    
    CurrentDb.Execute strSQL, dbFailOnError
    
    CurrentDb.Execute "DELETE FROM ADP_person_staging_tbl"
    
    MsgBox "Import Successful!"
txtFilepath.Value = ""
    Exit Sub


End If

End Sub
 

carterlw12

Registered User.
Local time
Today, 07:29
Joined
Oct 2, 2018
Messages
25
C:\Users\carterlw12\Documents\Audits\Rcokrons\rcokrons_20180718@1535.csv

This is what is in my txtFilepath.
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:29
Joined
Sep 21, 2011
Messages
14,051
No, Intsr returns the position of the text if found, 0 if not found(mostly). Check out the link I posted.
So test for > 0 to see if found, in your case
Code:
If InStr(1,Me.txtFilepath, "rcokrons") = 0
 

carterlw12

Registered User.
Local time
Today, 07:29
Joined
Oct 2, 2018
Messages
25
It's not working. Whether I do it how you referenced above or "<> 0" or "> 0" or "< 0" it still can't tell the difference.

Btw I did read the URL that you posted. That's where I came up with the initial code. lol
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:29
Joined
Sep 21, 2011
Messages
14,051
Walk through the code in the debugger line by line and inspect the variables.
That will show you where you are going wrong.
 

carterlw12

Registered User.
Local time
Today, 07:29
Joined
Oct 2, 2018
Messages
25
Thanks I got it.

Code:
If InStr(1, Me.txtFilepath, "rcokrons", vbBinaryCompare) = 0 Then
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:29
Joined
Sep 21, 2011
Messages
14,051
I don't believe the compare option is required in this issue?
From the immediate window

Code:
? instr(1,"the quick brown fox", "fox")
17


Thanks I got it.

Code:
If InStr(1, Me.txtFilepath, "rcokrons", vbBinaryCompare) = 0 Then
 

carterlw12

Registered User.
Local time
Today, 07:29
Joined
Oct 2, 2018
Messages
25
Whenever I tried it with the Compare option it would not work correctly. As soon as a put in the Compare option it is only importing files with the file name "rcokrons" in it, Which is what I'm looking for.
 

isladogs

MVP / VIP
Local time
Today, 12:29
Joined
Jan 14, 2017
Messages
18,186
PMFJI

Whenever I tried it with the Compare option it would not work correctly. As soon as a put in the Compare option it is only importing files with the file name "rcokrons" in it, Which is what I'm looking for.

Sorry that's not at all clear

As Gasman has already said you don't need vbBinaryCompare.
In any case vbTextCompare would be appropriate but neither are needed

Also by default Instr starts at the first character so that can be omitted also
You also only need the first argument where you are starting at a different part of the text string

So it can be written as
Code:
If InStr(Me.txtFilepath, "rcokrons") = 0 Then
 

carterlw12

Registered User.
Local time
Today, 07:29
Joined
Oct 2, 2018
Messages
25
I understand what you are saying. The very first time I tried it I omitted the 1st character and the compare options. Did not work. Then I added the 1 option without the compare options. Still didn't work. Then i used the 1 option with the TextCompare, still didn't work. Then I used the 1 option with the BinaryCompare and it did work.

If InStr(Me.txtFilepath, "rcokrons") = 0 Then
Did not work at all. This was the very first thing that I tried.
 

Users who are viewing this thread

Top Bottom