No trouble.
As you don't really have to check the whole file look at my other posting
and that of Chip Pearson.

RBS

"Kieran H" wrote in message
ups.com...
On Feb 23, 10:18 pm, "RB Smissaert"
wrote:
Haven't timed it, but this might be a faster way of doing this:

Sub test()
MsgBox Asc(GuessDelimiter("C:\DelimTest.txt"))
End Sub

Function GuessDelimiter(strFile As String) As String

Dim i As Byte
Dim n As Long
Dim x As Long
Dim z As Byte
Dim strText As String
Dim arrDelimiters(1 To 5) As String

strText = OpenTextFileToString(strFile)

arrDelimiters(1) = ","
arrDelimiters(2) = ";"
arrDelimiters(3) = "|"
arrDelimiters(4) = "~"
arrDelimiters(5) = vbTab

For i = 1 To 5
If i = 1 Then
n = CountChar(arrDelimiters(i), strText)
Else
n = CountChar(arrDelimiters(i), strText, x)
End If
If n x Then
x = n
z = i
End If
Next i

If z 0 Then
GuessDelimiter = arrDelimiters(z)
End If

End Function

Function OpenTextFileToString(ByVal strFile As String) As String

Dim hFile As Long

'obtain file handle, open file and load into a string buffer
hFile = FreeFile

Open strFile For Input As #hFile

OpenTextFileToString = Input$(LOF(hFile), hFile)

Close #hFile

End Function

Function CountChar(strChar As String, _
strString As String, _
Optional lStopAtCount As Long = -1) As Long

Dim lPos As Long
Dim n As Long

lPos = InStr(1, strString, strChar, vbBinaryCompare)

If lPos = 0 Then
CountChar = 0
Exit Function
End If

If lStopAtCount = -1 Then
Do Until lPos = 0
lPos = InStr(lPos + 1, strString, strChar, vbBinaryCompare)
n = n + 1
Loop
Else
Do Until lPos = 0 Or n lStopAtCount
lPos = InStr(lPos + 1, strString, strChar, vbBinaryCompare)
n = n + 1
Loop
End If

CountChar = n

End Function

You might make it a bit faster by altering the order of the delimiters in
arrDelimiters.

RBS

"KieranH" wrote in message

ps.com...



To all,

I would like to be able to programatically determine the delimiter
within a txt or csv file so that the user is not faced with the Excel
import text wizard..

The files come from several sources and the delimiter is likely to be
tab, comma or semicolon and less likely a pipe or tilda.

I'm thinking I need to count the number of each potential delimiter in
a given number of rows probably excluding those that occur within
quoted text.

If you have any thoughts or know of any examples they'd be a great
help

Cheers

Kieran- Hide quoted text -

- Show quoted text -

This is excelent - Real world problems - Real world solutions

My thanks and respect

Cheers

Kieran