6 Replies Latest reply on Jun 11, 2012 3:01 PM by philmodjunk

    Importing data based on nearly matching fields

    PeterMontague

      Title

      Importing data based on nearly matching fields

      Post

      Is there any way to import records based on pieces of data that are almost but not quite identical?

        • 1. Re: Importing data based on nearly matching fields
          LaRetta_1

          Hi Peter,

          No, sorry - if importing updates existing records, it must match exactly or import all.  It would be best to import all (into a temp table) and then run your comparisons there.  The type of comparisons required would depend upon the rules you would establish in the comparisons.  If you wish to share some of those rules, I could provide a more comprehensive example of how to achieve it.  :^)

          • 2. Re: Importing data based on nearly matching fields
            philmodjunk

            If you can spell out "rules" for what variations to the data might be an "almost match" (First 5 characters must match, or something) , you may be able to set up a calculation field in the source table that turns the "almost match" into an exact match for your import. (And this assumes the Imprecision lies with the source table data rather than the import data...)

            • 3. Re: Importing data based on nearly matching fields
              PeterMontague
              That might work. Where would I put these rules?
              • 4. Re: Importing data based on nearly matching fields
                philmodjunk

                I think it's a long shot, actually, but if you care to describe what kind of "almost match" you are dealing with, we can take a shot at setting such a thing up if it's possible.

                • 5. Re: Importing data based on nearly matching fields
                  PeterMontague

                  Maybe the first five words of the title?

                  Peter.

                  • 6. Re: Importing data based on nearly matching fields
                    philmodjunk

                    Won't work as a single phase import, but that option can be made to work with the suggestion LaRetta made of importing the data into a different table first, then matching records in order to update.

                    This calculation, for example: Leftwords(TitleField ; 5 ) will return the first 5 words in that field. If you defined that calculation field in both your main table and the temporary table that you first import into, you can define calculations and you can also set up an Import Records operation that uses the matching fields option with these two calculation fields as the matching fields.

                    To avoid using the added import table, you'd have to modify the data you are importing to include a field with just those first 5 words. That might be easy from something like an excel file, more of a challenge to impossible when importing from other sources.

                    And keep in mind that this is still an exact match of values, a typon of just a single extra character in one of these first 5 words and the records still do not match.

                    Much depends on how "clean" and predictable is your data. The more likely you are to have data entry error type variations, the more issues you'll have trying to match up records.

                    IF you have messy data, there are a number of ways to use filtered portals to partially match values in order to search for records that should match to each other and don't.