1 2 Previous Next 18 Replies Latest reply on Apr 4, 2016 4:39 PM by CarlSchwarz

    Inserting characters into a field

    derickc

      Hi all!

       

      I have a problem where the Excel file from where I am importing from is a mess unfortunately.

       

      The addresses have no space in between the house number and the street. ex. Washingtonavenue33

       

      Is there a way I can easy put a space before all numbers in a particular field?

       

      Thanks!

        • 1. Re: Inserting characters into a field
          erolst

          Yes, there is, but how reliable that works in practice depends on the consistency of your data (which, of course, is true for all parsing jobs …).

           

          You can try

           

          Let ( [

            myString = "Washingtonavenue33" ; // replace with field reference

            countDigits = Length ( GetAsNumber ( myString ) ) ;

            pos = Length ( myString ) - countDigits

            ] ;

            Case (

              countDigits ;

              Replace ( myString ; pos + 1 ; 0 ; " " ) ;

              myString

            )

          )

           

          which will work nicely inside an auto-enter calculation – if the house numbers are the only numbers in the address.

           

          Should that no be so, you would need a script (or a Custom Function; needs FM Advanced) that checks recursively from the end for any digits. Let us know if you need such a solution.

          • 2. Re: Inserting characters into a field
            dtcgnet

            I can see a lot of ways that any method you use will end up creating inaccuracies in the converted data. It looks like the house numbers are somehow being added to the end of the street name. What if the name of the street is a number, like I-90, or Highway 32? You'd see something like Highway321234 or I-901234. It's possible someone's added an apartment or suite number to an address, so you might have Washingtonavenue33#4.

             

            I hate to say it, but with the data coming in as it is, creating a solution which allow you to be truly confident in the accuracy of your imported data is going to be difficult.

             

            Just my 2 cents.

            • 3. Re: Inserting characters into a field

              Agreed. Fix the Excel file.

               

              - m

              • 4. Re: Inserting characters into a field
                beverly

                I would concur, but sometimes the Excel is out of our control (and/or we cannot fix in the spreadsheet before import). However, I endeavor to convince whomever has that control to provide the corrected format in future. Especially for those repeat imports.

                 

                beverly

                • 5. Re: Inserting characters into a field

                  Well put.

                   

                  Assuming the Excel file has addresses in separate cells, which doesn't sound like it's the case, with a little programming, you could set up a pre-import scrub of the file.

                   

                  The article below shows some quick (and free) Java to read/write Excel, but accessing Excel from other languages is pretty straightforward too.

                   

                  http://www.vogella.com/tutorials/JavaExcel/article.html

                   

                  Uses the free library: Jxl

                   

                  JExcelApi

                   

                  (From the Web site:)

                   

                  Some Features

                  • Reads data from Excel 95, 97, 2000, XP, and 2003 workbooks
                  • Reads and writes formulas (Excel 97 and later only)
                  • Generates spreadsheets in Excel 2000 format
                  • Supports font, number and date formatting
                  • Supports shading, bordering, and coloring of cells
                  • Modifies existing worksheets
                  • Is internationalized, enabling processing in almost any locale, country, language, or character encoding (formulas are currently only supported in English, French, Spanish, and German, but more can be added if translated)
                  • Supports copying of charts
                  • Supports insertion and copying of images into spreadsheets
                  • Supports logging with Jakarta Commons Logging, log4j, JDK 1.4 Logger, etc
                  • ...and much more

                   

                  ---

                   

                  On the other hand, if the address is all in one field, I'm thinking there would be too many variations to be able to accurately import those addresses. Or, maybe the script would get you 90% there, but you'd still need someone to manually review/correct the incorrectly "fixed" addresses.


                  This is a seriously sticky problem.

                   

                  I need some coffee.

                   

                  - m

                  • 6. Re: Inserting characters into a field
                    CarlSchwarz

                    I notice that the street name is also appended.

                    You could also check for the words "avenue, street, road" etc. and insert a space before them.

                    Use a a similar calculation to above except myString = "street" etc., and for pos use the position() function in the way that checks from the right end of the string.  e.g. pos = position(myString, ...,      );

                    • 7. Re: Inserting characters into a field
                      derickc

                      Thank you for this.

                       

                      I gave it a try as below but it just returns a "0"

                       

                      Substitute ( PostalAddressLine ; ";" ; "  ,  " ) and Let ( [

                        myString = "PostalAddressLine" ;

                        countDigits = Length ( GetAsNumber ( myString ) ) ;

                        pos = Length ( myString ) - countDigits

                        ] ;

                        Case (

                          countDigits ;

                          Replace ( myString ; pos + 1 ; 0 ; " " ) ;

                          myString

                        )

                      )

                       

                      Any ideas?

                      • 8. Re: Inserting characters into a field
                        CarlSchwarz

                        You can't use AND like that.  And only returns one or zero.  Either do the substitute function before the AND, or do the let function after the and to get a text result.

                        • 9. Re: Inserting characters into a field
                          dtcgnet

                          Could you possibly attach a copy of the Excel file, with perhaps just a few lines of actual data? I'd like to take a good look at it (and maybe others would, too).

                           

                          Your formula (which does need adjustments) is going to give you inaccurate results any time there are multiple strings of numbers in an address. For instance, 31st1234 (which is 1234 31st) has six numbers in it, and your "GetAsNumber" command would yield 311234 (6 digits), and after your fixed formula was applied, the result would be "31 st1234".

                          If you HAVE to do it this way, you need to identify the "end" component, and the "beginning" component and build a new string.

                           

                           

                          To get you closer, adjust your formula to:

                           

                          Let comes first. Define the variables in the Let. Then define what should happen when using the variables.

                           

                          Let (

                          [

                          myString = TableName::PostalAddressLine ;

                          countOfDigits = Length ( GetAsNumber ( myString ) ) ;

                          streetPortion = Left ( myString ; Length ( myString ) - countOfDigits ) ;

                          numberPortion = Right ( myString ;  countOfDigits )

                          ] ;

                           

                           

                          numberPortion & " " & streetPortion

                          )

                           

                          For Washingtonavenue33, you'll get 33 Washingtonavenue, which is a little wrong.

                          For 101st1234, you'll get 1st1234 10, which is very wrong.

                          For 31st1234, you'll get st1234 31, again, very wrong.

                           

                          But...the formula above will at least give you some examples to go from.

                          • 10. Re: Inserting characters into a field
                            derickc

                            Hey all!

                            Sorry for only getting back now.

                             

                            I am trying this Let function and its changing the addresses but not in the correct way.

                             

                            Here's the function:

                             

                            Let (

                            [

                            myString = FMnewrobm::PostalAddressLine ;

                            countOfDigits = Length ( GetAsNumber ( myString ) ) ;

                            streetPortion = Left ( myString ; Length ( myString ) - countOfDigits ) ;

                            numberPortion = Right ( myString ;  countOfDigits )

                            ] ;

                             

                             

                            numberPortion & " " & streetPortion

                            )

                             

                            Here's the output:

                             

                            Pad.JPG

                            And here is an example of what the field in the excel document look like with this client:

                             

                            Pad2.JPG

                            This particular one is actually a postal address but the same applies for where its an actual street name. Spaces before and after the number needed.

                            I forgot to mention this function I am looking for also needs to enter spaces before and after the ";" as well as replacing the ";" with a ",".

                            The reason I need this is so Google Maps function with it as at the moment Maps is having a hard time figuring out the persons address.

                             

                            PS. We do not really have names like in the US for example 1st Avenue 33 and so on. Its 99% Blabla street 33 or Bladiebla Avenue 43 if this makes it easier for you perhaps.

                             

                            Thanks for the help once again.

                            • 11. Re: Inserting characters into a field
                              CarlSchwarz

                              Hi,

                              Sorry but can't be bothered making something elegant on a Friday night but this will do it.  I've just nested a stack of substitute statemements

                               

                              Substitute (
                                Substitute (

                                  Substitute ( FMnewrobm::PostalAddressLine; GetAsNumber ( FMnewrobm::PostalAddressLine) ; "|" )
                                ; "|" ; " " & GetAsNumber ( FMnewrobm::PostalAddressLine) & " " )
                              ; ";" ; ", " )

                              • 12. Re: Inserting characters into a field
                                derickc

                                Wow! That might not be elegant in your book but for me its looking great! Thank you!

                                • 13. Re: Inserting characters into a field
                                  derickc

                                  Sorry one last thing. You mentioned earlier that I could also do a check for parts like "street" or "avenue" and insert a space before or after as well.

                                  How would I do that in your elegant substitute function above?

                                   

                                  I'm looking at this bit its not really much of a help:

                                  http://www.filemaker.com/help/12/fmp/html/func_ref3.33.78.html

                                  • 14. Re: Inserting characters into a field
                                    CarlSchwarz

                                    Glad it worked.

                                     

                                    Take the result from the first function that I gave and do the same this time replacing the getasnumber() part with the word that you want to put a space in:

                                     

                                    Substitute (
                                      Substitute (

                                        Substitute ( text to substitute; "street" ; "|" )
                                      ; "|" ; " " & "street" & " " )
                                    ; ";" ; ", " )

                                    1 2 Previous Next