[howto] Fetch data from txtfile and segregate it into corresponding field.

[hardcox]

Hello guys,

I read somewhere that it is possible to extract data from txt file to excel. I have a similar issue only that I have to extract the data into its respected field. Eg. The name, age, address etc. The txt file has indicator like the name:, age:, gender: the numbers 1 to 5 is always the indicator for address and A to D is always for phone numbers. I will attached the file for your reference. Also in the present and old address the 1st address in the txt file is always the present address and the last address is always the old address.

Hope someone can help me out.

Thank you.

[Kenneth Hobs]

If you can have more than one record, called a row in Excel, in the text file, please post that example text file.

[hardcox]

Im sorry I didnt catch that. What do you mean?

[Kenneth Hobs]

Is that what a typical text file would look like? It would only add one row or record in database speak. There is usually more than one record in a text file. If all you have is one, then it is even easier.

You have 7 "address" line in the text file but only 4 columns for them in the Excel file. How is that handled?

[hardcox]

Yes that would be the typical textfile would look like the format is always the same like that, and yes Its only one txfile although data in the textfile do change by just copy and pasting it there. Im sorry I overlooked that, it should be 7 address in the excel file, but there are instances that address in txtfile will be less 7 but will not be more than 7 and the one who apears the number 1 is the present address and who apears the last is always the old address and 7 phone numbers.

[Kenneth Hobs]

Sub Test_GetData()
GetData ThisWorkbook.Path & "\Data.txt"
End Sub

Sub GetData(filePath As String)
Dim aData() As String, nRow As Long, indexLastAddress As Integer, i As Integer
Dim r As Range

aData() = Split(StrFromTXTFile(filePath), vbCrLf)
'MsgBox Join(aData, vbLf)
nRow = Range("A" & Rows.Count).End(xlUp).Row + 1

Range("A" & nRow).Value2 = strAfterSpace(aData(0)) 'Name
Range("B" & nRow).Value2 = strAfterSpace(aData(1)) 'Age
Range("C" & nRow).Value2 = strAfterSpace(aData(2)) 'Gender
Range("D" & nRow).Value2 = strAfterSpace(aData(3)) 'Address 1

'Find Index of Last Address
For i = LBound(aData) To UBound(aData)
If aData(i) = "" Then
indexLastAddress = i - 1
Exit For
End If
Next i
Range("E" & nRow).Value2 = strAfterSpace(aData(indexLastAddress)) 'Last Address

'Add other addresses if needed
Set r = Range("F" & nRow)
If indexLastAddress = 4 Then GoTo Phones
For i = 4 To indexLastAddress - 1
r.Value2 = strAfterSpace(aData(i)) 'Next address
Set r = r.Offset(0, 1)
Next i

Phones:
For i = (indexLastAddress + 2) To UBound(aData)
r.Value2 = strAfterSpace(aData(i)) 'Next address
Set r = r.Offset(0, 1)
Next i

ActiveSheet.UsedRange.Columns.AutoFit
End Sub

Function StrFromTXTFile(filePath As String) As String
Dim str As String, hFile As Integer

If Dir(filePath) = "" Then
StrFromTXTFile = "NA"
Exit Function
End If

hFile = FreeFile
Open filePath For Binary Access Read As #hFile
str = Input(LOF(hFile), hFile)
Close hFile

StrFromTXTFile = str
End Function

Function strAfterSpace(aString As String) As String
Dim i As Integer
i = InStr(aString, " ")
strAfterSpace = Right(aString, Len(aString) - i)
End Function

[hardcox]

Thanks a million I will try that.

Result:

It works only that, If one info is missing eg (name, age or gender) it will result to error or the info will no longer be align in the field. There are some instances in my txtfile that there are no age, gender and names. Would it be possible that if no info it will just leave a blank? Anyway how does this able to indicate that it is a name?

Range("A" & nRow).Value2 = strAfterSpace(aData(0)) 'Name

[hardcox]

Mr. Kenneth,

Im having a hardtime modifying the code, since Im adding some Infos like First, Maiden, Last Name. How can I indicate it in the txtfile? Like this one

