Retriving data from a .txt file

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

With reference to my another thread, I can open a .txt file. But I am having problem of retriving data row by row.

The sample .txt file contains:
F1 F2 F3 F4
1a 2b 3c 4d
one two three
1 2 3 4

When I test for recordcounts, it returns 4, which is correct. I want to read the first record/row and it, then read the second and print and third and fourth etc. I use the following code.

rst1.movefirst
Do until rst1.EOF
debug.print rst1.getstring
< some logic here >
rst1.movenext
Loop

The result of GetString is that Access treats all 4 rows as one record. And Access says it is already end of file when executes MoveNext.

Appreciate advise to solve the problem.
 
To be honest, I've never used the GetString method, so I went to read about
it in MSDN.

At
http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthgetstringmethod(recordset)ado.asp
it says that the GetString method "Returns the Recordset as a string-valued
Variant (BSTR).", and the comment in the example in
http://msdn.microsoft.com/libraryen-us/ado270/htm/mdmthgetstringx.asp say:

' Use all defaults: get all rows, TAB as column delimiter,
' CARRIAGE RETURN as row delimiter, EMPTY-string as null delimiter

(note the "get all rows" in the first line of the comment)

In other words, I think that's the way it's supposed to work!

Now, if what you've retrieved is being passed as a string with the rows
delimited by carriage returns (Chr(13)), you should be able to change that
string into an array using the Split function with Chr(13) as the delimiter,
and you should be able to get the individual fields in each row of the
recordset using the Split function with Chr(9) (the Tab character) as the
delimiter.

Dim intField As Integer
Dim intRow As Integer
Dim strData As String
Dim varRS As Variant
Dim varRow As Variant

rst1.movefirst
If Not rst1.EOF Then
strData = rst1.getstring
varRS = Split(strData, Chr(13))
For intRow = LBound(varRS) To UBound(varRS)
Debug.Print "Row " & intLoop & ": "
varRow = Split(varRS(intLoop), Chr(9))
For intField = LBound(varRow) To UBound(varRow)
Debug.Print "Field " & intField & _
" (in Row " & intRow & "): " & _
varRow(intField)
Next intField
Next intRow
End If

NOTE: This is untested code. I'm just assuming it'll work from what I read.
Please let me know if it works ('cause I'm too lazy to test it myself!)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Peter said:
With reference to my another thread, I can open a .txt file. But I am
Click to expand...
having problem of retriving data row by row.
The sample .txt file contains:
F1 F2 F3 F4
1a 2b 3c 4d
one two three
1 2 3 4

When I test for recordcounts, it returns 4, which is correct. I want to
Click to expand...
read the first record/row and it, then read the second and print and third
and fourth etc. I use the following code.
rst1.movefirst
Do until rst1.EOF
debug.print rst1.getstring
< some logic here >
rst1.movenext
Loop

The result of GetString is that Access treats all 4 rows as one record.
Click to expand...
And Access says it is already end of file when executes MoveNext.
 
Thanks Douglas. I read the explanation of the link. You are right. I have missed the words "get all rows". I will test the code and let you know the result.

Before that, I would like to share with you what I suppose to do. Here is the sample data of the .txt file.

1. Bonnie Blair Ltd
Address: Room 1206, Block A, Focal Industrial Centre, 21 Man Lok Street, Hung Hom, Kowloon, Hong Kong
Phone: (852) 23287839
Fax: (852) 23284887
E-mail: (e-mail address removed)
Contact: Miss Charles Benchetrit, Managing Director
Miss Valerie-C Hoyau, Manager

2. Bosville Ltd
Address: 6/F, Union Park Centre, 771-775 Nathan Road, Mong Kok, Kowloon, Hong Kong
Phone: (852) 23810308
Fax: (852) 23970889
E-mail: (e-mail address removed)
Contact: Mr Albert Li, Director

As you see there is a pattern of the content. I want to read row by row and test if the row is company name, or address, or phone, or fax, or e-mail, or contact, and then update to the table. My original idea is to use TRIM to trim from the first character to ":" and test the keyword to identify the data.

Douglas, do you think TRIM will work in this case, too?
 
Trim only removes spaces, nothing more.

What you can do is, once more, use the Split function.

For example,

