8 Replies Latest reply: May 15, 2014 10:02 AM by Data Kruncher RSS

    Extracting Records froma continious string

    mmzd60 _

      Hello

      Is there a way for Monarch to extract records from a continious string(no carrage returns)? each record contains the same amount of characters.

       

      Thanks, Dave

        • Extracting Records froma continious string
          Data Kruncher

          Hi Dave,

           

          There may a few ways to deal with it, so long as the length of the input string doesn't exceed Monarch's input line length limit of 4,000 characters.

           

          It will be a little easier if there are field delimiters within each record, but you can still work with it pretty easily without delimiters if the record length is fixed - meaning that the width of each field within a record is fixed from record to record. This will make for a pretty straightforward slice and dice type of solution.

           

          What do you have?

            • Extracting Records froma continious string
              Grant Perkins

              Dave,

               

              If I read it right you have records with no separation between them?

               

              There are some Monarch Utilities that allow you to add some structure to such files before processing with Monarch. The progrma I think you need, for version 5, is PREP.EXE. (More recent version have this bundled into a single Utility.

               

              Prep was part of a Monarch installation at V5 and may still be available from the Datawatch web site - I have not checked since the reworking of the site earlier this year.

               

              V5 has some constraints on line length and record size but Prep should allow you to do something with your input since you know from the length of the records how you need to split the string.

               

              So first check that you can fins the program and we can take any further questions frojm there.

               

              On the other hand if Kruncher has the better interpretation of your needs Prep may still be in play.

               

              HTH.

               

               

              Grant

                • Extracting Records froma continious string
                  RalphB _

                  Dave,

                   

                  We have a report that comes off our mainframe that gets loaded into a third party program that looks just like what you describe.

                   

                  I was able to break it down into individual fields through Monarch so I could look over the data to see if it was correct.

                   

                  If you have the mapping document or know the start and stopping points of each field it isn't all that difficult.

                   

                  I had the mapping document which was a great help showing the starting point and length and data type of each field and all I did was paint each field I needed and set the type.  I have to admit it takes some time but it is doable.

                   

                  This is a sample of one report that I did this with:

                   

                  048A0000026  010119810327NIA      W00A2009032700000000MNMN   0000100000000000000000000000000000000000000000N01000000000000000000000000000002008080000100000000000644705000031240000000000000000000000000000               NELSON              EDWARD         WMM19230823A57N000000000000000000                                                                               

                  048A0000034  010419820209NIB      W00A2003020900000000MNMN   0000250000000000000000000000000000000000000000N01000000000000000000000000000002008080000250000000000000000000051025000000000000000000000000000               MC MORROW           CAROLE         HFF19370830A44N000000000000000000                                                                                /code

                    • Extracting Records froma continious string
                      mmzd60 _

                      A sample may look like this:

                       

                      5584392015Smith          John           M120919685432099678Jones          Henry          K061419025439670432Miller           Frank          H071320006543029543Yoder          William        H04151978

                       

                       

                      This should look Like this:

                      5584392015Smith          John           M12091968

                      5432099678Jones          Henry          K06141902

                      5439670432Miller           Frank          H07132000

                      6543029543Yoder          William        H04151978

                       

                      I have an estimated 6000 Records.

                       

                      I'll try that Prep.exe.

                       

                      As far as the starting and stopping points are concerned... intuitively I know where they are but I don't know how to tell the system to find them.

                       

                      thanks for your feedback and thanks in advance for future replys, Dave

                        • Extracting Records froma continious string
                          Data Kruncher

                          Dave,

                           

                          Given your ever-so-slightly varying field length (due to the name), I don't think that either of the Prep or Monarch Utility programs are going to give you the results you want.

                           

                          I see that each record, after a second space character, ends with a letter - probably middle initial and an eight number sequence. Then you're on to the next record.

                           

                          So I spent a few minutes and came up with the following VBA code that you can copy and paste into an Excel code module.

                           

                          Sub FixRecords()

                              Dim iEnd As Integer

                              Dim i As Integer

                              Dim iSpaceCount As Integer

                              Dim iCharsRemaining As Integer

                           

                              'A variable-length string can contain up to approximately 2 billion (2^31) characters

                              Dim sRecord As String

                              Dim sNewRecord As String

                              Dim sChar As String

                           

                              Dim bNearEnd As Boolean

                           

                              Open "C:\test\test.prn" For Input As #1

                              Input #1, sRecord

                           

                              iEnd = Len(sRecord)

                           

                              iSpaceCount = 0

                              bNearEnd = False

                              iCharsRemaining = 10

                           

                              Open "C:\test\test_output.prn" For Output As #2

                           

                              For i = 1 To iEnd

                                  sChar = Mid(sRecord, i, 1)

                           

                                  If bNearEnd Then

                                      iCharsRemaining = iCharsRemaining - 1

                                  End If

                           

                                  If sChar <> " " Then

                                      sNewRecord = sNewRecord & sChar

                                  Else

                                      If Not bNearEnd Then

                                          iSpaceCount = iSpaceCount + 1

                                          If iSpaceCount = 2 Then

                                              bNearEnd = True

                                          End If

                                      End If

                           

                                      sNewRecord = sNewRecord & sChar

                           

                                  End If

                           

                                  If iCharsRemaining = 1 Then

                                      Print #2, sNewRecord

                                      sNewRecord = ""

                                      iCharsRemaining = 10

                                      iSpaceCount = 0

                                      bNearEnd = False

                                  End If

                              Next i

                           

                              Close #2

                              Close #1

                           

                          End Sub[/code]

                           

                          You'll need to revise the file name and path for the input and output files to suit your needs, but this worked just fine for my test.

                           

                          It will create the file that you can use with Monarch.

                           

                          The only complication that I can dream up is if you don't have a letter before the last eight numbers. That'll throw things off nicely, but some more robust logic could probably be devised to account for that too. I don't have the time for that just now though. Thought I could share this with you as is; it might be enough for you.

                           

                          Hopefully this will help you proceed with your project.

                           

                          Kruncher

                          • Extracting Records froma continious string
                            Grant Perkins

                            Dave,

                             

                            In your first post above you mentioned at "each record contains the same amount of characters". Is that actually the case? Your proxy sample line suggest to me that it might not be so.

                             

                            If the records are all the same length then Prep should work fine. But if they are not then I could foresee some interesting challenges.

                             

                            The names are likely to be variable length I would think. Are they ALWAYS ONLY Family name and Given Name? ( I would be slightly surprised if they are ...)

                             

                            There are a few things you can do with the string but it might be worth seeing of you can get back to the source of the file to see if it can be presented in a different way that would make life easier, more consistent and more reliable for repeated use. Perhaps you have already investigated that option?

                             

                             

                             

                             

                            Grant

                             

                             

                            Edit: Hmm. Get called away in mid post and before you know it Kruncher has dropped in to share the same concerns and offer a solution.

                             

                             

                              • Extracting Records froma continious string
                                mmzd60 _

                                Sorry... the display did not display correctly.

                                 

                                the record lengths ARE the same for each record. when I posted the string on this page the white spaces truncated. I will substitute white sapce with carots.

                                 

                                5584392015Smith^^^^John^^^^M120919685432099678Jones^^^Henry^^^K061419025439670432Miller^^Frank^^^H071320006543029543Yoder^^^William^^H04151978

                                 

                                 

                                This should look Like this:

                                 

                                5584392015Smith^^^^John^^^^^M12091968

                                5432099678Jones^^^^Henry^^^^K06141902

                                5439670432Miller^^^Frank^^^^H07132000

                                6543029543Yoder^^^^William^^H04151978

                                  • Extracting Records froma continious string
                                    Data Kruncher

                                    Right then, getting back to the straightforward slice and dice. Too bad... the VBA was fun.

                                     

                                    Capture the whole string as a single character field, named "A".

                                     

                                    The first field - everything up to the last name -I assumed to be a single field:

                                    Left(A,10)[/code]

                                     

                                    If you need it to be numeric, then:

                                    Val(Left(A,10)[/SIZE])[/code]

                                     

                                    Last Name:

                                    [SIZE=2]Trim(Substr(A,11,9))[/SIZE][/code]

                                     

                                    First Name:

                                    [SIZE=2]Trim(Substr(A,20,10))[/SIZE][/code]

                                     

                                    Initial:

                                    [SIZE=2]Substr(A,31,1)[/SIZE][/code]

                                     

                                    And finally:

                                    [SIZE=2]Trim(Substr(A,32,8))[/SIZE][/code]

                                     

                                    or again, as a numeric field:

                                    Val(Substr(A,32,8))[/SIZE][/code]