Identify Delimiters

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

Re: Identify Delimiters

On Oct 16, 3:45 pm, Roy wrote:
> 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.

Re: Identify Delimiters

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
pleaseNOOSpamKallal@msn.com

Re: Identify Delimiters

On Oct 16, 4:45 pm, Roy wrote:
> 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
CDMAPoster@FortuneJames.com

Re: Identify Delimiters

On Oct 17, 3:36 pm, CDMAPos...@FortuneJames.com wrote:
> On Oct 16, 4:45 pm, Roy wrote:
>
>
>
>
>
> > 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
> CDMAPos...@FortuneJames.com- 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!

Re: Identify Delimiters

On Oct 18, 11:51 am, OldPro wrote:

> 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
CDMAPoster@FortuneJames.com

Re: Identify Delimiters

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 wrote:
>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!

Re: Identify Delimiters

On Oct 19, 7:29 pm, Chuck Grimsby
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
CDMAPoster@FortuneJames.com