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?
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?
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.
***********************
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.
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.
this information about the position I made it up, yours should be different according to the file layout.
I hope it helps.
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(iLineNu
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?