Retriving data from a .txt file

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-...getstringx.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
http://I.Am/DougSteele
(no e-mails, please!)



"Peter" wrote in message
...
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.

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:
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:
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?


"Douglas J. Steele" wrote:

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-...getstringx.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
http://I.Am/DougSteele
(no e-mails, please!)



"Peter" wrote in message
...
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.

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
http://I.Am/DougSteele
(no e-mails, please!)



"Peter" wrote in message
...
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:
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:
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?


"Douglas J. Steele" wrote:

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-...getstringx.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
http://I.Am/DougSteele
(no e-mails, please!)



"Peter" wrote in message
...
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.

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





"Douglas J. Steele" wrote:

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
http://I.Am/DougSteele
(no e-mails, please!)



"Peter" wrote in message
...
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:
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:
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?


"Douglas J. Steele" wrote:

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-...getstringx.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
http://I.Am/DougSteele
(no e-mails, please!)



"Peter" wrote in message
...
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.

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
http://I.Am/DougSteele
(no e-mails, please!)



"Peter" wrote in message
...
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





"Douglas J. Steele" wrote:

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
http://I.Am/DougSteele
(no e-mails, please!)



"Peter" wrote in message
...
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:
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:
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?


"Douglas J. Steele" wrote:

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-...getstringx.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
http://I.Am/DougSteele
(no e-mails, please!)



"Peter" wrote in message
...
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.

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


"Douglas J. Steele" wrote:

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
http://I.Am/DougSteele
(no e-mails, please!)



"Peter" wrote in message
...
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





"Douglas J. Steele" wrote:

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
http://I.Am/DougSteele
(no e-mails, please!)



"Peter" wrote in message
...
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:
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:
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?


"Douglas J. Steele" wrote:

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-...getstringx.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
http://I.Am/DougSteele
(no e-mails, please!)



"Peter" wrote in message
...
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.

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)


"Peter" wrote in message
...
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


"Douglas J. Steele" wrote:

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
http://I.Am/DougSteele
(no e-mails, please!)



"Peter" wrote in message
...
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





"Douglas J. Steele" wrote:

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
http://I.Am/DougSteele
(no e-mails, please!)



"Peter" wrote in message
...
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:
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:
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?


"Douglas J. Steele" wrote:

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(reco
rdset)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-...getstringx.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
http://I.Am/DougSteele
(no e-mails, please!)



"Peter" wrote in message
...
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.

The reason I am asking is I am having with with "Blanks" and "blank line" in
the text file. I have put up another thread. Visually it is a blank line
but Access says it is not. In debug mode, when I point to the variable, it
displays some funny characters look like small letter r. But I don't think
they are r.

"Douglas J. Steele" wrote:

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)


"Peter" wrote in message
...
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


"Douglas J. Steele" wrote:

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
http://I.Am/DougSteele
(no e-mails, please!)



"Peter" wrote in message
...
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





"Douglas J. Steele" wrote:

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
http://I.Am/DougSteele
(no e-mails, please!)



"Peter" wrote in message
...
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:
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:
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?


"Douglas J. Steele" wrote:

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(reco
rdset)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-...getstringx.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
http://I.Am/DougSteele
(no e-mails, please!)



"Peter" wrote in message
...
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.

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)


"Peter" wrote in message
...
The reason I am asking is I am having with with "Blanks" and "blank line"
in
the text file. I have put up another thread. Visually it is a blank line
but Access says it is not. In debug mode, when I point to the variable,
it
displays some funny characters look like small letter r. But I don't
think
they are r.

"Douglas J. Steele" wrote:

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)


"Peter" wrote in message
...
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


"Douglas J. Steele" wrote:

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
http://I.Am/DougSteele
(no e-mails, please!)



"Peter" wrote in message
...
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





"Douglas J. Steele" wrote:

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
http://I.Am/DougSteele
(no e-mails, please!)



"Peter" wrote in message
...
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:
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:
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?


"Douglas J. Steele" wrote:

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(reco
rdset)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-...getstringx.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
http://I.Am/DougSteele
(no e-mails, please!)



"Peter" wrote in message
...
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.