Newbie needs to import excel data to FM 5.5

[tink]

Hi all you that know how !

New to this forum. I usually work in other development environments and have not have experience with FMPro. I have a client with a FMPro 5.5 system and they need to regularly (weekly) import an Excel file into their existing database. The existing database has 67 fields. The data to import has 17 fields.

My questions are:

Does the data to be imported have to have the same field names as the FMPro database?

Do I have to have 67 fields in the Excel file (with all Field Names the same as I found in "Field Definitions"), and only have 17 of those fields holding the data I need to import?

Should I be looking at writing a script for them to run weekly, or can I just train them to go File -> Import Records... ?

They have around 11k records they store currently and obviously I don't want to botch that up.

Please guide me, thanks in advance...

Tinkerbell

[Wim Decorte]

You defenitely need a script otherwise you need to relay on whoever does the import to do it correct every single time...

You don't need the same field names or the same number of fields. When you make the script, in the 'import' script step you basically line up the FM fields and Excel columns that need to go together. These get stored with your script.

[Lee Smith]

Hi Tinkerbell, and welcome to the Forum.

To add to what Wim has posted.

FileMaker remembers the last "Import", "Export", "Sort", "Find" or "Page Setup" that is performed. Do these steps manually first, and the corresponding Script Step will use it as its default.

So, perform the your import manually first, and line up your fields as Wim has said, Select the appropriate options in "Map Legend" and "Import Action" that apply, and hit the Import button. Once you have your Import the way you want it, then immediately create a script using the Import Step.

If you create the script first (i.e. before you have the Import the way you want it), it will necessary to Edit your Script to change the Import to the new one. To do that, select your script in ScriptMaker, and then the Edit button, and then Okay button. This will bring up a Dialog Box which asks you if you want to Keep or Replace any of these steps, "Import", "Export", "Sort", "Find" or "Page Setup", be sure to check the "Replace" next to the "Import" step and then click the Okay Button.

HTH

Lee

[tink]

Thanks so much.

I'm seeing the client today and will test it there on site. Unfortunately I don't have FM Pro on my system here, so I'll have to do all testing on site.

So that I don't damage anything that they've got set up, can I copy their system to another directory and "practise" on that? Or are files mapped to certain directories?

In any case, if I don't hear from you, I'll let you know how I go...

Thanks again,

Tink - the good fairy

[stanley]

Tinkerbell:

Welcome to the Forums.

You should be able to copy the files to another directory & work with them there safely. Be sure to copy the whole lot, retaining any nested folder arrangement (FMP works, for the most part, with relative file paths.) Also, ensure that there are no other users accessing the files before you copy, to make sure that you get good clean copies.

Good luck.

-Stanley

[tink]

Hi,

Thanks so much for all your good advice. Couldn't have done this without you. The client is VERY happy. It's crazy, but what they have been doing for years (and I've only just found out) is printing a hard copy of the Excel file and manually data entering that into their Filemaker system. They really do think now that I'm the "good fairy who can" !

I've got the script set up - thanks Wim and Lee for your step by step instructions. It works beautifully in testing, but I have one challenge to sort out before I do it on live data.

They have one field that displays as a Yes/No Radio button on the form, (and to my surprise, displays as Yes/No radio button when list is viewed as a table also....I'm used to different dev environments) In the import data, this field holds either a 1 or 0. I've tried changing this to a Y or N respectively, and neither make the "Yes" selected in Filemaker.

Do they need to be T/F or .t./.f. in the Excel file? Can I import this into Filemaker? What's the trick?

Thanking you all in advance

*~*~~***~~~**~~****~~~~**

***~~***~~*****~~~~***~~*

(fairy dust...)

Tink

[stanley]

Tinkerbell:

You can add a loop to your import script to change the data from 1 to Yes and from 0 to No, just after the import.

-Stanley

[tink]

Hey Stanley,

Thankyou! So I need the physical word 'Yes' or 'No' in my data. I'll probably just replace it in the Excel file before I import.

Your help with copying files was appreciated - sent it all out to a test.fp5 and gave me piece of mind.

Ta,

Tink

[tink]

All done !

Client Happy - Tinkrbell Happy.

Much Appreciated...

**~~~**~~~**~~***~***~

~~~**~***~****~***~*~*~*~

*~*~*~*~*~*~*~*~*~*~*~*~*

Tink