By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 1,756 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Identify Delimiters

P: n/a
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

Oct 16 '07 #1
Share this Question
Share on Google+
7 Replies


P: n/a
On Oct 16, 3:45 pm, Roy <praish1...@yahoo.comwrote:
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.

Oct 16 '07 #2

P: n/a
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
Oct 17 '07 #3

P: n/a
On Oct 16, 4:45 pm, Roy <praish1...@yahoo.comwrote:
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

Oct 17 '07 #4

P: n/a
On Oct 17, 3:36 pm, CDMAPos...@FortuneJames.com wrote:
On Oct 16, 4:45 pm, Roy <praish1...@yahoo.comwrote:


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!

Oct 18 '07 #5

P: n/a
On Oct 18, 11:51 am, OldPro <rrossk...@sbcglobal.netwrote:
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

Oct 19 '07 #6

P: n/a

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!
Oct 19 '07 #7

P: n/a
On Oct 19, 7:29 pm, Chuck Grimsby <c.grim...@worldnet.att.net.invalid>
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

Oct 21 '07 #8

This discussion thread is closed

Replies have been disabled for this discussion.