Parsing tab-delimited records from a sequential file

[Bob Howard]

I have need to read in a text file containing tab-delmited fields. How do I
parse this gaining access to each individual data field and giving each a
variable name in the code (such as strName, strAddressLine1, etc.)?

Some fields are optional and simply represented by their tab character if in
the middle of the line. All trailing tabs on optional fields are omitted.
The first field (name) is required.

For example:

Name1<tab>Address line 1<tab><tab>City<tab>State<tab>ZIP
Name2<tab>Address line 1<tab>Address line 2<tab>City<tab>State<tab<ZIP
Name3
....
....
....
etc.

Note the first record has no "Address Line 2" but its tab character is
present.

Note the second record has data in all fields.

Note the third record has just one field and all the rest are missing but
not represented by tabs.

Is there any caned VBA to help me on this???

Thanks in advance!

Bob (@Martureo.Org)

 

[Ken Snell [MVP]]

You might want to use a variant array variable, into which you would split
the string using the Tab delimiter. You then can use each item in the array
to put the data where they go.

Something like this:

' Assume that strLine holds one line from your tab-delimited file:

Dim varFields As Variant
varFields = Split(strLine, Chr(9))
' varFields(0) is the Name value
' varFields(1) is the Address line 1 value
' varFields(2) is the Address line 2 value
' varFields(3) is the City value
' varFields(4) is the State value
' varFields(5) is the Zip value

 

[Bob Howard]

Thanks for such a quick response. The solution was so simple! I poked
around in the Help screens for a while and never found the Split function.
This Access will take me more years to figure out than I have left! I guess
I better keep it simple..... Thanks again. Bob.

 

[Ken Snell [MVP]]

You're welcome. ACCESS still presents me with new things every day....!

 

[Bob Howard]

Hi Ken;

I put the code in the program yet it's not working. The Split function
raises error 5, "Invalid procedure call or argument" --- I've checked Help
and cannot see anything wrong.

I'm using the following:

Dim strRecord As String ' Area to hold record as read
Dim varRecord As Variant ' Area to hold parsed record
strRecord = tfo.ReadLine ' Reads a record
MsgBox strRecord
varRecord = Split(strRecord, Chr(9))
MsgBox "done"

The first Msgbox displays the unparsed record, but the second Msgbox never
executes --- the Split function takes me right to the error handler.

For fun, I replaced the Chr(9) with " " and it still fails.

Then I removed the second argument totally and it still fails.

Then I tried:

varRecord = Split("abc def")

and it failed also!

I'm using A2K SR-1.

Is it possible that the result should be in a different type field than a
variant type? I'm not entirely sure what "variant" means.... The Help says
the Split function "Returns a zero-based, one-dimensional array containing a
specified number of substrings."

Bob.

 

[Bob Howard]

Ken;

I tried something else, but along the same lines. I went to MSKB in the VBA
component and found article # 188007 --- "How To Simulate Visual Basic 6.0
String Functions in VB5".

I clicked "Help About" in my VBA window and it says I'm using VB 6 --- but
since this is a new function, I figured maybe it wasn't a happy camper.

So I copied the code from 188007 into a module, compiled it, and used the
"simulated" Split in lieu of the native Split function --- and the program
now works! I think there's something wrong with my VBA software.

Thanks for your help, however! The more I use this stuff the more amazed I
am! This is now the third bug I've found in my A2K!

Bob.

 

[Albert D.Kallal]

Split should work.

I would check your refs....

Allen Browne
http://users.bigpond.net.au/abrowne1/ser-38.html

Doug Steele:
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html

Peter Walker:
http://www.papwalker.com/dllhell/index-page2.html


MsKb Articles: 310803, 208218, 209849, 286300

ACC2000: How Access 2000 Resolves Visual Basic for Applications References
http://support.microsoft.com/default.aspx?scid=kb;en-us;248941

ACC2000: How to Resolve Reference Issues in an Access Database
http://support.microsoft.com/default.aspx?scid=kb;en-us;310803

 

[Bob Howard]

The references appear correct. I don't get a compile error of any sort ---
the Split function just always seems to return an error. I know the Split
function is *there* because if I change the result to the wrong data type
(like Integer) I get a different error at execution time (pointing to an
incompatible type) --- thus I know that simply issuing the Split is taking
me to some code that is trying to do something. If the reference were bad,
then I don't think I would be getting that far. Bob.

 

[Ken Snell [MVP]]

Split should work for your needs, but glad you found a workaround.

A2K is a bit buggy... be sure that you've applied all patches and updates to
it -- you should be at SP3 for Office 2000.

 

[Bob Howard]

Thanks. But an overriding problem in this area is that most of the users of
the application will use the A2K Runtime --- and I'm not even sure what
level that's at! I may need to upgrade this totally!

And upon further testing, the workaround isn't really working. I'll have to
dig into the code I found on MSKB because only the first call works ---
after that it's giving back erroneous results.

What a mess!

Bob.

 

[Ken Snell [MVP]]

Note that the runtime is at the basic level of ACCESS 2000 -- no patches are
included in it. After you install the runtime on a computer, you then need
to run Office Updates on that PC so that the runtime will be updated. Same
is true for Jet dll files and such. With runtimes, you need to update after
installation; the runtime itself will not do that.

Take it from me, experience has shown that not updating runtime can lead to
all types of interesting data problems.
--

Ken Snell
<MS ACCESS MVP>

 

[Bob Howard]

OY!

By the way, the VBA Split function is now working! I decided to code the
third and fourth parameters even though the specs sayh only the first
parameter is required. That's a laugh --- the example from the Help file
gave the same error as I was getting with my program!

So when I coded the default values for the final two parameters, it worked!

Thanks for all your help!

I may need to rethink the packaging of this application and require full
Access on each computer.

Oh well ------

Bob.

 

[Albert D.Kallal]

Well, now that you got the split working, you should figure out why things
are not working.

Somitng is still a mess with your stetup. I used split for years and
years..and NEVER had a probem.

I susspect, that either your modiles are missing:


Option Compare Database
Option Explicit


Do you have the above at the start of each module. It is possible you are
missing the Option Compare. And, the Explicit also helps.

if you got the above the already set, then I suspect you are reading in
uni-code text, or perhaps your language/regional settings are different...