Range("A" & nRow).Value2 = strAfterSpace(aData(0)) 'Name

How did it know that it should fetch the data in name? Based on the format in the textfile? I see that it fetch the data after space, would it be possible to put indicator in the code like Name or 1-10 or A-Z and a delimiter ":" instead of "space"?

[Kenneth Hobs]

My first solution was based on knowing the structure of your data file.

If you don't know structure, then you need some other logic to make a complete solution. To that end, I will show you how to do use labels to determine what parts of the data to extract and to where. In this example, I show you how to find the Name, Age, and Gender labels and where to put them with like named column headings. Using this method, order in the data file and the Excel file do not matter.

Sub Test_GetData2()
GetData2 ThisWorkbook.Path & "\Data.txt"
End Sub

Sub GetData2(filePath As String)
Dim aData() As String, nRow As Long, indexLastAddress As Integer, i As Integer
Dim rc As Range, r As Range
Dim cNames() As String, vArray() As Variant, e As Variant
Dim idx As Long

cNames() = Split("Name,Age,Gender", ",")

aData() = Split(StrFromTXTFile(filePath), vbCrLf)
nRow = Range("A" & Rows.Count).End(xlUp).Row + 1

'Range("A" & nRow).Value2 = strAfterSpace(aData(0)) 'Name
'Range("B" & nRow).Value2 = strAfterSpace(aData(1)) 'Age
'Range("C" & nRow).Value2 = strAfterSpace(aData(2)) 'Gender
'Range("D" & nRow).Value2 = strAfterSpace(aData(3)) 'Address 1

vArray() = PrefixArray(aData)
'MsgBox Join(vArray, vbLf)

For Each e In cNames()
Set rc = Range("A1", Cells(1, Columns.Count).End(xlToLeft))
Set r = rc.Find(e)
If r Is Nothing Then GoTo Nexte
idx = Index(vArray(), e) - 1
If idx < 0 Then GoTo Nexte
Cells(nRow, r.Column).Value2 = strAfterSpace(aData(idx))
Nexte:
Next e

ActiveSheet.UsedRange.Columns.AutoFit
End Sub

Function PrefixArray(sArray() As String) As Variant
Dim i As Integer, vArray() As Variant, e As Variant
ReDim vArray(LBound(sArray) To UBound(sArray))

For i = LBound(sArray) To UBound(sArray)
vArray(i) = Split(sArray(i), " ")(0)
On Error Resume Next
If Right(vArray(i), 1) = ":" Then vArray(i) = Left(vArray(i), Len(vArray(i)) - 1)
Next i
PrefixArray = vArray()
End Function

'val is not case sensitive
Function Index(vArray() As Variant, val As Variant) As Long
On Error GoTo Minus1
Index = WorksheetFunction.Match(val, WorksheetFunction.Transpose(vArray), 0)
Exit Function
Minus1:
Index = -1
End Function

[hardcox]

Thanks Kenneth I somehow manage to make it work. Its just I am having a hard time revising it.

For i = LBound(aData) To UBound(aData) If aData(i) = "" Then indexLastAddress = i - 1 Exit For End If Next i Range("E" & nRow).Value2 = strAfterSpace(aData(indexLastAddress)) 'Last Address




Im want to modify it to make the last phone number to be equal to the lold address. Its also always that way that the last phone number is the phone number of your old address.

[Kenneth Hobs]

If the last element in the array is the last phone number as shown in your data file, then the Ubound() function is used to get the index position of the last element in the array.

Say that you Set r=rc.Find("Last Phone Number") then:
Cells(nRow, r.Column).Value2 = strAfterSpace(aData(ubound(aData)))

[hardcox]

Kenneth,

Thanks for the idea, I was trying to work on the second code you provided, but I always have a problem with it also I noticed that the data in Cnames should also exactly the same name in the field. Another problem is that the change format in txtfile is now different due to the new process I made. Attached is the new format I will be working for now on. Only the txt format changes but the condition is the same, the first one in the address is always the new address and the last one is always the old address together with the phone no., in the text file I already aligned the phone number with its proper address.

Hope you can help me again.

Thanks..