Split("Address: Room 1206, Block A, Focal Industrial Centre, 21 Man Lok
Street, Hung Hom, Kowloon, Hong Kong", ":")

will return an array with 2 elements. The first element (which will have
subscript 0) will be "Address", while the second element (subscript 1) will
be "Room 1206, Block A, Focal Industrial Centre, 21 Man Lok Street, Hung
Hom, Kowloon, Hong Kong" (actually, I'm lying: there will be some extra
blanks at the end of the 1st element, and some at the beginning of the 2nd
element because of spaces on either side of the colon. You can use Trim to
eliminate those, though)

If you're trying to remove part of a string, you need to use one (or more)
of Left, Right or Mid. You can use InStr (or InStrRev) to determine where a
particular character exists in the string.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Peter said:
Thanks Douglas. I read the explanation of the link. You are right. I
Click to expand...
have missed the words "get all rows". I will test the code and let you
know the result.
Before that, I would like to share with you what I suppose to do. Here is
Click to expand...
the sample data of the .txt file.
1. Bonnie Blair Ltd
Address: Room 1206, Block A, Focal Industrial Centre, 21 Man Lok Street, Hung Hom, Kowloon, Hong Kong
Phone: (852) 23287839
Fax: (852) 23284887
E-mail: (e-mail address removed)
Contact: Miss Charles Benchetrit, Managing Director
Miss Valerie-C Hoyau, Manager

2. Bosville Ltd
Address: 6/F, Union Park Centre, 771-775 Nathan Road, Mong Kok, Kowloon, Hong Kong
Phone: (852) 23810308
Fax: (852) 23970889
E-mail: (e-mail address removed)
Contact: Mr Albert Li, Director

As you see there is a pattern of the content. I want to read row by row
Click to expand...
and test if the row is company name, or address, or phone, or fax, or
e-mail, or contact, and then update to the table. My original idea is to
use TRIM to trim from the first character to ":" and test the keyword to
identify the data.
 
Hi Douglas,

I have tries some testing and believe I am starting to manage what I want to do. I need to code more details in order to have a clear picture.

Douglas, if I want to update the data from .txt to a table in Access. Do I need to define 2 connections? I have defined one connection (cnn1) points to the .txt file. Do I need ot define another connection such Set cnn2 = CurrentProject ? I am using ADO.

Peter
 
Yes, you'll need one connection object for the text file, and one connection
object for the Jet database (.MDB file)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Peter said:
Hi Douglas,

I have tries some testing and believe I am starting to manage what I want
Click to expand...
to do. I need to code more details in order to have a clear picture.
Douglas, if I want to update the data from .txt to a table in Access. Do
Click to expand...
I need to define 2 connections? I have defined one connection (cnn1) points
to the .txt file. Do I need ot define another connection such Set cnn2 =
CurrentProject ? I am using ADO.
 
Hi Douglas,

If it is a Word .doc instead of .txt, do you know how to open the document
and read row by row? or appreciate if you would direct me to find the
information.

Many thanks
 
Not sure what the best approach would be.

I don't believe there's an ODBC or Ole DB approach to allow you to link to
Word. You could use Automation to open the document. I've done lots of
Automation between Access and Word (and even a little to PowerPoint), but
not with Word, so I'm afraid I can't offer any specific advice.

Start with http://support.microsoft.com/?id=237337 and see if it leads you
anywhere.
 
You'll have to find out what those characters are.

When you've read a row that you believe should be blank, try code along the
lines of:

Dim intLoop As Integer

For intLoop = 1 To Len(strInput)
Debug.Print "Character " & intLoop & " is " & Asc(Mid(strInput,
intLoop, 1))
Next intLoop

where strInput is the contents of the supposedly blank line. That'll show
you what character(s) have been put into the line, which will let you either
ignore them or (better) determine why they're there.
 
According to the test, the funny characters are six "9" (999999). I really
don't understand why I cannot see these six 9 instead of a blank lines.
 
Chr(9) is a tab. Now you need to find out why there are 6 tabs instead of a
blank line.

Alternatively, as I mentioned before, you have the option of including 6
tabs in your check for a blank line.
 
Hi Douglas, many thanks to your advise so far. I learnt a lot from you.
Actually I am writing a data conversion program and the .txt file is the
source data.

I found another strange scenario. There is a single line of address in the
..txt file. When I browse the file using Notepad, I can see several ","
there. But when I use the method you advised me before to display the
address line, there is no comma, instead all comma have been changed to tab
chr(9).

Do you have any idea?
 
Sorry, no real idea, unless you're using a font in Notepad that for some
strange reason displays tabs as commas.
 
When I split a string, is it possible to know how many sub-strings that have
been splitted?
 
Back
Top