Bug using TransferText to import CSV files

[tad0075]

I have a database that imports data weekly from CSV files saved by field staff to a particular folder (they pull data stored in remote sensors). Import works great *unless* they put a period anywhere in the file name. For example, "example 10-29-2012.csv" works fine, but "example 10.29.2012" throws an error. Since staff turns over, I would rather fix the bug than enforce a naming convention.

The code finds the file just fine and stores the name correctly. The bug only comes at the TransferText command and gives the error that it can't find the named file.

Code below. Any ideas how to fix this error?

Thanks,
Tyler

Public Function ImportFile()

Dim strFile As String
Dim strDir As String
Dim StrDirBkup As String
Dim TempName As String
Dim OldName As String
Dim NewName As String
Dim objFSO As Object

DoCmd.SetWarnings (False)

strDir = "(file path removed)"
StrDirBkup = "(file path removed)"
strFile = Dir(strDir & "*.csv")

Do While strFile <> ""
    DoCmd.TransferText acImportDelim, "ImportSpec", "Import", strDir & strFile, False   <----- ERROR HERE
    DoCmd.OpenQuery "Import qry"
    DoCmd.OpenQuery "Clear Import qry"
    OldName = strDir & strFile
    NewName = StrDirBkup & strFile
    retval = 0
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    retval = objFSO.CopyFile(OldName, NewName, True)
    Set objFSO = Nothing
    Kill strDir & strFile
    strFile = Dir(strDir & "*.csv")
Loop

DoCmd.OpenQuery "Update Locations"
DoCmd.OpenQuery "Import Last - clear New flags"

If MsgBox("Check for duplicates?", vbYesNo) = 6 Then
DoCmd.OpenQuery "Duplicates 2"
DoCmd.OpenQuery "Duplicates 3b - clear close reads"
DoCmd.OpenQuery "Duplicates 4b - updatable"
End If

DoCmd.OpenQuery "Available Data qry"
DoCmd.OpenTable "Available Data"
DoCmd.OpenForm "Selection Form"

DoCmd.SetWarnings (True)

End Function

 

[boblarson]

As Microsoft is probably not going to fix this, I would suggest using your code to make a copy of the file and removing the dots in it and then using that file to import and then delete it. I know it isn't what you want to hear and I can try to contact someone at Microsoft to pass the info along, but given some of their recent responses, I would say that could be an uphill battle.

 

[tad0075]

Bob -
Good idea on the rename. I can just use some sort of Replace function to sub hyphens for periods.

This just seems to be an odd bug, considering the error message says "can't find [exact file name]." I would understand it better if it couldn't identify the file. Too bad it's a Microsoft problem.

Thanks,
Tyler

 

[tad0075]

Quick follow up, the rename worked. Inserted the following before the TransferText command to rename the file before import, and set the archive process to change the name back to the original when it goes into the backup folder:

    TempName = Left(Replace(strFile, ".", "-"), Len(strFile) - 4) & ".csv"
    TempDir = strDir & TempName
    OldName = strDir & strFile
    retval = 0
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    retval = objFSO.CopyFile(OldName, TempDir, True)
    Set objFSO = Nothing
    Kill strDir & strFile