1 2 Previous Next 16 Replies Latest reply on Oct 6, 2014 1:25 PM by BruceRobertson

    Importing using email as matching field

    keychange

      I can't believe I haven't encountered this in the past. But I am trying to update my database to remove bounced emails.

       

      So I import from csv or xls format where I am using the email address as the matching field. However the processs finds no matching fields despite all of the email addresses being valid records. I realise this has to do with the '@' in the middle of the string but I can't come up with a process that will allow me to overcome the issue. I have around 700 records to update

       

      Any suggestions please.

       

      Andrew

        • 1. Re: Importing using email as matching field
          mikebeargie

          Strip out the @ symbol in excel before import, then have a calculated field in filemaker as well to strip it out.

           

          Substitute(email ; "@" ; "" )

           

          The substitute function works the same way in FM and Excel.

           

          Use that as your match field, you are correct that the @ symbol causes an issue.

           

           

          Or you could import your csv to a temp table, and use a looping script to check each record against your current data, clearing the temp table when you are done.

          • 2. Re: Importing using email as matching field
            DavidJondreau

            You could change the default storage language to Unicode.

             

            I believe that would have a consequence of making capitalization matter though. david@wingforward.net would not match David@wingforward.net

             

            I always auto-enter my e-mail address fields as Lower ( Self ) though. I recommend it.

            • 3. Re: Importing using email as matching field
              keychange

              Mike

               

              I did exactly as you suggested - the substitution works and I have addresses that now look like andrew.hunterbigpond.com  in the spreadsheet and the new email field.  But it still won't find the matching fields!!

               

              When you have the "@" full email address and do a find using the full email it won't work because the "@" has to be escaped out.  Doing a find on the revised email works perfectly but won't match.  Is this because I am using a hosted file? - Obvioulsy not as I downloaded and tried on local copy and same result - no records found!!!

               

              I tried with both xls and csv, same result!

              • 4. Re: Importing using email as matching field
                keychange

                IMported the data as a new FMP file and tried the import - still won't match the fields.

                 

                I substituted to remove the " . " and still no matches !!!

                 

                I have been developing FMP since 4 - I have imported matching countless time - but this has be completly thrown.

                 

                The fields are both text fields - the files are not RO and the data in the matching fields is an exact match - why ????

                • 5. Re: Importing using email as matching field
                  mikebeargie

                  you need to perform the substitution on BOTH sides, in the excel source file, AND a field in filemaker that you can match against. They should also be formatted as text fields, and you might also want to run the trimall(email;1;1) function as well to remove any errant spaces.

                   

                  importing to a temp table and processing from there would give you more control, including the ability to escape the @ symbol when you perform a find inside of a loop.

                  • 6. Re: Importing using email as matching field
                    keychange

                    Yes I realise and indeed it is both sides and yes they are text fields.  Have imported to temp table and same result - the match find process is broken!

                     

                    I have exported from master file a subset of just the required fields and I have created an FMP fle from the csv file and it still doesn't work.  I think I need to try a fresh FMP install

                    • 7. Re: Importing using email as matching field
                      Stephen Huston

                      You might try converting the CSV file to a new FMP file, do the data cleanup in that intermediary file, and then import from that FMP file to the final target FMP file.

                       

                      I have seen this work much better than trying to clean up data within Excel or CSV sources.

                      • 8. Re: Importing using email as matching field
                        keychange

                        Well that was the most tedious process I can remember with FMP.   I created new table of the unsubscribed emails with @ substituted out.  Then created script loop  one by one copied the field then went to main file completed a find, edited a note a field to flag the find successful then rather than just run a loop field update, I exported the data with the unique record number and then ran the import using the record number as the match field and it ran perfectly.

                         

                        I still have no idea why the import on matched edited email field didn't work.

                         

                        I now have to remember this for next time I do mail out and need to record bounced addresses

                        • 9. Re: Importing using email as matching field
                          BruceRobertson

                          Matching on the complete email works fine.

                           

                          No parsing or email component splitting is required.

                          • 10. Re: Importing using email as matching field
                            BruceRobertson

                            Your conclusion just doesn't seem match the results I get.

                            Attached is a simple example file that demonstrates email matching while importing from Excel.

                             

                            Maybe this can help identify the problems you are experiencing, maybe we can spot the difference between your technique and what is demonstrated here.

                             

                            In this simple file, there are 100 records with dummy names and email addresses, there are scripts that generate a random found set, then export as Excel, to a desktop file, bounce.xslx.

                             

                            Then a find all is performed, and the bounce file is imported, using match on email address.

                             

                            The resulting found set matches the found set that was created for the export process.

                             

                            This simulates receiving a list of email addresses which you need to test for matches within your existing database.

                             

                            So import using match on email address DOES work, and does not require any parsing of the email address.

                             

                            How does this differ from the method you were using?

                            • 11. Re: Importing using email as matching field
                              keychange

                              Here is an article discussing the issue http://hbase.net/2012/04/12/getting-around-the-symbols-multiple-uses/

                               

                              Try this with your database - select an email address - copy it - enter find mode and paste the address - you will get a not found error.

                               

                              I assume that your database probably works because all of the domains are the same.  Believe me there is an issue and it has been there forever - but for me even removing the "@" didn't work - which I really can't understand but it's the truth.  I have been developing FMP since 98

                              • 12. Re: Importing using email as matching field
                                BruceRobertson

                                No.

                                 

                                Your assumption is completely wrong, and the article you cite has no relevance to this discussion.

                                 

                                Did you even look at the file? Did you try anything yourself?

                                 

                                There is no problem except for your scripting, which we have not yet seen.

                                 

                                Though your topic is "import using email as a matching field" it DOES work and HAS worked since at least FileMaker 6.

                                 

                                It appears that you are in fact NOT using the import update-matching feature.

                                 

                                You describe a problem performing a scripted find. But THAT problem isn't a problem either, if you merely quote the email.

                                 

                                If you enter find mode and place this in the field:

                                 

                                somebody@somwhere.com

                                 

                                It will, indeed, fail.

                                 

                                If you enter find mode and place this in the field:

                                 

                                "somebody@somwhere.com"


                                It will work.

                                 

                                But - as mentioned above - this is irrelvant to the subject, which is import using email as the match field.

                                 

                                FWIW I have been selling FileMaker solutions since 1994.


                                • 13. Re: Importing using email as matching field
                                  BruceRobertson

                                  Addendum: just tested the import update-matching feature in FileMaker 5.5.

                                  The feature existed back then; and it worked for this purpose, using a full unparsed email.

                                  • 14. Re: Importing using email as matching field
                                    keychange

                                    Ok thanks for your input oh and for the record it wasn't scripted it was doing a manual import.

                                     

                                    Good to know that you don't experience the problem.

                                    1 2 Previous Next