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.
MelanieWatkinsAsked:
Who is Participating?
 
Tommy KinardConnect With a Mentor Commented:
Hi MelanieWatkins,

The below subs will look into the learner file and write to a new file. It will then open the Aim file write the data that matches the learner data to a file then does the same thing with the SEF data, after all of that, it reads the next record in the Learner Data and starts over. Basically it open all three files and writes the data into another with the records combined/sorted. This will only work if the original files are sorted.

Private Sub MakeLeaner()
    Dim MyInfo As String
    Dim LearnerRec As String
    'open files
    Open "c:\learner.txt" For Input As #1  '<-- Learner Data
    Open "c:\learner1.txt" For Input As #2  '<--Learning Aim
    Open "c:\learner2.txt" For Input As #3  '<-- SEF Data
    Open "c:\learnerout.txt" For Output As #4  '<-- Output file
    While Not EOF(1)
        Line Input #1, MyInfo
        'extract learner info
        LearnerRec = Mid(MyInfo, 9, 12)
        'print to file first record
        Print #4, MyInfo & vbCrLf
        Call GetSecFilInfo(LearnerRec, 2)
        Call GetThrdFilInfo(LearnerRec, 3)
    Wend
    'close files
    Close (1)
    Close (2)
    Close (3)
    Close (4)
End Sub
Private Sub GetSecFilInfo(LearnerRec As String, FilId As Integer)
    Static HldInfo As String
    If HldInfo = vbNullString Then
        Line Input #FilId, HldInfo
    End If
    While Mid(HldInfo, 9, 12) = LearnerRec
        Print #4, HldInfo & vbCrLf
        Line Input #FilId, HldInfo
    Wend
End Sub
Private Sub GetThrdFilInfo(LearnerRec As String, FilId As Integer)
    Static HldInfo As String
    If HldInfo = vbNullString Then
        Line Input #FilId, HldInfo
    End If
    While Mid(HldInfo, 9, 12) = LearnerRec
        Print #4, HldInfo & vbCrLf
        Line Input #FilId, HldInfo
    Wend
End Sub

HTH
dragontooth

0
 
DarkoLordCommented:
Can you post samples of this files and what the result should look like?

Darko
0
 
_agj_Commented:
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)
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
_agj_Commented:
do u have textpad?
0
 
EDDYKTCommented:
just use copy command


ie

shell(environ("ComSpec") & " /c copy /b a.txt+b.txt+c.txt out.txt")
0
 
DarkoLordCommented:
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
0
 
EDDYKTCommented:
If the record is in acsii format, then why not just append together?

from the title

Combine 3 ASCII files into 1 ASCII file
0
 
MelanieWatkinsAuthor Commented:
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  
0
 
MelanieWatkinsAuthor Commented:
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.
0
 
DarkoLordCommented:
I think it would be easier to read values from Access database then to parse text files...

Darko
0
 
MelanieWatkinsAuthor Commented:
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.
0
 
ShauliCommented:
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(1))
                    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
0
 
ShauliCommented:
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
0
 
ShauliCommented:
No, wait with the last function  :(

S
0
 
ShauliCommented:
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
0
 
MelanieWatkinsAuthor Commented:
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).
0
 
ShauliCommented:
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,fieldname3,fieldname4,fieldname5,fieldname6,.....
1155080,3349BBBBBBBB100,0,0,NWatkins,Melanie......

So if more than one record exist in table two for a given learner than the file should read:
fieldname1,fieldname2,fieldname3,fieldname4,fieldname5,fieldname6,.....
1155080,3349BBBBBBBB100,0,0,NWatkins,Melanie......
1155080,3349BBBBBBBB940,1,8,,,,,,,,,xxx,,y
.....

Let us know if this would work for you, and we can return with a sample code.

S
0
 
_agj_Commented:
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?
0
 
_agj_Commented:
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..
0
 
_agj_Commented:
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?
0
 
MattiCommented:
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
0
 
MelanieWatkinsAuthor Commented:
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.
0
All Courses

From novice to tech pro — start learning today.