importing data from Excell

[Bartlegend]

I want to import data from an excell file into my filemaker database.

I have a huge list of movies in my database and i have the same list of movies in excell with some extra information with them that i want to import into my database.

Normally i would use the title of the movie as "primary key" to make sure all the extra info will be listed under the correct films in my database.

Only problem is... The films in my database are listed like "The green mile" and the films on my excell file are listed as green mile ,the.

And there are probably some other things that aren't the same but i don't know that yet.

For the moment i would like to create a script that allows me to reference the excell movie title to the filemaker movie titel to make them the same so i can import the extra data.

This must be kidstuff for the lot of you.. But since i am not really an programming wizard i haven't got a clue (just started working with filemaker).

[BobWeaver]

Nope, it's not kid stuff. This is a problem that comes up again and again, and there's no pat solution.

The first thing I would do is import the Excel file into a separate Filemaker table so that it's all in Filemaker. Then, you could create some calculation fields to try to normalize your movie title. You can use the substitute and filter functions to remove punctuation and words like "a" and "the." That kind of thing would normalize the title in your "The green mile" example by converting the data from both tables to "green mile" which would then match. So, you would use these normalized fields as your key fields. You may still need to do more text processing than that, but it's a start.

The next thing you can do is make another calculated field to count the number of related records between the original table and the imported data table. This will then let you do a search for records in one table that don't have a counterpart in the other table. This gives you the opportunity to check if there is legitimately no related record, or whether you need to add more goodies to your normalization text calculations.