extracting information from a text using vba

[absan]

i have text file i need to take the address, account number (start with 200-xxxx-xxxx) and name from it and insert it into excel file.
that why im having problem with, looks like this i need to extract the bold information

""1615 MISSISSAUGA ROAD, ONW" 500-4 LT A/A 0 1 1 15 0 N Y 00000000 200-0000-0001 customer one
Attn Codes 0
HDW 409 DSR2 2.14459E+15 2-way SRVC: 154(154)
"L-191 COPELAND RIVER LAKE RD, O" NW 500-4 LT A/A 0 1 1 15 0 N Y 333-9494 200-0000-0002customer two
Attn Codes 37 0
HDW 435 NAV3 6.24472E+14 2-way SRVC: 154(154)
"86 WHITE LAKE DRIVE AD, ONW" 500-2 LT C/C 0 1 0 15 0 N N 00000000 200-0000-0003 customer three
Attn Codes 30.85
"2244 HWY, ONW" 500-2 LT C/C 0 1 0 1 0 N N 00000000 200-0000-0004 customer four "

please advice thanks

 

[EdNerd]

Welcome to Mr Excel. I hope you get an answer you can use.

First, I do hope none of the data you posted is real and belongs to someone. I'd hate for any of *my* info to be thrown around as "sample" stuff.

Next, it looks like you might be able to take your text string and:
-- get the first part by finding "500-", and using that character position to grab the address
-- get the last part by using InStrRev to find the first (last, actually) number, and use that character position to get the name
-- go backwards from the character position above to find the second "-", then backwards again to get the first space; in between the space and the character position is the account number

It would also be helpful to us to know how you are getting your text file, and how proficient are you with Excel and VBA?

 

[BiocideJ]

Ed, it clearly looks generic to me.. unless someone's name is actually customer one and it's not like addresses are not public information. I digress.
I had a similar concept as EdNerd suggested and submit this example to test to see if it works for you.

Public Type CustomerData
    Addr As String
    Acct As String
    Name As String
