6 Replies Latest reply on Jan 13, 2016 11:59 AM by siplus

    Extracting a client code from the description column of csv Bank download

    Raymond

      I am trying to extract a client code from the description column of a csv file downloaded from my bank. The description will be entered by the clients and unfortunately will not always be consistent in its position and sometimes the client code will be missing.

       

      The purpose of identifying the client code is to help with matching the bank payments with a invoices in a FM db.

       

      The maximum length of the description is 60 characters.

      Samples 2 descriptions

      INTERNET TRANSFER   LUNDI BLA001 104347  Client name

      ref R1H002          rick design Co

      BLA001 = Client code always 3 alpha & 3 numbersExtracting a client code

        • 1. Re: Extracting a client code from the description column of csv Bank download
          siplus

          Taking a look at at least 2 consecutive payments in the bank file would be helpful imho.

           

          Matching the payment with the invoice should be done on more criteria, anyway, one of which is of course the invoice amount.

          • 2. Re: Extracting a client code from the description column of csv Bank download
            Raymond

            Siplus,

             

            I need to be able to extract the client code which is unique and deposits would be made once or twice a year.  The amount are very similar example $163.00 1,550 plus number.

             

             

            Once I can extract the code then I can match the payments, except if the case of an under payment, which will be handled.

             

             

            Thank for your response.

             

            Raymond

            • 3. Re: Extracting a client code from the description column of csv Bank download
              siplus

              If the file is CSV then the code will be in a consistent position, even if left blank. For every line, it will be between comma n and comma n+1.  By replacing commas with returns you will transform the line into a list, then you can get the code with GetValue().

              • 4. Re: Extracting a client code from the description column of csv Bank download
                beverly

                My thoughts as well, si! however OP says it may be that the single 'Description' column is where this code appears with other text and is client-defined, thus inconsistent.

                 

                I'd say it's time to have the bank provide a unique column, or have a human (possible errors!) go through this field and find the  value.

                 

                beverly

                • 5. Re: Extracting a client code from the description column of csv Bank download
                  greatgrey

                  Raymond

                      This assumes that you are used FM7 or newer.

                      That all letters of the alphabet are used regardless of their case.

                      That the left 3 characters are only alpha and the right 3 characters are numeric.

                      As long as the Client ID is separated by characters the “word” functions recognize as the end and start of a word it is not hard to pick it out. However in the attached script I show two ways to test for your Client ID one is word by word and the other steps though character by character and there are only two lines that need to be changed to switch between them. The character lines are the Disabled “//” ones in the script. Also the character by character will find the Client Id info even if the client leaves out a space which will cause the word by word to fail.

                  Of course you could run the word by word search first and if it fails to find it then run the character by character search. The character search takes longer but won’t miss if it’s there but, both will fail if there happen to be the same string pattern before the real ID.

                      Then there is the possibly that only certain letters are used in one of the 3 positions, i.e.  The first character may be only an A, B, G, W or Z. Giving you a filter string of $$AlphaStringLeft = “ABGWZ” add lowercase to it if needed.  Then you need to change the filtering test.

                  To

                  6 =

                  (Length ( Filter ( Left ($TestString ; 1 ) ; $$AlphaStringLeft ))  /* test first 1 character is alpha and in “ABGWZ” */

                  + Length ( Filter ( Middle ($TestString ; 2 ; 2 ) ; $$AlphaString )) /* test second and third characters are alpha  */

                  +

                  Length ( Filter ( Right ($TestString ; 3 ) ; $$NumericString ))) /* test last 3 chars are numeric */

                   

                  etc. or some variation.

                   

                  /* All the Letters, but if any letters are never used in the Client ID remove them from this string*/

                  $$AlphaString ="ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefgjijklmnopqrstuvwxyz"

                  $$NumericString = "0123456789"

                  • 6. Re: Extracting a client code from the description column of csv Bank download
                    siplus

                    I don't understand how and why a bank can supply such a disaster, to start with.

                     

                    In Switzerland all banks supply a .v11 file (which is a simple text file) and everybody knows how to decode it.

                     

                    Change bank !