Need to check if a file exsists before importing csv file

[woodje]

Hello,

I need to check to see if a file name exsists in a directory. If it does I need to error out and send a message to the user. If the file does not exsist then to import the csv file to an Access 2000 database. I have the code to import the csv and to move the file when complete I just need to figure out if the file has been processes already. Any help you can give me would be great.

Jeff

 

[Troyston]

Check this snippet out, it's very recent:

'---The location and file name for the artemis data feed is stored in the SysDefaults table
strArtSchLoc = tLookup("SysDefaultValue", "SYS_Defaults", "SysDefaultName='ArtSchLoc'")
strArtSchName = tLookup("SysDefaultValue", "SYS_Defaults", "SysDefaultName='ArtSchName'")
SysMsg = SysCmd(acSysCmdUpdateMeter, 2)
'---Check that the Artemis data is in the right place, else abort
If Dir(strArtSchLoc & strArtSchName) = "" Then
MsgBox "Data source may be missing or named incorrectly." & vbCr & _
"Check that " & strArtSchName & " is in the directory " & vbCr & _
strArtSchLoc & "." & vbCr & _
"Check the system defaults are configured correctly.", vbOKOnly + vbInformation, "Missing:"
booAbort = True
GoTo ExitPoint
End If
'---Before making new data and reports etc the existing data must be backed up
'---Backup is carried out into default directories as specified in the systems defaults table
'---First check that there is a directory to create the backups in
strArtSchBkDir = tLookup("SysDefaultValue", "SYS_Defaults", "SysDefaultName='ArtSchBakDir'")
If Dir(strArtSchBkDir, vbDirectory) = "" Then
MkDir strArtSchBkDir
End If
'---Now check that there is a backup directory for the year, and if not create it
strArtSchBkDir = strArtSchBkDir & Format(Date, "yyyy") & "\"
If Dir(strArtSchBkDir, vbDirectory) = "" Then
MkDir strArtSchBkDir
End If
'---Now check that there is a backup directory for the month, and if not create it
strArtSchBkDir = strArtSchBkDir & Format(Date, "mm") & "\"
If Dir(strArtSchBkDir, vbDirectory) = "" Then
MkDir strArtSchBkDir
End If
SysMsg = SysCmd(acSysCmdUpdateMeter, 3)
'---Now there is a directory export the SCH_MilestoneSchedule table to it
'---Set up the name for the database to export to, this is unique based on the date and time
strTblName = Format(Date, "yymmdd") & Format(Time, "hhnnss")
'---Now create the new database
Set dbsNew = Workspaces(0).CreateDatabase(strArtSchBkDir & strTblName & "_SCH_MilestoneSchedule.mdb", dbLangGeneral)
Set dbsNew = Nothing
SysMsg = SysCmd(acSysCmdUpdateMeter, 4)
'---Transfer the milestone schedule data to the new database
DoCmd.TransferDatabase acExport, "Microsoft Access", strArtSchBkDir & strTblName & "_SCH_MilestoneSchedule.mdb", acTable, "SCH_MilestoneSchedule", strTblName & "_SCH_MilestoneSchedule"
SysMsg = SysCmd(acSysCmdUpdateMeter, 5)

Troy Vinson
Trading as IT Supportman

http://www.itsupportman.co.uk

 

[woodje]

Thought this might help. This is the VBA code I am using to
import the file and then move the file and change the file
name.


Private Sub cmdGPDataImport_Click()
On Error GoTo cmdGPDataImport_Click_Err
Dim intResponse As Integer
intResponse = MsgBox("Warning: You are about to Import Data to the Global Phone database." & Chr(13) & _
"Are you sure you want to do this?", vbYesNo + vbExclamation, "Import Confirmation")
Select Case intResponse
Case vbYes
Dim hFILENAME As String
Dim hPath As String
Dim hFile As String

hPath = "F:\GA ATL Fraud\GP_Data_Imports\"
'the file that is placed in the directory above
'is set "HUR_VERIZON_REPORT_00599.csv" where
'the "00599" is a sequencial number that is
'different each day. So we would need to have
'a way to check the H_NEW_PATH directory to see
'if the file above is located in that directory
'with a file extension of "_done"
hFile = Dir(hPath & "HUR*.csv")
hFILENAME = hPath & hFile


DoCmd.TransferText acImportDelim, "HUR_Import_Specification_Test", "tblGP_HUR_Import", hFILENAME, False, ""
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryHURStep1", acNormal, acEdit
DoCmd.OpenQuery "qryHURStep1_2", acNormal, acEdit
DoCmd.OpenQuery "qryHURStep2", acNormal, acEdit
DoCmd.OpenQuery "qryHURStep3", acNormal, acEdit
DoCmd.OpenQuery "qryHURStep4", acNormal, acEdit
DoCmd.SetWarnings True
Dim H_OLD_PATH, H_OLD_FILE, H_OLD_FILENAME, H_NEW_PATH, H_NEW_FILE, H_NEW_FILENAME
H_OLD_PATH = "F:\GA ATL Fraud\GP_Data_Imports\"
H_OLD_FILE = Dir(H_OLD_PATH & "HUR*.csv")
H_OLD_FILENAME = H_OLD_PATH & H_OLD_FILE
H_NEW_PATH = "F:\GA ATL Fraud\GP_Data_Imports\GP_HUR_Data_Import_Completed\"
Do Until H_OLD_FILE = ""
H_NEW_FILE = H_OLD_FILE & "_done"
H_NEW_FILENAME = H_NEW_PATH & H_NEW_FILE
H_OLD_FILE = Dir
Name H_OLD_FILENAME As H_NEW_FILENAME
H_OLD_FILENAME = H_OLD_PATH & H_OLD_FILE
Loop

DoCmd.SetWarnings True
Beep
MsgBox "CAR & HUR Updates Completed!!!", vbInformation, "Global Phone Data Upload"
Case Else
Forms!frmGlobalPhoneSupMenu.Visible = True
End Select
cmdGPDataImport_Click_Exit:
Exit Sub
cmdGPDataImport_Click_Err:
MsgBox Error$
Resume cmdGPDataImport_Click_Exit
End Sub

 

[ssatech]

How bout something like:

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists("C:\something.txt") = True Then
MsgBox "File exist"

Else
MsgBox "File does not exist"

'Your code to import your csv file


End If

Set fso = Nothing


***Make sure you reference Microsoft Scripting Runtime

 

[woodje]

ssatech,

Thanks for the response. How can I use your code to check the file name in one directory and then see if that file exsists in another directory with the same file name with a file extension of "_done".

 

[JerryKlmns]

?fso.GetFile("C:\something.txt").ShortName
something.txt

?fso.GetExtensionName("C:\something.txt")
txt

strFileShortName = fso.GetFile("C:\something.txt").ShortName
strFileExtension = fso.GetExtensionName("C:\something.txt")
strFileNewLocation = "D:\Processed\"
strFileExtra = "_done"
strCheckFile = strFileNewLocation & strFileShortName & strFileExtra & "." & strFileExtension 
If fso.FileExists strCheckFile Then
  Msgbox "Already there"
End If