MelanieWatkins
asked on
VB 6.0 - Combine 3 ASCII files into 1 ASCII file
I have 3 ASCII files of records:
1. Learner Data (one record per learner)
2. Learning Aim (zero to many records per learner)
3. SEF Data (zero to many records per learner)
They have a common link of Learner Number.
I need to now create one ASCII file of these records in a particular order and keep the field type and size accurately from the original 3 files. (Each of the files has a varied amount of field names, sizes and data types).
The order should be the first Learner Number record from Learner Data file as a single computer record (fixed length as already setup in original file, no field separators), use the character pair, carriage return followed by line feed as terminator THEN any related records for that Learner Number from Learning Aim file (again as single records with carriage retrun, line feed, etc.) THEN any related records for that Learner Number from SEF Data file. My vb skills are limited, I have no idea how to code this. I started off with raw data and amazed I have got this far (using ACCESS). Urgent piece of work to be uploaded to government office system tomorrow! HELP.
1. Learner Data (one record per learner)
2. Learning Aim (zero to many records per learner)
3. SEF Data (zero to many records per learner)
They have a common link of Learner Number.
I need to now create one ASCII file of these records in a particular order and keep the field type and size accurately from the original 3 files. (Each of the files has a varied amount of field names, sizes and data types).
The order should be the first Learner Number record from Learner Data file as a single computer record (fixed length as already setup in original file, no field separators), use the character pair, carriage return followed by line feed as terminator THEN any related records for that Learner Number from Learning Aim file (again as single records with carriage retrun, line feed, etc.) THEN any related records for that Learner Number from SEF Data file. My vb skills are limited, I have no idea how to code this. I started off with raw data and amazed I have got this far (using ACCESS). Urgent piece of work to be uploaded to government office system tomorrow! HELP.
not very sure of the format of the files (require sample like darkolord says)....
are u files continuous ascii text?
or are the records separated by ssome character? some identifyable delimiting character...(in that case u can use excel to do this stuff in a bang)
are u files continuous ascii text?
or are the records separated by ssome character? some identifyable delimiting character...(in that case u can use excel to do this stuff in a bang)
do u have textpad?
just use copy command
ie
shell(environ("ComSpec") & " /c copy /b a.txt+b.txt+c.txt out.txt")
ie
shell(environ("ComSpec") & " /c copy /b a.txt+b.txt+c.txt out.txt")
That would just append those files together, but he needs to copy the records and their related records from other files one by one...
Darko
Darko
If the record is in acsii format, then why not just append together?
from the title
Combine 3 ASCII files into 1 ASCII file
from the title
Combine 3 ASCII files into 1 ASCII file
ASKER
Final file should look something like this - each record 365 long, not a problem, have 3 text files now that all have correct field format and size, each record 365 characters. It is just joining them like this without manual copy and paste!
There is a learner data record for 3349BBBBBBBB, followed by a record matching that learner number from learning aim data set and then a record for that learner from esf data set.
1155080 3349BBBBBBBB100 0 0 NWatkins Melanie 23F20 0 ST16 1NH49 The Heavens h49 The Heavens h49 The Heavens h49 The Heavens hST16 1NH0123457894561230 39012345678900 0 0 0 0 0 0 0 0 0 2 0 0 0 0 0 0 0 0 0
1155080 3349BBBBBBBB10990 0 4105000000 0 0 0 0 0 0 0 0 00 000 0 ST11 5HE70000 38170 38170 38170 0 20 0 0 00 0 0 0 0 0 0 0 0 0
1155080 3349BBBBBBBB10991 0 682003 0682003 0682003 09998989999D 9797970 031020EM3111
1155080 5302BBBBBBBB100 0 0 NByatt Dave 25M20 0 ST16 1NH49 The Heavens h49 The Heavens h49 The Heavens h49 The Heavens hST16 1NH0123457894561230 39012345678900 0 0 0 0 0 0 0 0 0 3 0 0 0 0 0 0 0 0 0
There is a learner data record for 3349BBBBBBBB, followed by a record matching that learner number from learning aim data set and then a record for that learner from esf data set.
1155080 3349BBBBBBBB100 0 0 NWatkins Melanie 23F20 0 ST16 1NH49 The Heavens h49 The Heavens h49 The Heavens h49 The Heavens hST16 1NH0123457894561230 39012345678900 0 0 0 0 0 0 0 0 0 2 0 0 0 0 0 0 0 0 0
1155080 3349BBBBBBBB10990 0 4105000000 0 0 0 0 0 0 0 0 00 000 0 ST11 5HE70000 38170 38170 38170 0 20 0 0 00 0 0 0 0 0 0 0 0 0
1155080 3349BBBBBBBB10991 0 682003 0682003 0682003 09998989999D 9797970 031020EM3111
1155080 5302BBBBBBBB100 0 0 NByatt Dave 25M20 0 ST16 1NH49 The Heavens h49 The Heavens h49 The Heavens h49 The Heavens hST16 1NH0123457894561230 39012345678900 0 0 0 0 0 0 0 0 0 3 0 0 0 0 0 0 0 0 0
ASKER
If it helps I have the data in 3 Access tables and then have set up file export specifications to get them to ASCII text files.
I think it would be easier to read values from Access database then to parse text files...
Darko
Darko
ASKER
Thanks for that Darko but it means nothing to me. I have tried everything in my limited Access and VB brain and it is like treading mud as far as I am concerned, have deadline early tomorrow and have no idea where to go next with this.
Pls modify the filenames and locations: I named the target file "final.txt".
Private Sub Command1_Click()
Dim myString As String, mySplit() As String, LearnerCode As String
'open target file
Open "c:\my documents\final.txt" For Output As #1
'open learner data file
Open "c:\my documents\data.txt" For Input As #2
Do Until EOF(2)
Line Input #2, myString
mySplit = Split(myString, Chr(32), -1)
If UBound(mySplit) > 0 Then
LearnerCode = ExtractLearnerCode(mySplit
Print #1, myString
'look for records in aim and sef
Print #1, ExtractFromAimSef("aim", LearnerCode)
Print #1, ExtractFromAimSef("sef", LearnerCode)
End If
Loop
Close #2
MsgBox "Done"
Close #1
End Sub
Private Function ExtractLearnerCode(ByVal fnLine As String) As String
Dim posChar As Integer, tmpCode As String
For posChar = 1 To Len(fnLine)
tmpCode = Mid(fnLine, posChar, 1)
If posChar > 8 Then
If IsNumeric(tmpCode) Then
ExtractLearnerCode = Left(fnLine, posChar - 1)
Exit For
End If
End If
Next posChar
End Function
Private Function ExtractFromAimSef(ByVal fnFile As String, fnLCode As String) As String
Dim LLine As String
Open "c:\my documents\" & fnFile & ".txt" For Input As #3
Do Until EOF(3)
Line Input #3, LLine
If InStr(1, LLine, fnLCode) Then
ExtractFromAimSef = LLine
Exit Do
End If
Loop
Close #3
End Function
S
Private Sub Command1_Click()
Dim myString As String, mySplit() As String, LearnerCode As String
'open target file
Open "c:\my documents\final.txt" For Output As #1
'open learner data file
Open "c:\my documents\data.txt" For Input As #2
Do Until EOF(2)
Line Input #2, myString
mySplit = Split(myString, Chr(32), -1)
If UBound(mySplit) > 0 Then
LearnerCode = ExtractLearnerCode(mySplit
Print #1, myString
'look for records in aim and sef
Print #1, ExtractFromAimSef("aim", LearnerCode)
Print #1, ExtractFromAimSef("sef", LearnerCode)
End If
Loop
Close #2
MsgBox "Done"
Close #1
End Sub
Private Function ExtractLearnerCode(ByVal fnLine As String) As String
Dim posChar As Integer, tmpCode As String
For posChar = 1 To Len(fnLine)
tmpCode = Mid(fnLine, posChar, 1)
If posChar > 8 Then
If IsNumeric(tmpCode) Then
ExtractLearnerCode = Left(fnLine, posChar - 1)
Exit For
End If
End If
Next posChar
End Function
Private Function ExtractFromAimSef(ByVal fnFile As String, fnLCode As String) As String
Dim LLine As String
Open "c:\my documents\" & fnFile & ".txt" For Input As #3
Do Until EOF(3)
Line Input #3, LLine
If InStr(1, LLine, fnLCode) Then
ExtractFromAimSef = LLine
Exit Do
End If
Loop
Close #3
End Function
S
One correction, as there can be more than one record in aim and sef:
Replace the last function with this one:
Private Function ExtractFromAimSef(ByVal fnFile As String, fnLCode As String) As String
Dim LLine As String
Open "c:\my documents\" & fnFile & ".txt" For Input As #3
Do Until EOF(3)
Line Input #3, LLine
If InStr(1, LLine, fnLCode) Then
ExtractFromAimSef = LLine
End If
Loop
Close #3
End Function
S
Replace the last function with this one:
Private Function ExtractFromAimSef(ByVal fnFile As String, fnLCode As String) As String
Dim LLine As String
Open "c:\my documents\" & fnFile & ".txt" For Input As #3
Do Until EOF(3)
Line Input #3, LLine
If InStr(1, LLine, fnLCode) Then
ExtractFromAimSef = LLine
End If
Loop
Close #3
End Function
S
No, wait with the last function :(
S
S
OK. This is the last function, replace pls. You will have some empty lines in the final file. If this is a problem then we can run a cleanup after we merge.
Private Function ExtractFromAimSef(ByVal fnFile As String, fnLCode As String) As String
Dim LLine As String
cntLine = 0
Open "c:\my documents\" & fnFile & ".txt" For Input As #3
Do Until EOF(3)
Line Input #3, LLine
If InStr(1, LLine, fnLCode) Then
ExtractFromAimSef = ExtractFromAimSef & LLine & vbNewLine
End If
Loop
Close #3
End Function
S
Private Function ExtractFromAimSef(ByVal fnFile As String, fnLCode As String) As String
Dim LLine As String
cntLine = 0
Open "c:\my documents\" & fnFile & ".txt" For Input As #3
Do Until EOF(3)
Line Input #3, LLine
If InStr(1, LLine, fnLCode) Then
ExtractFromAimSef = ExtractFromAimSef & LLine & vbNewLine
End If
Loop
Close #3
End Function
S
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Think I have a problem in the fact that the ASCII text files I have do not have field names (only data) and the code searches for records by learner reference to tie the records from the 3 files together. Not sure if there is a way around that or if I have to go back and start again by taking the data direct from the 3 Access tables and setting up the data type, spacing etc. in vb rather than setting in export specifications? (that is what I have done to get the 3 ASCII text files I have).
Can comma separated file (.csv) do? If so, and we can return with a code to work with the access tables. I see something like:
fieldname1,fieldname2,fiel
1155080,3349BBBBBBBB100,0,
So if more than one record exist in table two for a given learner than the file should read:
fieldname1,fieldname2,fiel
1155080,3349BBBBBBBB100,0,
1155080,3349BBBBBBBB940,1,
.....
Let us know if this would work for you, and we can return with a sample code.
S
fieldname1,fieldname2,fiel
1155080,3349BBBBBBBB100,0,
So if more than one record exist in table two for a given learner than the file should read:
fieldname1,fieldname2,fiel
1155080,3349BBBBBBBB100,0,
1155080,3349BBBBBBBB940,1,
.....
Let us know if this would work for you, and we can return with a sample code.
S
easiest way is to go back to ur access tables:
sort each of the tables by ur learner id...or whatever ur learners unique feature is....
(this can be done jus by opening the table, right clicking on the required column and pressing sort ascending, etc.)
1. Learner Data (one record per learner)
2. Learning Aim (zero to many records per learner)
3. SEF Data (zero to many records per learner)
for each table do the following:
now copy off the data by doing a select all and paste in some gud editor.
then cutpaste...
is this also too much work?
sort each of the tables by ur learner id...or whatever ur learners unique feature is....
(this can be done jus by opening the table, right clicking on the required column and pressing sort ascending, etc.)
1. Learner Data (one record per learner)
2. Learning Aim (zero to many records per learner)
3. SEF Data (zero to many records per learner)
for each table do the following:
now copy off the data by doing a select all and paste in some gud editor.
then cutpaste...
is this also too much work?
we cud do this with jus an sql query in access and then exporting it to a file...but i guess that wont help u at THIS juncture...with this lil time..
do u really need the output in a text file as u said...?
access reports are a gud way to do this....but again...i'd need ur database.
how many unique learner ids do u have?
access reports are a gud way to do this....but again...i'd need ur database.
how many unique learner ids do u have?
This will combine 3 files record len 365 to one.
Private Sub Command1_Click()
Dim i&, x&
Open App.Path + "\my.txt" For Random As #1 Len = 365
Open App.Path + "\LearnerData.txt" For Random As #2 Len = 365
Open App.Path + "\LearningAim.txt" For Random As #3 Len = 365
Open App.Path + "\SEFData.txt" For Random As #4 Len = 365
For i = 1 To 4000
If EOF(1) Then Exit For
Get #1, i, myrecord
x = x + 1
Put #2, x, myrecord
Get #1, i, myrecord
x = x + 1
Put #3, x, myrecord
Get #1, i, myrecord
x = x + 1
Put #4, x, myrecord
Next i
Close #1
Close #2
Close #3
Close #4
End Sub
If the task is more complex set a empty copy of the database available or download and run this kind of utill
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=8210&lngWId=1
And set the output code available so we can make the whole system on our computers.
Much simpler will be to work whit the database and make a set of querys to combine data from it to a file.
Matti
Private Sub Command1_Click()
Dim i&, x&
Open App.Path + "\my.txt" For Random As #1 Len = 365
Open App.Path + "\LearnerData.txt" For Random As #2 Len = 365
Open App.Path + "\LearningAim.txt" For Random As #3 Len = 365
Open App.Path + "\SEFData.txt" For Random As #4 Len = 365
For i = 1 To 4000
If EOF(1) Then Exit For
Get #1, i, myrecord
x = x + 1
Put #2, x, myrecord
Get #1, i, myrecord
x = x + 1
Put #3, x, myrecord
Get #1, i, myrecord
x = x + 1
Put #4, x, myrecord
Next i
Close #1
Close #2
Close #3
Close #4
End Sub
If the task is more complex set a empty copy of the database available or download and run this kind of utill
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=8210&lngWId=1
And set the output code available so we can make the whole system on our computers.
Much simpler will be to work whit the database and make a set of querys to combine data from it to a file.
Matti
ASKER
Points and grade were awarded to Dragontooth. After accepting the answer another related query came up which they continued to help me with. All now resolved. Many thanks. Apologies for the delay but I have not been in the office for the last week and a half.
Darko