Lately i have imported a lot of CSV files. The fielddelimiter is a semicolon. When users add a semicolon in a free text field the number of fields do not match the header which contains fieldnames.
When that happens docmd.transfertext reports an unclear error message and the import is aborted.

The function below sets the number of fields by reading the header line which contains the fieldnames and checks it with the rest of the file.
When a line doesn't match the number of fields, the function exits with an error message.

Share & Enjoy!

Public Function CheckInputfile(strFilenamePath As String) As String

Dim intNumFields As Integer
Dim strTextline As String
Dim lngLinenum As Long

On Error GoTo Err_CheckInputfile

Open strFilenamePath For Input As #1

If Not EOF(1) Then
'Header line sets the number of fields.
Line Input #1, strTextline ' Read line into variable.
intNumFields = UBound(Split(strTextline, ";"), 1)
End If

lngLinenum = 1
CheckInputfile = ""

Do While Not EOF(1) ' Loop until end of file.
lngLinenum = lngLinenum + 1
Line Input #1, strTextline ' Read line into variable.
If intNumFields <> UBound(Split(strTextline, ";"), 1) Then
MsgBox "Error in inputfile: " & strFilenamePath & " on line: " & lngLinenum & vbcrlf & strTextline & vbcrlf & "Open the file and correct the problem", vbExclamation, gstrApplication
CheckInputfile = strTextline
Close #1
Exit Function
End If
Loop

Close #1

Exit_CheckInputfile:
Exit Function

Err_CheckInputfile:
msgbox Err & ":" & Error$
If Len(CheckInputfile) = 0 Then CheckInputfile = Error$
Resume Exit_CheckInputfile
Resume 'For debugging purposes
End Function