Identify Delimiters

[Roy]

Hi ,

I use a A2003 App.I get a text file everyday from a external source
which I use to update my tables.The problem is there is no consistent
delimiter used in this text file.Some days it is tab or other days it
uses semi-colon(;) as a delimiter.
So in my line input code I use both the codes and commenting either
one when the import fails.

Open TestFile For Input As #1
Do While Not EOF(1)
Line Input #1, str1
'str2 = Split(str1, vbTab)--the days when tab is used
str2 = Split(str1, ";"))--the days when semi-colonis used

How to identify the delimiter so that I can use a If statement to
chose one?

Thanks,

Roy

[OldPro]

On Oct 16, 3:45 pm, Roy <[e-mail]:

Hi ,

I use a A2003 App.I get a text file everyday from a external source
which I use to update my tables.The problem is there is no consistent
delimiter used in this text file.Some days it is tab or other days it
uses semi-colon(;) as a delimiter.
So in my line input code I use both the codes and commenting either
one when the import fails.

Open TestFile For Input As #1
Do While Not EOF(1)
Line Input #1, str1
'str2 = Split(str1, vbTab)--the days when tab is used
str2 = Split(str1, ";"))--the days when semi-colonis used

How to identify the delimiter so that I can use a If statement to
chose one?

Thanks,

Roy

You could always replace all vbTabs with semi-colons before Splitting.

[Albert D. Kallal]

Line Input #1, str1

if instr(str1,";") 0 then
str2 = Split(str1, ";"))--the days when semi-colonis used
else
str2 = Split(str1, vbTab)--the days when tab is used
end if

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[CDMAPoster]

On Oct 16, 4:45 pm, Roy <[e-mail]:

Hi ,

I use a A2003 App.I get a text file everyday from a external source
which I use to update my tables.The problem is there is no consistent
delimiter used in this text file.Some days it is tab or other days it
uses semi-colon(;) as a delimiter.
So in my line input code I use both the codes and commenting either
one when the import fails.

Open TestFile For Input As #1
Do While Not EOF(1)
Line Input #1, str1
'str2 = Split(str1, vbTab)--the days when tab is used
str2 = Split(str1, ";"))--the days when semi-colonis used

How to identify the delimiter so that I can use a If statement to
chose one?

Thanks,

Roy

That is a tricky problem. When the file is tab delimited, there might
be semicolons in the data and vice versa. You can blame my
mathematics background for considering such possibilities :-). You
can't just see if there are more semicolons or tabs. A reasonable
assumption might be that tabs in data are relatively rare so count the
number of tabs and use that number relative to the number of lines or
a multiple thereof (NumFields - 1) to determine what delimiter to
use. Perhaps use only the first line of data to make that decision as
Albert does (and to count the number of fields). Remember that the
success of choosing the delimiter this way is only as good as the
assumption about the number of tabs that can be in the data. OldPro's
method will fail if there are semicolons in the data. IMO, Albert's
method would be better if vbTab is checked in the 'If' rather than the
semicolon due to the assumption.

James A. Fortune
CD********@FortuneJames.com

[OldPro]

On Oct 17, 3:36 pm, [e-mail] wrote:

On Oct 16, 4:45 pm, Roy <[e-mail]:

Hi ,

I use a A2003 App.I get a text file everyday from a external source
which I use to update my tables.The problem is there is no consistent
delimiter used in this text file.Some days it is tab or other days it
uses semi-colon(;) as a delimiter.
So in my line input code I use both the codes and commenting either
one when the import fails.

Open TestFile For Input As #1
Do While Not EOF(1)
Line Input #1, str1
'str2 = Split(str1, vbTab)--the days when tab is used
str2 = Split(str1, ";"))--the days when semi-colonis used

How to identify the delimiter so that I can use a If statement to
chose one?

Thanks,

Roy

That is a tricky problem. When the file is tab delimited, there might
be semicolons in the data and vice versa. You can blame my
mathematics background for considering such possibilities :-). You
can't just see if there are more semicolons or tabs. A reasonable
assumption might be that tabs in data are relatively rare so count the
number of tabs and use that number relative to the number of lines or
a multiple thereof (NumFields - 1) to determine what delimiter to
use. Perhaps use only the first line of data to make that decision as
Albert does (and to count the number of fields). Remember that the
success of choosing the delimiter this way is only as good as the
assumption about the number of tabs that can be in the data. OldPro's
method will fail if there are semicolons in the data. IMO, Albert's
method would be better if vbTab is checked in the 'If' rather than the
semicolon due to the assumption.

James A. Fortune
[e-mail]- Hide quoted text -

- Show quoted text -

Of course, if there are semi-colons in the data of a text file
delimited by semi-colons, then it won't work anyway. Whatever the
data is, one would hope that there are no tabs or semi-colons!

[CDMAPoster]

On Oct 18, 11:51 am, OldPro <[e-mail]:

Of course, if there are semi-colons in the data of a text file
delimited by semi-colons, then it won't work anyway. Whatever the
data is, one would hope that there are no tabs or semi-colons

An even better idea might be to parse the first data line until the
first of either two tabs or two semi-colons are encountered. That
should cut down the odd cases to almost nothing. Data delimited by
tabs may contain semi-colons. You can't ignore that possibility
because you don't know the delimiter a priori.

James A. Fortune
CD********@FortuneJames.com

[Chuck Grimsby]

Will the file *always* have a known number of "fields" in it?

If so, use the split function to find out if it has the right amount
of fields. Try it once with vbTab, then (if that returns the wrong
number of fields) try the semi-colon.

Public Function CountFields(strIn As String, _
strDelimiter As String) _
As Long
Dim varFields As Variant

varFields = Split(strIn, strDelimiter)
CountFields = UBound(varFields)
End Function

On Tue, 16 Oct 2007 13:45:36 -0700, Roy <pr********@yahoo.comwrote:

>I use a A2003 App.I get a text file everyday from a external source
which I use to update my tables.The problem is there is no consistent
delimiter used in this text file.Some days it is tab or other days it
uses semi-colon(;) as a delimiter.
So in my line input code I use both the codes and commenting either
one when the import fails.

Open TestFile For Input As #1
Do While Not EOF(1)
Line Input #1, str1
'str2 = Split(str1, vbTab)--the days when tab is used
str2 = Split(str1, ";"))--the days when semi-colonis used

How to identify the delimiter so that I can use a If statement to
chose one?

Please Post Any Replies To This Message Back To the Newsgroup.
There are "Lurkers" around who can benefit by our exchange!

[CDMAPoster]

On Oct 19, 7:29 pm, Chuck Grimsby <[e-mail]>
wrote:

Will the file *always* have a known number of "fields" in it?

If so, use the split function to find out if it has the right amount
of fields. Try it once with vbTab, then (if that returns the wrong
number of fields) try the semi-colon.

Public Function CountFields(strIn As String, _
strDelimiter As String) _
As Long
Dim varFields As Variant

varFields = Split(strIn, strDelimiter)
CountFields = UBound(varFields)
End Function

That's a good idea. If you can enforce that the field names be
included on the first line it gets even simpler since they should be
flogged if they use a tab or a semi-colon as part of a field name.

James A. Fortune
CD********@FortuneJames.com