Access 2003: import of csv fails due to period in filename

[vanthien]

Hi,

I'm using VBA (Access 2003) to open and process CSV data, and I wonder if there is a way to circumvent Access inability to open a csv file that has a period (or more) in the filename?
For example, a filename such as data_886.13_2012.csv results in a failed import because of the extra period.
Before I go back to modify the filename creation process, or manually edit each filename, is there some way that VBA can address this at the time of import?

Thanks,
Frank :-)

[ssanfu]

How are you doing the import?
VBA Code or manually importing? If code, what is you code?

[vanthien]

I use VBA code. There is quite a lot of code, due to the complexity of this import, but here is the guts of the code, where i take the raw csv file, strip the first row, then save the remainder as a temp csv file for further processing:
If boCSV = True Then
'REMOVE first record as it contains fieldnames
Dim fInput
Dim fOutput
Dim fs
Dim sline As String
Dim strTempTable As String

strTempTable = "temp(canBdeleted).csv"
Set fs = CreateObject("Scripting.filesystemobject")
Set fInput = fs.opentextfile(mstrFileName)
Set fOutput = fs.createtextfile(strTempTable)
sline = fInput.readline 'if you want to skip the first line
Do While fInput.atendofstream <> True
sline = fInput.readline
fOutput.writeline sline
Loop

' CLOSE the temp csv file before the next step - open doc prevents import
fInput.close
fOutput.close

DoCmd.TransferText acImportDelim, "", strTable, strTempTable, False, ""

End If

[ssanfu]

strTempTable = "temp(canBdeleted).csv"

You shouldn't use special characters (or spaces) in names.
This would be better:

strTempTable = "temp_CanBeDeleted.csv"

Before I go back to modify the filename creation process, or manually edit each filename, is there some way that VBA can address this at the time of import?

After you get "mstrFileName", why don't you rename the file first?

    Dim fInput
    Dim fOutput
    Dim fs
    Dim sline As String
    Dim strTempTable As String
    Dim mstrFileName As String
    Dim NewFileName As String

    'somehow you get the file name/ no code shown
    mstrFileName = "data_886.13_2012.csv"

    NewFileName = Left(mstrFileName, Len(mstrFileName) - 4)
    NewFileName = Replace(NewFileName, ".", "_")
    NewFileName = NewFileName & ".csv"
    
    'rename the CSV file
    Name mstrFileName As NewFileName

    strTempTable = "temp_CanBeDeleted.csv"
    Set fs = CreateObject("Scripting.filesystemobject")
    Set fInput = fs.opentextfile(NewFileName)
    Set fOutput = fs.createtextfile(strTempTable)
    sline = fInput.readline    'if you want to skip the first line
    Do While fInput.atendofstream <> True
        sline = fInput.readline
        fOutput.writeline sline
    Loop

    ' CLOSE the temp csv file before the next step - open doc prevents import
    fInput.Close
    fOutput.Close

If you are not using an option, just use commas, don't use empty string. At the end, just don't include anything

DoCmd.TransferText acImportDelim, , strTable, strTempTable, False

[vanthien]

I'll be darned...
Taking baby steps, at first I only renamed the temp csv to exclude the brackets... and the import runs fine!

I've made a note of the code you're suggesting, just in case this is an intermittent problem, but for now: problem solved.

Thanks very much.
Cheers,
Frank :-)

[ssanfu]

Wonderful...