Importing multiple csv error

Question

I am using the below code which usually works. But I am trying to import over 100 CSV file and getting an error

The error:

Run-Time '3125' - FILENAME is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

My code Below:

Sub imports()

Const strPath As String = "J:\Centsupp\Risk Management\Debts Reporting (MD)\Adhoc\06 - Daves Work\Useful Tools\Compile Data\All Files\" 'Directory Path
    Dim strFile As String 'Filename
    Dim strFileList() As String 'File  Array
    Dim intFile As Integer 'File Number

     'Loop through the folder & build file list
    strFile = Dir(strPath & "*.CSV")
    While strFile <> ""
         'add files to the list
        intFile = intFile + 1
        ReDim Preserve strFileList(1 To intFile)
        strFileList(intFile) = strFile
        strFile = Dir()
    Wend
     'see if any files were found
    If intFile = 0 Then
        MsgBox "No files found"
        Exit Sub
    End If
     'cycle through the list of files &  import to Access
     'creating a new table called MyTable
    For intFile = 1 To UBound(strFileList)
        DoCmd.TransferText acImportDelimi, , "addresspoint", strPath & strFileList(intFile)
         'Check out the TransferSpreadsheet options in the Access
         'Visual Basic Help file for a full description & list of
         'optional settings
    Next
    MsgBox UBound(strFileList) & " Files were Imported"
End Sub

It could simply be too long. Your path string is already 108 characters. Looking at your propensity for calling things literally what they are, if you have a file name like "Second quarter report for widgets and flibbles sorted by customer and part number run september 1st 2017 " it would exceed the path length specification I think... (255 characters total) — Minty
– Minty, Commented Oct 2, 2017 at 14:07

Gustav · Accepted Answer · 2017-10-02 07:02:50Z

1

You probably need to wrap your path in quotes - and correct the typo:

DoCmd.TransferText acImportDelim, , "addresspoint", Chr(34) & strPath & strFileList(intFile) & Chr(34)

answered Oct 2, 2017 at 7:02

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Erik A · Accepted Answer · 2017-10-02 07:02:31Z

Sounds simple enough: the filename probably isn't valid. You're not providing it, though.

The most frequent reason filenames aren't valid, is because they contain non-ascii characters. You can do a simple test for non-ascii characters:

Public Function StringContainsNonASCII(str As String) As Boolean
    Dim i As Integer
    'Default is false
    StringContainsNonASCII = False
    'Remove question marks
    str = Replace(str, "?", "")
    For i = 1 To Len(str)
        'Search for question marks
        If Asc(Mid(str, i, 1)) = 63 Then
            StringContainsNonASCII = True
            Exit Function
        End If
    Next i
End Function

When you've included this function, you can test StringContainsNonAscii(strFile), and if this returns true, you can generate a descriptive error. You can do little with these files from Access, though. You need to rename them first.

Collectives™ on Stack Overflow

Importing multiple csv error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related