?
Solved

Split text files

Posted on 2003-02-27
7
Medium Priority
?
181 Views
Last Modified: 2012-03-15
I have a text file that I need to process. I need to split the file and create a list of each elements. Then, I will process
the list in an excel file. This seems simple, but I cannot figure out how to do it. Here is a sample
from the file:

4821234 34565BC +0000000000000000.2 +00000000.4 Johnson J Perter


how can I split this text file into diffrent fields, following a layout?
0
Comment
Question by:nyquil
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 

Expert Comment

by:RMatzka
ID: 8036471
It is not quite clear for me what exaclty your problem is, but anyway, the tasks seems indeed to be simple.

The first thing you do is read the file line by line and store all the lines in a string array:
Dim iFile As Integer
Dim sFile As String
Dim sLine() As String
Dim iLineNumber As Integer
iFile=FreeFile
Open sFile For Input As iFile
Do Until EOF(iFile)
    ReDim Preserve sLine(iLineNumber)
    Line Input #iFile, sLine(iLineNumber)
    iLineNumber = iLineNumber + 1
Loop
Close sFile

Then you split each line into components (this can be done inside the above reading loop or in a second loop). From your example it looks like the fields are separated by blanks, so you simply use the Split function:
Dim sField() as String
sField=Split(sLine(iLineNumber)," ")
Now Ubound(sField) - 1 is the number of fields in this line, and you have the elements in the fields of the sField() array.

Is that helpful?
0
 

Expert Comment

by:RMatzka
ID: 8036487
Sorry, please substitute the line "Close sFile" by "Close iFile".
0
 
LVL 1

Expert Comment

by:MN_Dave
ID: 8037298
If you know the structure of the data that is in the text file you could do something like this.
***********************
Type iBuff  
   ID As String * 10
   Name As String * 20
End Type

Dim iRec As iBuff
Dim x as Integer
Dim rCnt as Integer

' Open file for random access.
Open "MyFile" For Random As #1 Len = Len(iRec)
' Read the file using the Get statement.

Do Until EOF(1)
 x = x + 1
 Get #1, x, iRec  
  ...Some Code to process...
loop

Close #1   ' Close file.
**************************
The "Get" statement moves the record into the iBuff structure. You can then access each element as iRec.ID and iRec.Name.

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Accepted Solution

by:
sjpedro earned 300 total points
ID: 8037460
nyquil
Here is something similar to MN Dave's.
Except this is done using the File System Object

This would work under the assumtion that you know the source and target file layout
On this sample I gave the new list fields a lenght of 20.
Then I assumed that you wanted to split this records into four fields.
Hope this helps

This is the source.txt file:
4821234 34565BC +0000000000000000.2 +00000000.4 Johnson J Perter

This is the new list:
Johnson J Perter    +0000000000000000.2 +00000000.4         4821234 34565BC  


Private Sub Form_Load()

    Dim fSys As New FileSystemObject
    Dim fsoSourceFile As Scripting.TextStream
    Dim fsoTargetFile As Scripting.TextStream
    Dim strSourceRecord As String
    Dim strTargerRecord As String
    'Field variables and new file layout, each field is a lenght of 20 positions
    Dim strRecord01 As String * 20
    Dim strRecord02 As String * 20
    Dim strRecord03 As String * 20
    Dim strRecord04 As String * 20
     
    'Open file to Read
    Set fsoSourceFile = fSys.OpenTextFile("C:\source.txt", ForReading) '
   
    'create new file
    Set fsoTargetFile = fSys.CreateTextFile("C:\target.txt", True)
   
    Do While Not fsoSourceFile.AtEndOfStream
        strSourceRecord = fsoSourceFile.ReadLine
       
        strRecord01 = Mid(strSourceRecord, 49, 16)
        strRecord02 = Mid(strSourceRecord, 17, 19)
        strRecord03 = Mid(strSourceRecord, 37, 11)
        strRecord04 = Mid(strSourceRecord, 1, 15)
       
        strTargetRecord = strRecord01 & strRecord02 & strRecord03 & strRecord04
        fsoTargetFile.WriteLine strTargetRecord    ' write record to new file
                   
     Loop
   
    fsoSourceFile.Close
    fsoTargetFile.Close
    Set fsoSourceFile = Nothing
    Set fsoTargetFile = Nothing
    Unload Me
     
End Sub
0
 

Author Comment

by:nyquil
ID: 8041800
Sipedro:
when you wrote:
strRecord01 = Mid(strSourceRecord, 49,16)

what does that means?
Sorry for this type of question but I'm still learning alot of things I don't know.
0
 

Author Comment

by:nyquil
ID: 8041946
Sipedro:
when you wrote:
strRecord01 = Mid(strSourceRecord, 49,16)

what does that means?
Sorry for this type of question but I'm still learning alot of things I don't know.
0
 
LVL 1

Expert Comment

by:sjpedro
ID: 8042482
it means whatever is at the starting position 49 and for the lenght of 16 bytes from the source record is assigned to the variable.
this information about the position I made it up, yours should be different according to the file layout.

I hope it helps.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…
Suggested Courses
Course of the Month11 days, 5 hours left to enroll

770 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