14 Replies Latest reply on Feb 6, 2017 8:34 PM by JackRodges

    Address Cleaning and Import

    JackRodges

      I am importing addresses from web pages by copying, pasting into word, cleaning up, copying and pasting into a global in a table. I then create one record for each line, define it as name, company, etc. This is needed due to the rampant non-standardi data on pages.

       

      I then put those records into a global and paste it into a global field in my contacts table so that I can create records from the data.

       

      The pasted result looks like:

      <New Record>
      <Person>Tori Oglesby
      <Company>1st Quality Insurance Group - Bulk Mail Processing Center
      <Street>250 East Park Ave
      <City>Lake Wales
      <State>FL
      <Zip>33853
      <Category>Insurance
      <Note>BETTER PRICE . BETTER SERVICE . BETTER CHOICES Each insurance client doing business with 1st Quality Insurance Group, is provided a professional insurance consultant to assist with applicat... (more)

       

      Then I loop through this global field and create contact records using the <xxx> to determine the field to set.

       

      What I am wondering is if there is a standard for the <xxx> that is used for import and export. I recall seeing this idea a few decades ago. And if there is a standard are there sources of address lists using this standard. And what is it called?

        • 1. Re: Address Cleaning and Import
          IT_User

          Do you mean like an xml vCard?

           

          vCard - Wikipedia

          1 of 1 people found this helpful
          • 2. Re: Address Cleaning and Import
            JackRodges

            Like that, yes. But by natue my idea is much simpler. VCard is going to extremes and changing every week.

             

            I edit the import and assign the fields to place it in using my <tags>. Maybe the data I am copying would work with vcard?

             

            There was a standard of two decades ago that used an idea similar to mine using the <tags>. These are easy to script within a loop since you if for each tag and also easy to modify the scripts.

            • 3. Re: Address Cleaning and Import
              keywords

              It looks like the data in your global field is basically a return delimited list, with the first word on each line being a tag. I think the approach I'd use would be something like this:

              Write a script which—

              1.     extracts the data from each line as a variable, using a combination of GetValue ( ) function and MiddleWords ( ) function starting with the second word.

              2.     creates a new record with fields set to auto-enter the relevant variable.

              The key to this approach is to have the fields already set to auto-enter by calculation, with the calculation being simply a named variable ($person for the Person field, etc). When a new record is created from the menu, no value would be entered into the fields because the relevant variables don't exist, but when a new record is created by the script which creates the variables, the values will be posted to their relevant fields.

              • 4. Re: Address Cleaning and Import
                philmodjunk

                Hmmm, if the tags were actual field names in your table, you might extract that field name to use with set field by name to set the field named in the tag. There's a certain "brittle quality" to that approach that I'm not a big fan of, it locks down field names such that a future field name change could break the script, but it has a certain efficiency that I like as well...

                • 5. Re: Address Cleaning and Import
                  Malcolm

                  gofmp wrote:

                   

                  What I am wondering is if there is a standard for the <xxx> that is used for import and export. I recall seeing this idea a few decades ago. And if there is a standard are there sources of address lists using this standard. And what is it called?

                  If you are going to that effort why not generate XML, or more simply, push the data into a spreadsheet then import as CSV or Excel?

                   

                  Malcolm

                  • 6. Re: Address Cleaning and Import
                    JackRodges

                    Here's the conditions of my idea;

                    Copy addresses from a web page or open a text file of addresses. Generally the order of the fields is not always the same somes person's name sometimes a company sometimes both, etc.

                     

                    So, I use Word to clean up the data and get it into the form

                    Blank Line

                    First Name Last Name

                    Job Title

                    Company

                    Street

                    City

                    State

                    Postal Code

                    Phone Number

                     

                    etc.

                     

                    Each word doc will be different. But I have used find and replace, etc. to produce this type of entry.

                     

                    I paste the data into a global field and then create a record in that table for each line. The record has a field for Type of data: New Reord, name, company, title, street, etc.

                     

                    I run a script that set the type of data. and let's me search for City Name, state name, etc.

                     

                    Then I run a script to create a text variable like:

                     

                    <New Record>
                    <Person>Tori Oglesby
                    <Company>1st Quality Insurance Group - Bulk Mail Processing Center
                    <Street>250 East Park Ave
                    <City>Lake Wales
                    <State>FL
                    <Zip>33853
                    <Category>Insurance
                    <Note>BETTER PRICE . BETTER SERVICE . BETTER CHOICES Each insurance client doing business with 1st Quality Insurance Group, is provided a professional insurance

                     

                    There could be many addresses in this variable. Then I use this data to create new contact records using the <xx> to target a field in the record. The nice part of this script is that if a particular tag is not in the variable for one or more records, it doesn't matter. The tags do all the work via if statements.

                     

                    The terrible state of data entry on the web pages, text documents of address lists created by non-profits and government agencies for instance, makes this method of cleanup and tagging essential for my purposes.

                     

                    The reason for my question goes back decades where I encountered something similar to this for importing/exporting text where scripts would fill in the fields rather than an import editor like FileMaker's.

                     

                    This is simpler than importing using the import editor since fields don't have to be matched, created, etc. And it allows the cleansing of the data using Word such as Lake Wales, FL 33835 and I can find and replace (, FL ) with (PP FL PP) to create the 3 lines above

                    • 7. Re: Address Cleaning and Import
                      JackRodges

                      Yes, trying to use set field by name is subject to self destruction if someone changes the name of the field.

                       

                      Using my method of

                       

                      if var1 = "<Street?"

                      set field street to var2

                      end if

                       

                      Allows changing the name at any time since set field in this instance isn't tied to the field name which is one of the reasons I prefer FIleMaker after suffering through Basic and dBase.

                      • 8. Re: Address Cleaning and Import
                        Malcolm

                        gofmp wrote:

                         

                        There could be many addresses in this variable. Then I use this data to create new contact records using the <xx> to target a field in the record. The nice part of this script is that if a particular tag is not in the variable for one or more records, it doesn't matter. The tags do all the work via if statements.

                        You are inventing XML-lite. By adding a little more structure, such as end tags, you could start to take advantage of all the tools that grok standard XML, including FMP.

                         

                        <?xml version="1.0" encoding="UTF-8"?>
                        <Record>
                        <Person>Tori Oglesby</Person>
                        <Company>1st Quality Insurance Group - Bulk Mail Processing Center</Company>
                        <Street>250 East Park Ave</Street>
                        <City>Lake Wales</City>
                        <State>FL</State>
                        <Zip>33853</Zip>
                        <Category>Insurance</Category>
                        <Note>BETTER PRICE . BETTER SERVICE . BETTER CHOICES Each insurance client doing business with 1st Quality Insurance Group, is provided a professional insurance</Note>
                        </Record>
                        </xml>

                         

                        malcolm

                        • 9. Re: Address Cleaning and Import
                          JackRodges

                          True. But then I would have to learn/deal with that which is not in my mental budget after 30 years.

                           

                          I can conceive an idea and implement it in very little time. Can't do that by learning/debugging SQL, XML, etc.

                           

                          Someone else might find it worth the time since they will be entering an environment where those things rule but I will not do that.

                           

                          I am not against learning this skills (for someone else of course).

                           

                          My method will create related records such as telephone, things to do, keywords etc. Can you do that with xml out of the box?

                           

                          To create the related record I only have to click a checkbox item...

                          • 10. Re: Address Cleaning and Import
                            Malcolm

                            gofmp wrote:

                             

                            My method will create related records such as telephone, things to do, keywords etc. Can you do that with xml out of the box?

                            XML import is a standard feature. Has been forever. How you do the import do it is up to you.

                             

                            The only reason I suggested it is that the markup you are creating is very similar to XML. At present, your markup is compatible with your code and nothing else.  If you tweaked your markup a little it would be standard XML and it would be compatible with many, many other tools, including FMP's import/export engine.

                             

                            You may be able to leverage XML to make your work easier. For instance, when you have data in reasonably well formed web pages you could use javaScript to manipulate the DOM and output XML.

                             

                            malcolm

                            • 11. Re: Address Cleaning and Import
                              JackRodges

                              Yes, I see what you mean. No problem to add your idea.

                               

                              Next question which is what I meant to ask:

                               

                              Is there a standard set of labels that other apps would recognize? I can easily add/replace these.

                               

                              If I use <addrs> </addrs> then it fails if everyone else is using <address>.

                               

                              Maybe Wikipedia has a standard...

                              • 13. Re: Address Cleaning and Import
                                Malcolm

                                Don't get lost in the details. 99% of what you'll do with XML is going to work if you follow one rule: balance your markup tags! Look at the example I gave above. It's really basic. You put everything between start and end tags.

                                 

                                Unlike HTML with XML you can make up your own tags. Having said that, "address" is easier to understand than "addrs".

                                 

                                --

                                 

                                Malcolm

                                • 14. Re: Address Cleaning and Import
                                  JackRodges

                                  Thanks, but my idea works so simply that I don't want to add complexity.