• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 182
  • Last Modified:

Split text files

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
nyquil
Asked:
nyquil
  • 2
  • 2
  • 2
  • +1
1 Solution
 
RMatzkaCommented:
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
 
RMatzkaCommented:
Sorry, please substitute the line "Close sFile" by "Close iFile".
0
 
MN_DaveCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
sjpedroCommented:
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
 
nyquilAuthor Commented:
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
 
nyquilAuthor Commented:
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
 
sjpedroCommented:
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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now