VBA code to assemble data spread across several rows.

A

Antonio

I posted a question on 7/1/05 In the Access Import/Export Data group. One of
the three suggestions from a very kind person named John was to "write VBA
code to read the text file line by line, assemble records, and append them to
a table." But I am looking for example of such code (as I am new to the VBA
arena) Here is the problem, I have a linked table to a text file. When you
open the link the data displays like this:
Field1 Field2 Field3
Field4 Field5
M000443990 MORA,EDUARDO N M/30
07/05/05

1440

CARDIOLOGY
M000162333 GARCIA,HOPE J F/80
07/05/05

1000

CARDIOLOGY
The desired end result would be to bring all the data into one record by
Field1 and Field Two, like this.
Field1 Field2 Field3
Field4 Field5
M000999999 MORA,EDUARDO N M/30 07/05/05 1440 CARDIOLOGY
M000123456 GARCIA,HOPE J F/80 07/05/05 1000 CARDIOLOGY

Can anyone give examples of such code? OR...since I am not terribly familiar
with VBA, can anyone suggest code that would copy Field1 into all Null fields
below it until it reaches a "Not Is Null" field, and then copy the data that
is in that field to all Null Field1 fields below it, and then again, until it
reaches the last record in the table? (I saw something in the knowledge base
about something to do with Looping Structures? perhaps?)... Example:
From this:
Field1 Field2 Field3
Field4 Field5
M000999999 MORA,EDUARDO N M/30
07/05/05

1440

CARDIOLOGY
M000123456 GARCIA,HOPE J F/80
07/05/05

1000

CARDIOLOGY
To This:
Field1 Field2 Field3
Field4 Field5
M000999999 MORA,EDUARDO N M/30
M000999999 07/05/05
M000999999 1440
M000999999
CARDIOLOGY
M000123456 GARCIA,HOPE J F/80
M000123456 07/05/05
M000123456 1000
M000123456
CARDIOLOGY
What I am thinking (hoping) is that with the same information across the
corresponding rows, I can build a query that will bring all the info together
where Field1 is the same. (field 1 is never repeated..its unique to the
person.)

Any help would be Awsome!. Thank you.
 
A

Antonio

Sorry, the news group shifted the fields in my example...this is how it
should look:
Field1 Field2 Field3 Field4 Field5
M000999999 MORA,EDUARDO N M/30
07/05/05
1440

CARDIOLOGY
M000123456 GARCIA,HOPE J F/80
07/05/05
1000

CARDIOLOGY
To This:
Field1 Field2 Field3 Field4 Field5
M000999999 MORA,EDUARDO N M/30
M000999999 07/05/05
M000999999 1440
M000999999
CARDIOLOGY
M000123456 GARCIA,HOPE J F/80
M000123456 07/05/05
M000123456 1000
M000123456
CARDIOLOGY
 
A

Antonio

In the clarification, the word Cardiology is actually under field 5...not in
field 1...
Sorry for the confusion.
Antonio
 
J

John Nurick

Antonio, almost every message you have posted, both here and in
..externaldata, shows a different structure of the sample data. It now
seems that the [Field 1] value (which you previously described as [ID])
is repeated at the beginning of every line of a patient's record;
previously you said it was only present on the first line of each
patient's record.

See my message today in .externaldata for the wildcard search patterns
needed to do what I thought you wanted in Word. Meanwhile, I'm going to
stop chasing a moving target.
 
A

Antonio

