9 Replies Latest reply: May 15, 2014 10:16 AM by Olly Bond RSS

    Parse Multiple Record Types in Bank Data Dump

      Can multiple detail templates be defined in one model?

      Information format varies within report by type i.e.:

      Checks

      ACH Payments

      Debitcard Transactions

      etc.

       

      or

       

      Is a separate model required for each type?  If so, how is the report cobled together in the end?

        • Parse Multiple Record Types in Bank Data Dump
          Olly Bond

          Hello chipshot,

           

          It depends - on the layout of the report, and how the elements relate to each other if at all, and how "trappable" the records are. You might be able to use one model, but if you have DataPump/Automator then you can easily execute several models against the report in sequence.

           

          How best to cobble the data together afterwards depends on how you need to use it. Are you trying to get a picture of one customer that includes all the relevant transaction types? How to you deliver the data to users?

           

          We'd be happy to help - if you can send us an anonymised sample by email we could advise on the trapping options for a start. You can anonymise the data using the tool at blog.greenbar.info.

           

          Best wishes,

           

          Olly

            • Parse Multiple Record Types in Bank Data Dump

              Thanks Olly

               

              Looking to create one file that contains all transactions.  Columns will be:  Date; Amount; Type of Transaction; Ref; Description

               

              Sample data is (Note that the columns do line up in the actual file, the cut and paste produced an unexpected result):

               

                          1.    -1000    4116CHECK # 4116                                         

                          2.   -19.07        POS PURCHASE - ABC FINE 1239 SOUTH W ANYWHERE FL     

                          3.   -17.27        POS PURCHASE - CVS 03668 03668 5 Gar ANYWHERE FL     

                          4.     -184        CHECK CRD PURCHASE 12/15 REALTOR ASSOCIATIO          

                          5.   -17.04        CHECK CRD PURCHASE 12/15 HILTON PARKING BOO ANYWHERE

              FL                

                          1.   -12.75        CHECK CRD PURCHASE 12/15 ORANGE COUNTY C C ANYWHERE

              FL                 

                          1.   -17.71        POS PURCHASE - JB ENTERPRISES OF FL ANYWHERE FL      

                          2.   -22.24        POS PURCHASE - CVS ANYWHERE FL                       

                          3.   -37.47        POS PURCHASE - Staples Inc                           

                          4.      -47        CHECK CRD PURCHASE 12/15 CHOPS RESTAURANT ANYWHERE FL

                          5.       -9        CHECK CRD PURCHASE 12/15 OCBCC CONV CTR PAR ANYWHERE

              FL                

                          1.   -11.83        POS PURCHASE - PUBLIX SUPER MAR 1923 ANYWHERE FL     

                          2.   -30.03        POS PURCHASE - SHELL Service Station ANYWHERE FL     

                          3.      -35        CHECK CRD PURCHASE 12/13 LACITA GOLF AND CO ANYWHERE

              FL                

                          1.    -8.23        CHECK CRD PURCHASE 12/13 BARNES & NOBLE #29

              ANYWHERE FL                

                          1.    -8.75        CHECK CRD PURCHASE 12/13 REGAL CINEMAS WATE ANYWHERE

              FL                

                          1.       -9        CHECK CRD PURCHASE 12/13 REGAL CINEMAS WATE ANYWHERE

              FL                

                          1.   -127.2        CHECK CRD PURCHASE 12/12 PRESTIGE SELF STOR ANYWHERE

              FL                

                          1. -332.23    4114CHECK # 4114                                         

                          2. -694.93        FPL DIRECT DEBIT ELEC PYMT 12/13 WEBI JOHNS CO INC DI

                          3. -461.39        STATE FARM RO XX XXXX XX X XXXXXXXXXX JIM SMITH[/code]

               

              (Edited to try to improve the format  - looks like there are still some wrap problems, maybe others.)

                • Parse Multiple Record Types in Bank Data Dump
                  Olly Bond

                  Hello Chipshot,

                   

                  Yes, you can do this in one model - I'm assuming the ####### represent the date? - but you'll need to specify in advance the different possible types of transaction. In your sample I can see:

                   

                  nnnnCHECK

                  CHECK CRD PURCHASE

                  POS PURCHASE

                  FPL DIRECT DEBIT ELEC PYMT

                   

                  but I'm not sure what to make of "STATE FARM RO" - is that a transaction type?

                   

                  It's an ugly report - inconsistent decimals for the amounts, the nnnn before the CHECK repeating the reference, mixed upper and proper case... Where does it come from?

                   

                  Best wishes,

                   

                  Olly

                    • Parse Multiple Record Types in Bank Data Dump

                      Thank you for your reply.

                      Never mind the date and the amount, I know how to handle them.  The issue is with the detail.  Below are the types of records and a new view of the detail.

                      How do I parse these out in one run?

                      Side question, how could I parse out the city and state only?

                       

                      TYPES:

                      CHECK

                      POS PURCHASE

                      CHECK CRD PURCHASE

                      DIRECT DEBIT

                       

                       

                      DETAIL:

                      CHECK # 4116

                      POS PURCHASE - ABC FINE 1239 SOUTH W ANYWHERE FL

                      POS PURCHASE - CVS 03668 03668 5 Gar ANYWHERE FL

                      CHECK CRD PURCHASE 12/15 REALTOR ASSOCIATIO

                      CHECK CRD PURCHASE 12/15 HILTON PARKING BOO ANYWHERE FL

                      CHECK CRD PURCHASE 12/15 ORANGE COUNTY C C ANYWHERE FL

                      POS PURCHASE - JB ENTERPRISES OF FL ANYWHERE FL

                      POS PURCHASE - CVS ANYWHERE FL

                      POS PURCHASE - Staples Inc

                      CHECK CRD PURCHASE 12/15 CHOPS RESTAURANT ANYWHERE FL

                      CHECK CRD PURCHASE 12/15 OCBCC CONV CTR PAR ANYWHERE FL

                      POS PURCHASE - PUBLIX SUPER MAR 1923 ANYWHERE FL

                      POS PURCHASE - SHELL Service Station ANYWHERE FL

                      CHECK CRD PURCHASE 12/13 LACITA GOLF AND CO ANYWHERE FL

                      CHECK CRD PURCHASE 12/13 BARNES & NOBLE #29 ANYWHERE FL

                      CHECK CRD PURCHASE 12/13 REGAL CINEMAS WATE ANYWHERE FL

                      CHECK CRD PURCHASE 12/13 REGAL CINEMAS WATE ANYWHERE FL

                      CHECK CRD PURCHASE 12/12 PRESTIGE SELF STOR ANYWHERE FL

                      CHECK # 4114

                      FPL DIRECT DEBIT ELEC PYMT 12/13 WEBI JOHNS CO INC DI

                       

                      Thank you

                        • Parse Multiple Record Types in Bank Data Dump

                          Source of data the download of transactions from a bank, csv format was used.

                            • Parse Multiple Record Types in Bank Data Dump
                              Olly Bond

                              Hello chipshot,

                               

                              It's manageable, using if(), along with lsplit, rsplit, and extract. Basically, you need to define a calculated field that checks for #,-,/ and splits accordingly. The city, state is harder, as some towns in Florida have two words in their name, so without punctuation to help it's going to be tricky to split the merchant name from the town.

                               

                              How automated, and reliable, do you need this to be?

                               

                              Best wishes,

                               

                              Olly

                                • Parse Multiple Record Types in Bank Data Dump

                                  automation although nice is not required.  It needs to be very reliable.

                                   

                                  As to the City and State, Is there a string formula that will return the col number based on a string search starting from the right? i.e.

                                   

                                  show me the starting number for the city field based on the second occurence of a "space" starting from the end of the string.

                                    • Parse Multiple Record Types in Bank Data Dump
                                      Grant Perkins

                                      Chipshot,

                                       

                                      If this is a csv input file are the columns with relevant detail well separated in the csv? For example is any address info in separate columns (your challenge suggests not) or at least in a single column/field?

                                       

                                      Have you looked at the Address Block feature? It's been a while since I have needed it so memory is a little rusty and I don't recall if I have ever used it to work with just part of an address but there may be some traction for it and it has a possibility to flag potential errors - which might be useful since address info, even abbreviated, cannot usually be guaranteed accurate. Being able to successfully identify which records are suspect can be an enormous time saver for corrective activity.

                                       

                                       

                                      Grant

                                       

                                      ETA: I have a feeling that successful and reliable use of the Address Block for US addresses may be a little reliant on identification of a ZIP code in the address data so it could be a marginal approach here, although State would be handy. Not too difficult to get that in other ways though.

                                      • Parse Multiple Record Types in Bank Data Dump
                                        Olly Bond

                                        Hello chipshot,

                                         

                                        rsplit(;10;" ";2)

                                         

                                        might help...

                                         

                                        Best wishes,

                                         

                                        Olly