End Type
Sub ExtractTEST()
    Dim xCust As CustomerData
    xCust = ExtractData("""L-191 COPELAND RIVER LAKE RD, O"" NW 500-4 LT A/A 0 1 1 15 0 N Y 333-9494 200-0000-0002customer two")
    
    Debug.Print "The Customer's Acct: " & xCust.Acct
    Debug.Print "The Customer's Name: " & xCust.Name
    Debug.Print "The Customer's Addr: " & xCust.Addr
    
End Sub
Function ExtractData(dataStr As String) As CustomerData
    Dim ltr As Long
    Dim thisCustomer As CustomerData
    
    thisCustomer.Addr = Mid(dataStr, InStr(dataStr, Chr(34)), InStrRev(dataStr, Chr(34)) - InStr(dataStr, Chr(34)) + 1)
    thisCustomer.Acct = Mid(dataStr, InStrRev(dataStr, "200-"), Len(dataStr))
    For ltr = 1 To Len(thisCustomer.Acct)
        If Not Mid(thisCustomer.Acct, ltr, 1) Like "[0-9-]" Then
            thisCustomer.Name = Trim(Mid(thisCustomer.Acct, ltr, Len(thisCustomer.Acct)))
            thisCustomer.Acct = Left(thisCustomer.Acct, ltr - 1)
            Exit For
        End If
    Next ltr
    ExtractData = thisCustomer
End Function

 

[absan]

Welcome to Mr Excel. I hope you get an answer you can use.

First, I do hope none of the data you posted is real and belongs to someone. I'd hate for any of *my* info to be thrown around as "sample" stuff.

Next, it looks like you might be able to take your text string and:
-- get the first part by finding "500-", and using that character position to grab the address
-- get the last part by using InStrRev to find the first (last, actually) number, and use that character position to get the name
-- go backwards from the character position above to find the second "-", then backwards again to get the first space; in between the space and the character position is the account number

It would also be helpful to us to know how you are getting your text file, and how proficient are you with Excel and VBA?

Hi Ed,

you have my word that all the info here is not real but the formation is close to how the file look.

i would say I'm about 7/10 with excel but about 2/10 with VBA and i wont lie but i didnt understand the solution you suggested

 

[absan]

Ed, it clearly looks generic to me.. unless someone's name is actually customer one and it's not like addresses are not public information. I digress.
I had a similar concept as EdNerd suggested and submit this example to test to see if it works for you.

Public Type CustomerData
    Addr As String
    Acct As String
    Name As String
End Type
Sub ExtractTEST()
    Dim xCust As CustomerData
    xCust = ExtractData("""L-191 COPELAND RIVER LAKE RD, O"" NW 500-4 LT A/A 0 1 1 15 0 N Y 333-9494 200-0000-0002customer two")
    
    Debug.Print "The Customer's Acct: " & xCust.Acct
    Debug.Print "The Customer's Name: " & xCust.Name
    Debug.Print "The Customer's Addr: " & xCust.Addr
    
End Sub
Function ExtractData(dataStr As String) As CustomerData
    Dim ltr As Long
    Dim thisCustomer As CustomerData
    
    thisCustomer.Addr = Mid(dataStr, InStr(dataStr, Chr(34)), InStrRev(dataStr, Chr(34)) - InStr(dataStr, Chr(34)) + 1)
    thisCustomer.Acct = Mid(dataStr, InStrRev(dataStr, "200-"), Len(dataStr))
    For ltr = 1 To Len(thisCustomer.Acct)
        If Not Mid(thisCustomer.Acct, ltr, 1) Like "[0-9-]" Then
            thisCustomer.Name = Trim(Mid(thisCustomer.Acct, ltr, Len(thisCustomer.Acct)))
            thisCustomer.Acct = Left(thisCustomer.Acct, ltr - 1)
            Exit For
        End If
    Next ltr
    ExtractData = thisCustomer
End Function

Hi BiocideJ ,

shouldn't i have something to refer to the text file location? what i have there is just small sample of it? as i said I'm very bad with VBA and doing this manually takes forever so people told about me to VBA.

i tried something like this but no luck

Private Sub CommandButton1_Click()


Dim myFile As String, text As String, textline As String, Acct As Integer, Address As String
myFile = "C:\test\geographical-coordinates.txt"
'myFile = Application.GetOpenFilename()


Open myFile For Input As #1
Do Until EOF(1)
Line Input #1, textline
text = text & textline
Loop
Close #1

Acct = InStr(text, "Acc")
Address = InStr(text, "Adds")



End Sub

 

[BiocideJ]

You are correct in assuming there needs to be more to my VBA code.
I was simply supplying the information to parse out the Customer Data given a string.
I believe I can combine your code portion with my function and you should have a nearly complete solution.

The only part that is missing is what to do with the customer data once you have it.
I am simply loading it into variables and since no other information is currently known, they simply get overwritten by the next loop.
If this gets you close enough to complete, great, otherwise, more information is needed as to what you expect to happen to the customer data. (e.g. Written to another text file, stored in Excel cells, etc.)

Also, from your example it looks like some lines do not have all of the necessary information so I am excluding those for instances where the TextLine does not contain a valid Name, Acct# and Address.

Place the following code in a Standard Module (not a sheet or workbook module)
NOTE: You will need to insert this module likely.

Public Type CustomerData
    Addr As String
    Acct As String
    Name As String
End Type

Function ExtractData(dataStr As String) As CustomerData
    Dim ltr As Long
    Dim thisCustomer As CustomerData
    
    thisCustomer.Addr = Mid(dataStr, InStr(dataStr, Chr(34)), InStrRev(dataStr, Chr(34)) - InStr(dataStr, Chr(34)) + 1)
    thisCustomer.Acct = Mid(dataStr, InStrRev(dataStr, "200-"), Len(dataStr))
    For ltr = 1 To Len(thisCustomer.Acct)
        If Not Mid(thisCustomer.Acct, ltr, 1) Like "[0-9-]" Then
            thisCustomer.Name = Trim(Mid(thisCustomer.Acct, ltr, Len(thisCustomer.Acct)))
            thisCustomer.Acct = Left(thisCustomer.Acct, ltr - 1)
            Exit For
        End If
    Next ltr
    ExtractData = thisCustomer
End Function

In the sheet that this already exists, simply modify with my additional code

Private Sub CommandButton1_Click()

Dim xCust as CustomerData
Dim myFile As String, text As String, textline As String, Acct As Integer, Address As String
myFile = "C:\test\geographical-coordinates.txt"
'myFile = Application.GetOpenFilename()


Open myFile For Input As #1
Do Until EOF(1)
     Line Input #1, textline

     xCust = ExtractData(textline)
     If xCust.Addr <> "" And xCust.Name <> "" And xCust.Acct <> "" Then
          'this textline has all of a customer's data and can be used

          'Some other code to DO something with the Customer Data
     End If

Loop
Close #1



End Sub

 

[EdNerd]

you have my word that all the info here is not real

Thank you!! Just wanted to throw that out -- many people don't think about that.

How are you getting the text file? Is it actually a .TXT file?

The first thing the VBA would have to do is open the text file and read the information. Is this one file with everyone's data in it? Is one person's data all contained on one single and separate line?

The operations I suggested are basic VB string manipulations.
"Left" gets the left end of a string for as many characters as you specify.
Same with "Right", but at the other end.
"Mid" starts at a specific character position and gets the specified number of characters.
"InStrRev" starts from the end of a string and searches backwards for a specified character.
"InStr" does the same thig, but starts from the front and searches forward.

Once you have your text in a String variable, you can use these methods and operations to extract any little bits you need - as long as you can identify the start and end points, that is. If there's nothing definite and repeatable that you can hand a hat on, then you're kind of stuck.

In what you've provided:

""1615 MISSISSAUGA ROAD, ONW" 500-4 LT A/A 0 1 1 15 0 N Y 00000000 200-0000-0001 customer one
Attn Codes 0
HDW 409 DSR2 2.14459E+15 2-way SRVC: 154(154)
"L-191 COPELAND RIVER LAKE RD, O" NW 500-4 LT A/A 0 1 1 15 0 N Y 333-9494 200-0000-0002customer two
Attn Codes 37 0
HDW 435 NAV3 6.24472E+14 2-way SRVC: 154(154)
"86 WHITE LAKE DRIVE AD, ONW" 500-2 LT C/C 0 1 0 15 0 N N 00000000 200-0000-0003 customer three
Attn Codes 30.85
"2244 HWY, ONW" 500-2 LT C/C 0 1 0 1 0 N N 00000000 200-0000-0004 customer four "

I would ask:
-- how do these break up into lines?
-- is the customer name always in a certain format? (LastName, FirstName? or FirstName LastName? any initials?)

 

[absan]

Thank you!! Just wanted to throw that out -- many people don't think about that.

How are you getting the text file? Is it actually a .TXT file?

I would ask:
-- how do these break up into lines?
-- is the customer name always in a certain format? (LastName, FirstName? or FirstName LastName? any initials?)

we have program called CBS which is DOS program and yes it is .TXT the problem im having if the information are all over the file. but in one line you can find the address which is what between the quotation marks, and the account number is the number start with 200-xxxx-xxxx, and the name is after the account number (firstname lastname)


is there a way to extract what in said the quotation marks and put it in cell A1 then the next one and B2 ...etc?

again thank for the help.

 

[absan]

You are correct in assuming there needs to be more to my VBA code.
I was simply supplying the information to parse out the Customer Data given a string.
I believe I can combine your code portion with my function and you should have a nearly complete solution.

The only part that is missing is what to do with the customer data once you have it.
I am simply loading it into variables and since no other information is currently known, they simply get overwritten by the next loop.
If this gets you close enough to complete, great, otherwise, more information is needed as to what you expect to happen to the customer data. (e.g. Written to another text file, stored in Excel cells, etc.)

Also, from your example it looks like some lines do not have all of the necessary information so I am excluding those for instances where the TextLine does not contain a valid Name, Acct# and Address.

thanks for your help
Place the following code in a Standard Module (not a sheet or workbook module)
NOTE: You will need to insert this module likely.

im trying to take the data and insert it on excel file 3 columns.
for example extract what in said the quotation marks (the address) and put it in cell A1 then the next one and A2 ...etc?
and account number start with 200-xxxx-xxxx and place in in B1, B2 ..etc
and same with name in C1,C2,...etc

 

[EdNerd]

Here's what I've got, absan. Copy everything from Sub to End Sub and paste it into your module. Then in your _Click code or other sub, use Call GetCustInfo.

Sub GetCustInfo()
Dim wkb As Workbook
Dim wks As Worksheet
Dim regEx As New RegExp
Dim Match, matches, s
Dim cntRow As Long, posChar As Long
Dim strFile As String, strText As String
Dim strCust As String, strAdd As String, strAcct As String
Dim strRegEx As String
'Set workbook, worksheet objects
Set wkb = ActiveWorkbook
Set wks = ActiveSheet
'Initiate row count at 2 (assuming headers in row 1)
cntRow = 2
'Assume all account numbers are formatted
'123-1234-1234
'Uses Regular Expressions for pattern matching
'Set a reference under Tools >> References to
'Microsoft VBScript Regular Expressions 5.5
strRegEx = "([0-9]{3})(\-)([0-9]{4})(\-)([0-9]{4})"
'Set up RegExp for match
With regEx
  .Global = True
  .MultiLine = True
  .IgnoreCase = False
  .Pattern = strRegEx
End With
'Navigate to and open data file
strFile = Application.GetOpenFilename()
'Open file and read each line
Open strFile For Input As #1
Do Until EOF(1)
  Line Input #1, strText  'strText is one line of data
  'Print line in Immediate window (CTRL+G)
  Debug.Print strText
  
  'Check text for validity
  If regEx.test(strText) = True Then
    'RegExp matched the pattern; data is valid
    'Get the match
    Set matches = regEx.Execute(strText)
    For Each Match In matches
      'Gets character position of regEx match
      s = Match.FirstIndex
    Next Match
    
    'Extract account number; use the "s" variable
    strAcct = Mid(strText, CLng(s), 13)
    'Clean of leading, ending spaces
    strAcct = Trim(strAcct)
    Debug.Print strAcct
    
    'Extract name; use the "s" variable + 13 characters
    strCust = Right(strText, Len(strText) - (CLng(s) + 13))
    'Clean of leading, ending spaces
    strCust = Trim(strCust)
    Debug.Print strCust
    
    'Extract address; find " 500-" after address
    posChar = InStr(1, strText, " 500-")
    strAdd = Left(strText, posChar)
    'Clean out quotes
    strAdd = Replace(strAdd, Chr(34), "")
    'Clean of leading, ending spaces
    strAdd = Trim(strAdd)
    Debug.Print strAdd
    
    'Write to worksheet
    'Name to col A
    wks.Cells(cntRow, 1) = strCust
    'Account to col B
    wks.Cells(cntRow, 2) = strAcct
    'Address to col C
    wks.Cells(cntRow, 3) = strAdd
    
    'Increment row
    cntRow = cntRow + 1
    
  End If
  
Loop
Close #1
'Clear objects
Set regEx = Nothing
Set wks = Nothing
Set wkb = Nothing
MsgBox "Done!"
End Sub