John, being relatively weak in VBA, no experience in Perl, and not having
success with Word (and needing to get this process as automated as possible
by tuesday morning) I posted a question in VBA Programming that would copy
values down a column until it hit the next column that was not null. There by
I would have a key that would link the data that was spread across the 4
rows. Whenever I attempted to provide an example, this news group would shift
the location "cardio" to a fifth row under the Patient Identifier. which is
not the case (and why I tried to post a couple of clarifications after the
fact)
So...while it was related to our original posting on Import/Export group, I
was asking for help for a VBA solution that I think I would be able to manage
given my level of experience. (ie...a button that would run code to copy the
Patient # and then loop until the end of the table)
If you look at the example I ask for VBA code that would go from the first
example to what I have in the second example... (From this...To This). The
secod example, where the Patient number is repeated across the rows is not
meant to indicate what the file looks like now..just where I want to go.
So...for example purposes for the easier VBA solution I thougt I could handle
, you can omit the Field 5 and the word Cardiology all together and take that
to mean what the current file looks like.
So, My VBA question example is going from this.....

Field1 Field2 Field3 Field4
M000999999 MORA,EDUARDO N M/30
07/05/05
1440
M000123456 GARCIA,HOPE J F/80
07/05/05
1000

To this........
Field1 Field2 Field3 Field4
M000999999 MORA,EDUARDO N M/30
M000999999 07/05/05
M000999999 1440
M000123456 GARCIA,HOPE J F/80
M000123456 07/05/05
M000123456 1000

Sorry for the confusion. Still would appreciate any suggestions.
Antonio




John Nurick said:
Antonio, almost every message you have posted, both here and in
..externaldata, shows a different structure of the sample data. It now
seems that the [Field 1] value (which you previously described as [ID])
is repeated at the beginning of every line of a patient's record;
previously you said it was only present on the first line of each
patient's record.

See my message today in .externaldata for the wildcard search patterns
needed to do what I thought you wanted in Word. Meanwhile, I'm going to
stop chasing a moving target.

In the clarification, the word Cardiology is actually under field 5...not in
field 1...
Sorry for the confusion.
Antonio
Click to expand...
Click to expand...
 
J

John Nurick

Here is an "air code" VBA procedure which shows one approach to the
problem. It assumes among other things that every record has the same
number of lines in the same order, and that the padding between fields
in the text file consists of spaces.


Function XX()
Dim lngFN As Long
Dim strLine As String
Dim strF1 As String
Dim strF2 As String
Dim strF3 As String
Dim strF4 As String
Dim strF5 As String
Dim rsR As DAO.Recordset


Set rsR = CurrentDb.OpenRecordset("My Table")

lngFN = FreeFile()
Open "C:\Folder\file.txt" For Input As lngFN

Do Until EOF(lngFN)
Line Input #lngFN, strLine 'read first line
strLine = Trim(strLine)
strF1 = Left(strLine, InStr(strLine, " ") - 1)
strF2 = Trim(Mid(strLine, InStr(strLine, " ")))

Line Input #lngFN, strLine 'read second line
strF3 = Trim(strLine)
'repeat for lines 3 & 4 (and any others)
'...

With rsR 'add record to table
.AddNew
.Fields(0).Value = strF1
.Fields(1).Value = strF2
'and so on
.Update
End With
Loop

rsR.Close
Close #lngFN

End Function



John, being relatively weak in VBA, no experience in Perl, and not having
success with Word (and needing to get this process as automated as possible
by tuesday morning) I posted a question in VBA Programming that would copy
values down a column until it hit the next column that was not null. There by
I would have a key that would link the data that was spread across the 4
rows. Whenever I attempted to provide an example, this news group would shift
the location "cardio" to a fifth row under the Patient Identifier. which is
not the case (and why I tried to post a couple of clarifications after the
fact)
So...while it was related to our original posting on Import/Export group, I
was asking for help for a VBA solution that I think I would be able to manage
given my level of experience. (ie...a button that would run code to copy the
Patient # and then loop until the end of the table)
If you look at the example I ask for VBA code that would go from the first
example to what I have in the second example... (From this...To This). The
secod example, where the Patient number is repeated across the rows is not
meant to indicate what the file looks like now..just where I want to go.
So...for example purposes for the easier VBA solution I thougt I could handle
, you can omit the Field 5 and the word Cardiology all together and take that
to mean what the current file looks like.
So, My VBA question example is going from this.....

Field1 Field2 Field3 Field4
M000999999 MORA,EDUARDO N M/30
07/05/05
1440
M000123456 GARCIA,HOPE J F/80
07/05/05
1000

To this........
Field1 Field2 Field3 Field4
M000999999 MORA,EDUARDO N M/30
M000999999 07/05/05
M000999999 1440
M000123456 GARCIA,HOPE J F/80
M000123456 07/05/05
M000123456 1000

Sorry for the confusion. Still would appreciate any suggestions.
Antonio




John Nurick said:
Antonio, almost every message you have posted, both here and in
..externaldata, shows a different structure of the sample data. It now
seems that the [Field 1] value (which you previously described as [ID])
is repeated at the beginning of every line of a patient's record;
previously you said it was only present on the first line of each
patient's record.

See my message today in .externaldata for the wildcard search patterns
needed to do what I thought you wanted in Word. Meanwhile, I'm going to
stop chasing a moving target.

In the clarification, the word Cardiology is actually under field 5...not in
field 1...
Sorry for the confusion.
Antonio

:

Sorry, the news group shifted the fields in my example...this is how it
should look:
Field1 Field2 Field3 Field4 Field5
M000999999 MORA,EDUARDO N M/30
07/05/05
1440

CARDIOLOGY
M000123456 GARCIA,HOPE J F/80
07/05/05
1000

CARDIOLOGY
To This:
Field1 Field2 Field3 Field4 Field5
M000999999 MORA,EDUARDO N M/30
M000999999 07/05/05
M000999999 1440
M000999999
CARDIOLOGY
M000123456 GARCIA,HOPE J F/80
M000123456 07/05/05
M000123456 1000
M000123456
CARDIOLOGY


:

I posted a question on 7/1/05 In the Access Import/Export Data group. One of
the three suggestions from a very kind person named John was to "write VBA
code to read the text file line by line, assemble records, and append them to
a table." But I am looking for example of such code (as I am new to the VBA
arena) Here is the problem, I have a linked table to a text file. When you
open the link the data displays like this:
Field1 Field2 Field3
Field4 Field5
M000443990 MORA,EDUARDO N M/30
07/05/05

1440

CARDIOLOGY
M000162333 GARCIA,HOPE J F/80
07/05/05

1000

CARDIOLOGY
The desired end result would be to bring all the data into one record by
Field1 and Field Two, like this.
Field1 Field2 Field3
Field4 Field5
M000999999 MORA,EDUARDO N M/30 07/05/05 1440 CARDIOLOGY
M000123456 GARCIA,HOPE J F/80 07/05/05 1000 CARDIOLOGY

Can anyone give examples of such code? OR...since I am not terribly familiar
with VBA, can anyone suggest code that would copy Field1 into all Null fields
below it until it reaches a "Not Is Null" field, and then copy the data that
is in that field to all Null Field1 fields below it, and then again, until it
reaches the last record in the table? (I saw something in the knowledge base
about something to do with Looping Structures? perhaps?)... Example:
From this:
Field1 Field2 Field3
Field4 Field5
M000999999 MORA,EDUARDO N M/30
07/05/05

1440

CARDIOLOGY
M000123456 GARCIA,HOPE J F/80
07/05/05

1000

CARDIOLOGY
To This:
Field1 Field2 Field3
Field4 Field5
M000999999 MORA,EDUARDO N M/30
M000999999 07/05/05
M000999999 1440
M000999999
CARDIOLOGY
M000123456 GARCIA,HOPE J F/80
M000123456 07/05/05
M000123456 1000
M000123456
CARDIOLOGY
What I am thinking (hoping) is that with the same information across the
corresponding rows, I can build a query that will bring all the info together
where Field1 is the same. (field 1 is never repeated..its unique to the
person.)

Any help would be Awsome!. Thank you.
Click to expand...
Click to expand...
Click to expand...
 
A

Antonio

John, thank you for the help. It did the trick and I've met my deadline.
Again, sorry for the confusion. I appreciate all you have done.
Antonio

John Nurick said:
Here is an "air code" VBA procedure which shows one approach to the
problem. It assumes among other things that every record has the same
number of lines in the same order, and that the padding between fields
in the text file consists of spaces.


Function XX()
Dim lngFN As Long
Dim strLine As String
Dim strF1 As String
Dim strF2 As String
Dim strF3 As String
Dim strF4 As String
Dim strF5 As String
Dim rsR As DAO.Recordset


Set rsR = CurrentDb.OpenRecordset("My Table")

lngFN = FreeFile()
Open "C:\Folder\file.txt" For Input As lngFN

Do Until EOF(lngFN)
Line Input #lngFN, strLine 'read first line
strLine = Trim(strLine)
strF1 = Left(strLine, InStr(strLine, " ") - 1)
strF2 = Trim(Mid(strLine, InStr(strLine, " ")))

Line Input #lngFN, strLine 'read second line
strF3 = Trim(strLine)
'repeat for lines 3 & 4 (and any others)
'...

With rsR 'add record to table
.AddNew
.Fields(0).Value = strF1
.Fields(1).Value = strF2
'and so on
.Update
End With
Loop

rsR.Close
Close #lngFN

End Function



John, being relatively weak in VBA, no experience in Perl, and not having
success with Word (and needing to get this process as automated as possible
by tuesday morning) I posted a question in VBA Programming that would copy
values down a column until it hit the next column that was not null. There by
I would have a key that would link the data that was spread across the 4
rows. Whenever I attempted to provide an example, this news group would shift
the location "cardio" to a fifth row under the Patient Identifier. which is
not the case (and why I tried to post a couple of clarifications after the
fact)
So...while it was related to our original posting on Import/Export group, I
was asking for help for a VBA solution that I think I would be able to manage
given my level of experience. (ie...a button that would run code to copy the
Patient # and then loop until the end of the table)
If you look at the example I ask for VBA code that would go from the first
example to what I have in the second example... (From this...To This). The
secod example, where the Patient number is repeated across the rows is not
meant to indicate what the file looks like now..just where I want to go.
So...for example purposes for the easier VBA solution I thougt I could handle
, you can omit the Field 5 and the word Cardiology all together and take that
to mean what the current file looks like.
So, My VBA question example is going from this.....

Field1 Field2 Field3 Field4
M000999999 MORA,EDUARDO N M/30
07/05/05
1440
M000123456 GARCIA,HOPE J F/80
07/05/05
1000

To this........
Field1 Field2 Field3 Field4
M000999999 MORA,EDUARDO N M/30
M000999999 07/05/05
M000999999 1440
M000123456 GARCIA,HOPE J F/80
M000123456 07/05/05
M000123456 1000

Sorry for the confusion. Still would appreciate any suggestions.
Antonio




John Nurick said:
Antonio, almost every message you have posted, both here and in
..externaldata, shows a different structure of the sample data. It now
seems that the [Field 1] value (which you previously described as [ID])
is repeated at the beginning of every line of a patient's record;
previously you said it was only present on the first line of each
patient's record.

See my message today in .externaldata for the wildcard search patterns
needed to do what I thought you wanted in Word. Meanwhile, I'm going to
stop chasing a moving target.

On Sun, 3 Jul 2005 02:48:01 -0700, "Antonio"

In the clarification, the word Cardiology is actually under field 5...not in
field 1...
Sorry for the confusion.
Antonio

:

Sorry, the news group shifted the fields in my example...this is how it
should look:
Field1 Field2 Field3 Field4 Field5
M000999999 MORA,EDUARDO N M/30
07/05/05
1440

CARDIOLOGY
M000123456 GARCIA,HOPE J F/80
07/05/05
1000

CARDIOLOGY
To This:
Field1 Field2 Field3 Field4 Field5
M000999999 MORA,EDUARDO N M/30
M000999999 07/05/05
M000999999 1440
M000999999
CARDIOLOGY
M000123456 GARCIA,HOPE J F/80
M000123456 07/05/05
M000123456 1000
M000123456
CARDIOLOGY


:

I posted a question on 7/1/05 In the Access Import/Export Data group. One of
the three suggestions from a very kind person named John was to "write VBA
code to read the text file line by line, assemble records, and append them to
a table." But I am looking for example of such code (as I am new to the VBA
arena) Here is the problem, I have a linked table to a text file. When you
open the link the data displays like this:
Field1 Field2 Field3
Field4 Field5
M000443990 MORA,EDUARDO N M/30
07/05/05

1440

CARDIOLOGY
M000162333 GARCIA,HOPE J F/80
07/05/05

1000

CARDIOLOGY
The desired end result would be to bring all the data into one record by
Field1 and Field Two, like this.
Field1 Field2 Field3
Field4 Field5
M000999999 MORA,EDUARDO N M/30 07/05/05 1440 CARDIOLOGY
M000123456 GARCIA,HOPE J F/80 07/05/05 1000 CARDIOLOGY

Can anyone give examples of such code? OR...since I am not terribly familiar
with VBA, can anyone suggest code that would copy Field1 into all Null fields
below it until it reaches a "Not Is Null" field, and then copy the data that
is in that field to all Null Field1 fields below it, and then again, until it
reaches the last record in the table? (I saw something in the knowledge base
about something to do with Looping Structures? perhaps?)... Example:
From this:
Field1 Field2 Field3
Field4 Field5
M000999999 MORA,EDUARDO N M/30
07/05/05

1440

CARDIOLOGY
M000123456 GARCIA,HOPE J F/80
07/05/05

1000

CARDIOLOGY
To This:
Field1 Field2 Field3
Field4 Field5
M000999999 MORA,EDUARDO N M/30
M000999999 07/05/05
M000999999 1440
M000999999
CARDIOLOGY
M000123456 GARCIA,HOPE J F/80
M000123456 07/05/05
M000123456 1000
M000123456
CARDIOLOGY
What I am thinking (hoping) is that with the same information across the
corresponding rows, I can build a query that will bring all the info together
where Field1 is the same. (field 1 is never repeated..its unique to the
person.)

Any help would be Awsome!. Thank you.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

please help .. VBA to split data within the same field. 1
VBA code or formula needed to identify certain records in a database 1
Nested Do...Loops and Date Cell Defined as Date in VBA code 4
VBA Help...I need a lot 3
Execl DrWatson Error 80000007 3
non-VBA Alternative to SUBTITUTE function 6
Outlook crashes with Outlook connector 2002 0
Outlook connector crashes outlook 0

Top