Hi guys. I'm working on an app in which the user copy and pastes from a website into a richtextbox then the program uses the phone number, name and "match by string" from the richtexbox. What is copied is like an Excel table type thing but all the data is in Column "A". The data isn't tab deliminated. If i split on spaces then that will give incorrect results since some of the pieces of data have spaces in them. It looks like I'm about to do some really nasty ugly coding to get to the values I need. Can you guys super please take a look at this snippet of data and tell me of a graceful way of getting the name, phone number and that last value in the line if such coding exists? This is the info I would need to pickup on in the first row of data. SHEKITA LLOYD, (xxx) 956-80, and Non-Payment of premium.
This is a sample of the data:
Producer Code Policy # Insured Name Insured Phone Policy Status
08xx57 350xxx1xxx Cancelled Non-Reinstateable 01/09/2012 Non-Payment of premium;
08xx57 350xxx1xxx SHEKITA LLOYD (xxx) 956-80 Cancelled Non-Reinstateable 11/18/2011 Non-Payment of premium;
08xx57 350xxx2xxx DAVID MORALES (xxx) 993-53 Cancelled Non-Reinstateable 12/03/2011 Non-Payment of premium;
08xx57 350350xxx8 LORRAINE JOHNSON (xxx) 403-86 Cancelled Non-Reinstateable 11/28/2011 Non-Payment of premium;
08xx57 3503505xxx CHRYSTAL YOUNG (xxx) 508-31 Cancelled Non-Reinstateable 02/05/2012 Non-Payment of premium;
08xx57 0704000xxx LARRY MORALES JR (xxx) 239-80 Cancelled Non-Reinstateable 02/29/2012 Substantial Increase in Hazard
08xx57 3503503xxx ASHANTI WILLIAMS (xxx) 634-68 Cancelled-Reinstateable 02/06/2012 03/07/2012 $6.86 Non-Payment of premium;
08xx57 5503007xxx Sergio J Brito xxx-844-8358 Cancelled-Reinstateable 02/09/2012 03/10/2012 $196.68 Non-payment of premium
08xx57 0703702xxx Alex Lopez 323-xxx-1871 Cancelled-Reinstateable 02/10/2012 03/11/2012 $108.00 Non-payment of premium
08xx57 3503501xxx SUN HONG (310) xxx-75 Cancelled-Reinstateable 02/11/2012 03/12/2012 $43.28 Non-Payment of premium;
08xx57 0703600xxx Rogelio Patino 310-xxx-0090 Cancelled-Reinstateable 02/18/2012 03/19/2012 $188.60 Non-payment of premium
08xx57 070370xxxx Jaqwun L Turner 323-xxx-0121 Cancelled-Reinstateable 02/18/2012 03/19/2012 $57.40 Non-payment of premium
Left$(), Right$() and Mid$() are your friends. Might be slow, but once you figure out the coordinates to use for each field, set them up and parse each record.
Code:
a1 = Left$(v1,6)
a2 = Mid$(v2,4,12)
Opps. Wrong forum!
You'll want to convert to string, then use .SUBSTRING(4,14)
OK, tough, but not so bad. I've had to do worse once.
Have a look at this :
Code:
Public Class Form1
Private Counter As Integer
Private InputLines() As String
Private LineCount As Integer
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'Process
Dim NameArray() As String
Dim NumberArray() As String
Dim MessageArray() As String
Dim SplitDateArray() As String
NameArray = InputLines(Counter).Substring(18).Split(" ") 'NAME
TextBox1.Text = NameArray(0) & " " & NameArray(1)
NumberArray = InputLines(Counter).Substring(18).Split("(")
TextBox2.Text = "(" & NumberArray(1).Substring(0, 12) 'NUMBER
MessageArray = InputLines(Counter).Split("Non") 'MESSAGE
If MessageArray.Length = 2 Then
SplitDateArray = MessageArray(1).Split("/")
TextBox3.Text = SplitDateArray(2).Substring(5)
ElseIf MessageArray.Length = 3 Then
TextBox3.Text = "N" & MessageArray(2)
End If
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Counter = 2
Dim i As Integer
LineCount = RichTextBox1.Lines.Count
ReDim InputLines(LineCount - 1)
For i = 0 To LineCount - 1
InputLines(i) = RichTextBox1.Lines(i).ToString()
Next
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
If Counter < LineCount Then
Counter += 1
Button1.PerformClick()
Else
Counter = 2
End If
End Sub
End Class
I basically split everything then use Substring to display the correct info. I am attachin a small sample with. It is 99% perfect. I have included a button on there to show the Next entry, you could click on that to navigate through all records. Unfortunately, with a record or 2, it bombs out because the charcater is not at the right place - it can easily be fixed using the very same logic that I have used.
I have done most of the Tough, ugly, horrible, messy, agonising, mind-bending, make-me-think work for you, and I think my code is easy enough to understand. I really hope it helps you
Just as a side note. It would do you a wealth of good by looking into Regular Expressions. If I had used it, the above code wouldn't have looked so messy, and a bit more streamlined. I didn't use it here as I am pressed for time here at work
Hey Hannes thanks so much. I've glance the code over and downloaded the zip. Now I have to look at it carefully and get it to work in the program. I'll definilty take a look a regular expressions. I'll let you know how it goes. I really appreicate your help and hope to be able to return the favor.
* The Best Reasons to Target Windows 8
Learn some of the best reasons why you should seriously consider bringing your Android mobile development expertise to bear on the Windows 8 platform.