create batch import script

[Rizal Az]

Hi all,

Can anyone teach/help me how to create batch import script?

I have multiple .xls files (100+) in 1 folder that I would like to import to 1 table.

Appreciate your help.

Thanks

[Fenton]

I would need an example of a couple of your Excel files to set up an actual Import; I could whip up something tomorrow. There are 2 main things you need to do:

1. Get a list of the file paths in the folder. I would use AppleScript for this, in Perform AppleScript step, if it is to done on a Mac.

2. Convert each path to FileMaker syntax, then put that into a script variable, and Import, in a Loop.

The AppleScript to get the files would look something like this. You can run this in Script Editor. At the end you would need a tell block to set the result into a FileMaker field. So right now you can just run this to see the files.

set theFolder to choose folder



tell application "Finder"

	set theFiles to (files of theFolder whose name extension is "xls") as alias list

	set old_delims to AppleScript's text item delimiters

	set AppleScript's text item delimiters to ASCII character 13

	set theFiles to theFiles as Unicode text

	set AppleScript's text item delimiters to old_delims

	return theFiles

end tell

Result looks like:

Macintosh HD:Users:fej:Documents:FileMaker:My_FM:AS:IMPORT EXPORT:Excel Import:Import_Excel_8:Done:DomainNames1.xls

Macintosh HD:Users:fej:Documents:FileMaker:My_FM:AS:IMPORT EXPORT:Excel Import:Import_Excel_8:Done:DomainNames2.xls

Whereas a FileMaker syntax path looks like:

filemac:/Macintosh HD/Users/fej/Documents/FileMaker/My_FM/AS/IMPORT EXPORT/Excel Import/Import_Excel_8/Done/DomainNames1.xls

Basically, take them out, one at a time, with GetValue ( paths; $counter), then (pseudo code):

"filemac:/" & Substitute ( mac_path; ":"; "/" )

Unfortunately, that's all I can do tonight. Zip up a couple of your Excel files so we have something to work with.

[Fenton]

OK, here is a simple example. It will Import the 3 Excel files in the Excel_files folder, then move each to the Done folder. To see again, move the files back again. It will NOT import the same records again however, as it uses the "Matching records" option (not entirely necessary, as we're moving the files into another folder; but you can see how it works to stop duplicates).

It uses AppleScript for both getting the file list, and for moving the files.

On Windows you could use the free MooPlug to do similar; though I believe it uses a "list files of folder" command, which returns only the file names. This is not a problem, as you can either use a "relative" FileMaker path, or reconstruct the full path by adding back the folder path. (AppleScript's list folder command is the same.)

Import_Excel_8.zip

[org]

Fenton

Just a quick thanks. Was looking at similar task last night - quick search on the forum and you've saved me some painful Applescripting by posting this example.

I owe you one. Thanks again.

Olly Groves

[RodM]

Hello,

excuse me... because this is a similar question... should have i replied or created a NEW post?

Your obvisious mr.Mac.. any clue to Windows

I have an appllication that purges dateNamed fp7 files out to the desktop.

If i can have my user stuff a folder with multiple certain datenamed files..

How can I import All these files into one file again?

The import dialog seems to behave only with named files.. where as my files change.. I can manually do so but very clumbersome.

Thought.. if i copied those named .fp7files into a folder.. renaming them 1.fp7, 2.f79 in the import dialog box, then i could problaly name 15-20 fictious file names.. set error capture on.. and test for matching records.. MIGHT? assemble these files to one.. is kludgy

Thanks for any ideas.. to get me thinking in the right directions

[RodM]

thanks for your effort , I did say I am a Windows dork.. What I got thanks to all, was I need to look at some MOO plugin for this. I am not importing excell but real exported Filemaker files from the same structure i export out to... They are Daily Journal.fp7 files

I first need to cut in paste text to create a filenames in records.. mulitple records of misc names.fp7

I thought I could import using something like $getFilenames inside a Loop

and figure late that the Import process expects a particular filename.. if i do it manually..

Problay the MOO plugin can do 1 more thing missing saving me the cut in paste text to records for filenames.... is read a folder that has .fp7 files in it.

let me just import all files... matching records

Thanks next is MOOOOOOOoooooooo time :