Solved

VB 6.0 - Combine 3 ASCII files into 1 ASCII file

Posted on 2004-08-09
25
222 Views
Last Modified: 2010-05-02
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.
0
Comment
Question by:MelanieWatkins
  • 5
  • 5
  • 5
  • +4
25 Comments
 
LVL 22

Expert Comment

by:DarkoLord
ID: 11751320
Can you post samples of this files and what the result should look like?

Darko
0
 
LVL 7

Expert Comment

by:_agj_
ID: 11751505
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
 
LVL 7

Expert Comment

by:_agj_
ID: 11751517
do u have textpad?
0
 
LVL 26

Expert Comment

by:EDDYKT
ID: 11751545
just use copy command


ie

shell(environ("ComSpec") & " /c copy /b a.txt+b.txt+c.txt out.txt")
0
 
LVL 22

Expert Comment

by:DarkoLord
ID: 11751568
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
 
LVL 26

Expert Comment

by:EDDYKT
ID: 11751900
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
 

Author Comment

by:MelanieWatkins
ID: 11752193
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
 

Author Comment

by:MelanieWatkins
ID: 11752823
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
 
LVL 22

Expert Comment

by:DarkoLord
ID: 11752858
I think it would be easier to read values from Access database then to parse text files...

Darko
0
 

Author Comment

by:MelanieWatkins
ID: 11752894
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
 
LVL 19

Expert Comment

by:Shauli
ID: 11753223
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 19

Expert Comment

by:Shauli
ID: 11753265
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
 
LVL 19

Expert Comment

by:Shauli
ID: 11753277
No, wait with the last function  :(

S
0
 
LVL 19

Expert Comment

by:Shauli
ID: 11753502
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
 
LVL 14

Accepted Solution

by:
Tommy Kinard earned 500 total points
ID: 11754250
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
 

Author Comment

by:MelanieWatkins
ID: 11760168
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
 
LVL 19

Expert Comment

by:Shauli
ID: 11761086
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
 
LVL 7

Expert Comment

by:_agj_
ID: 11762813
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
 
LVL 7

Expert Comment

by:_agj_
ID: 11762868
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
 
LVL 7

Expert Comment

by:_agj_
ID: 11762979
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
 
LVL 14

Expert Comment

by:Matti
ID: 11766203
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
 

Author Comment

by:MelanieWatkins
ID: 12399097
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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now