Auto Excel Import

[LindaG]

Hi

I'm new to the FileMaker realm. We have 1,200 Excel spreadsheet files containing the same fields. Each client currently has their own spreadsheet. I would like to merge these files into one database. But I'm not crazy about performing 1200 imports!

Is there a way, via script, that I can automate this import process but allow a pause to still be allowed to specify the file name each time? I am using vs. 5.5 on Windows98. Any help would be appreciated ... I've currently imported five and I'm already irritated with it! Thank you.

Linda Gent

DAC

[cjaeger]

scripting in filemaker:

after the first import, simply do a script import databse, restore import options. Leave file blank.

not just hit ctrl-1 (apple-1), and navigate to the next file.

more comfortable:

AppleScript:

Simply write a droplet to import file in filemaker

(take a droplet template, then

for every file in list:

tell application "finder"

duplicate file xyz.xls

copy file xyz copy.xls to to "tempfile.xls"

end tell

tell Application "FileMaker Pro"

tell database 1

do script import --- this FM script imports" tempfile.xls"

-- with stored import order

end tell

end tell

(code needs tweaking, idea is to script import a temp file in a predefined location and rename/move the excel files to tempfile....)

windows:

the key is to script excel to put (most or all) files into one spreadsheet.

make sure you do inclue the info which client the data belongs to.

[LindaG]

Hi cjaeger

Thank you for responding to me. I do not have access to AppleScript (I don't think) - We have no MACs.

When I tried to combine my Excel Worksheets, I got a memory error partway through the fourth import. I also tried copy/paste to combine them. I don't think our computers have enough juice to combine that many spreadsheets - and it still would be a manual process, right?

The Worksheets are named what I am considering, the ClientID. For instance, 0031SMITHCO.xls. I was hoping to write a script to import a specific filename based upon each Worksheet. Sorry I've been a bit unclear. I've just tried so many things that I'm a bit lost. I wish I could at least enter a series of file names into FM and then use that within the script to import.

Can you help me?

Linda

[LindaG]

I'm sorry, I forgot to mention that I have a database with the Client ID. I copied text from a Word document where the IDs were kept and stripped it. It matches the Excel Worksheet file names. I am trying to create the related 'client activities' and also set a field within it, to the ClientID (or in this case, the Excel file name less the .xls). I think I can do this after each import by setting the ClientID field through each imported set. I'm just trying to automate this whole thing. It's a pretty big undertaking, it appears, regardless of how I accomplish it.

Sorry to be so long-winded. I just need this by Monday for a demonstration on why a database (particularly FileMaker) is the answer. I think I committed to too much.

Linda Gent

DAC

[Fenton]

Short answer: Use a plug-in. There are several choices. Since you have the Client IDs, which you can use to reconstruct the file path of the files, then you could do this with David McKee's free FileToolbox plug-in.

http://www.fmplugin.org/compile.htm

It has some examples. You will have to put together the file name, from the file path of its folder (same for all) and the Client ID.

Then Rename each file to a fixed name, for the Import.

Then Rename it back to what it was.

You may want to write the error returned, if any, to a field, so you know that one didn't work.

This is a cross-platform solution, which I've played with. I have some, but not all of the finished code for this. I'll be available off and on today if you need more help.

I'm also in San Diego, 619-692-1529

[LindaG]

Hi Fenton,

Thank you. I've reviewed File Toolbox and it appears to be able to do what I need for the mass conversion. I think for my demo, I'll just use two or three customers. This is going to take some time to do.

I'm a bit disappointed that I have to use another program. I'm just learning this one. But after the data is switched maybe things will be easier.

Linda Gent

DAC

[SteveB]

Use WinBatch, which is 'the' Windows automation tool. It can handle repetitive tasks very easily, as well as doing a lot of other stuff. You can download a trial copy at www.wilsonware.com

Its our version of Applescript, only more powerful.

Steve

[cjaeger]

Its our version of Applescript, only more powerful.

sure is. But can it access Filemaker fields, layouts, scripts by name or

get the calculation formula for field xxx just as easy as AppleScript does?

In Filemaker, WinBatch has to be restricted to everything you can point at with your mouse, copy & paste or access of the Windows registry.

Unfortunately, FileMaker is not particularily scriptable in Windows apart from open, doscript and quit. Something that should be fixed by FileMaker for serious business use. ....

But - WinBatch probably will do the job just as well in this case ...