Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Parseing names in a pipe delimited file

Posted on 2008-10-21
6
Medium Priority
?
383 Views
Last Modified: 2008-11-18
I'm in the process of trying to build a text file convertor for my clinet. For lack of a bette description. Tehy are getting 4-6 text files from 1 venodr and need to convert that to 3 text fles to send to another vendor. All of that is taken care of, did the easy part. This is the hard part. The incoming text files are pipe delimited like in the code block. The name is combined as last, first middle inital (if there is one) I need to split the name up into 3 "fields" last|first|middle intiial.

I also need to split the address field (1234 N ridgeway) to street num|street direction|streetname

and to put it frankly, I'm at my wits end. any help would be greatly appreciated. Oh and some times the name could come in as: DE LA FONT, BILLY BOB

Just want you guys to earn these points ;)

13406206|""|"SMITH, BOB"|"IL"|"W"|604|180|"BLU"|"BRO"|"N"|" "|"20081009"|"UNEMPLOYED"|"20081008"|"MOT"|"3000N"|"1222"|"TYP2"|"ARR"|"A"|"017384108"|"IL0164A00"|"M"|"08-30516"|"FAR"|"L52016085079"|"  "|"016"|""|"N"|""|""|""|"  "|""|"        "|"218"|"1234 N RIDGEWAY"|"SKOKIE"|"IL"|"60076"|"LS10160017384108"||""|""|""

Open in new window

0
Comment
Question by:gnixon14
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
6 Comments
 
LVL 25

Expert Comment

by:SStory
ID: 22770585
For the address

dim parts() as string=split(address," ")

dim StreetNumber as string=parts(0)
dim Address as string=""
if parts.count>1 then
  for b as byte=1 to parts.length-1
      if b<>1 then
          address &=" "
      end if
      Address & = parts(b)
  next
0
 
LVL 25

Accepted Solution

by:
SStory earned 1500 total points
ID: 22770646
The above should say Address.Split(" ")

Of course you'd take the whole input line by reading it into a string
say strInput

Split the fields
dim Fields() as string=strInput.Split("|")

'get the correct field position and split it
dim strNameParts() as string=Fields(2).Split(",")

'get last name
dim strLastName as string=strNameParts(0).Trim

dim OtherParts() as string=strNameParts(1).Split(" ")

'get first name
dim strFirstName as string=OtherParts(0)

if OtherParts.length>1 then
dim strMidName as string=OtherParts(1)

Of course there could be , JR. etc.  If so you are going to have to write a more sophisticated parser to
handle any cases you can come up with, but basically you know there is a comma between last and first, so split by the comma and you've got last name.  There should be a space after the first name so you can get that.  The rest if there is any can be handle by examination using string manipulation methods.


HTH,

Shane

0
 
LVL 16

Expert Comment

by:HooKooDooKu
ID: 22771773
Here's some old VB6 code that might help.  
You pass the data, a sub string position, and a single character delimiter, and a flag if data between quotation marks should be ignored (that way, delimeters inside quoation marks are ignored).  The functions return the desired sub string (where sub string 1 is the string that appears BEFORE the 1st delimeter).

If your data is in a string variable by the name of DataString, here's how the code might look:
Addrstring = TrimAndRemoveQuotes( Nth$( DataString, 3, "|" ) )
FirstString = Nth$( AddrString, 1, "," )
LastString = Nth$( AddrString, 2, "," )

Public Const QUOTATION_MARK As String = """"
 
 
Public Function TrimAndRemoveQuotes(ByVal TextLine As String) As String
    TrimAndRemoveQuotes = Trim$(TextLine)
    If Left$(TrimAndRemoveQuotes, 1) = QUOTATION_MARK And _
       Right$(TrimAndRemoveQuotes, 1) = QUOTATION_MARK Or _
       Left$(TrimAndRemoveQuotes, 1) = "'" And _
       Right$(TrimAndRemoveQuotes, 1) = "'" Then
        If Len(TrimAndRemoveQuotes) > 1 Then
            TrimAndRemoveQuotes = Mid$(TrimAndRemoveQuotes, 2, Len(TrimAndRemoveQuotes) - 2)
        End If
    End If
End Function
 
 
Public Function NullBetweenQuotes(Str As String) As String
Dim StartPos As Long    
Dim EndPos   As Long    
Dim LenStr   As Long    
Dim LenStartEnd As Long 
 
    'Initialize
    LenStr = Len(Str)
    NullBetweenQuotes = Str
 
    'Find First Open Quote
    StartPos = InStr(NullBetweenQuotes, QUOTATION_MARK)
    
    Do While StartPos
    
        'Find Matching Close Quote
        EndPos = InStr(StartPos + 1, NullBetweenQuotes, QUOTATION_MARK)
        
        'If None Found - Pretend there is an extra Quotation Mark at end of string
        If EndPos = 0 Then
            EndPos = LenStr + 1
        End If
        
        'Fill Between Quoates with NULLs
        If StartPos < LenStr Then
            LenStartEnd = EndPos - StartPos - 1
            Mid$(NullBetweenQuotes, StartPos + 1, LenStartEnd) = String$(LenStartEnd, vbNullChar)
        End If
        
        'Find Next Open Quote
        StartPos = InStr(EndPos + 1, NullBetweenQuotes, QUOTATION_MARK)
    Loop
    
End Function
 
 
Public Function Nth$(Str As String, N As Integer, Delimiter As String, Optional DealWithQuotes As Boolean = False)
Dim StartPos As Long        
Dim EndPos   As Long        
Dim SearchString As String  
Dim I As Long               
 
    Debug.Assert Len(Delimiter) = 1
    
    'Create SearchString (make sure it Ends with a Delimiter so that we are guarenteed to find at least one
    If DealWithQuotes Then
        SearchString = NullBetweenQuotes(Str, DealWithEscapes, EscapeChar) & Delimiter   'NullBetweenQuotes Calls NullEscapeCodes
    Else
        SearchString = Str & Delimiter
    End If
    Debug.Assert Right$(SearchString, 1) = Delimiter
    
    'Find the Nth Delimiter (it Ends the Nth$)
    EndPos = 0
    For I = 1 To N
    
        'Remeber the Last Delimter + 1
        StartPos = EndPos + 1
        
        'Find the Next Delimeter
        EndPos = InStr(EndPos + 1, SearchString, Delimiter)
        
        If EndPos = 0 Then
            EndPos = Len(SearchString) + 1
            StartPos = EndPos
            Exit For
        End If
        
    Next I
    
    'Extract the Nth$
    If EndPos = 0 Then
        Nth$ = ""
    Else
        Nth$ = Mid$(Str, StartPos, EndPos - StartPos)
    End If
    
End Function

Open in new window

0
 
LVL 25

Expert Comment

by:SStory
ID: 22984317
I think my solution should have gotten at least partial credit. Since it appears abandoned there is no way that we will know what they asker wanted, but from what he said I've at least shown him how to split into pieces by the pipe and how to go about dealing with the name issues to some degree.
0
 

Author Comment

by:gnixon14
ID: 22984777
Got busy with work, forgot about this posting. Opps.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question