Link to home
Start Free TrialLog in
Avatar of nyquil
nyquil

asked on

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?
Avatar of RMatzka
RMatzka

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?
Sorry, please substitute the line "Close sFile" by "Close iFile".
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.

ASKER CERTIFIED SOLUTION
Avatar of sjpedro
sjpedro

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of nyquil

ASKER

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.
Avatar of nyquil

ASKER

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.
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.