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.
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!
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 ????
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.
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
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.
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
Matching on the complete email works fine.
No parsing or email component splitting is required.
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?
EmailMatch.fmp12.zip 71.3 K
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
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:
It will, indeed, fail.
If you enter find mode and place this in the field:
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.
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.
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.