Jump to content

  •  

Photo

create batch import script


  • Please log in to reply
5 replies to this topic

#1 Rizal Az  newbie

Rizal Az
  • Newbies
  • PipPipPipPip
  • 4 posts
  • FM Application:9

Posted 14 March 2009 - 07:45 PM

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
  • 0

#2 Fenton  Post Master General

Fenton
  • Moderators
  • 5,046 posts
  • FM Application:11 Advance
  • Platform:Mac OS X Snow Leopard
  • Skill Level:Expert
  • Membership:TechNet, FileMaker Business Alliance
  • Time Online: 17h 12m 20s

Posted 14 March 2009 - 09:58 PM

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.
  • 0

#3 Fenton  Post Master General

Fenton
  • Moderators
  • 5,046 posts
  • FM Application:11 Advance
  • Platform:Mac OS X Snow Leopard
  • Skill Level:Expert
  • Membership:TechNet, FileMaker Business Alliance
  • Time Online: 17h 12m 20s

Posted 15 March 2009 - 03:19 PM

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.)

Attached Files


  • 0

#4 org  newbie

org
  • Members
  • 16 posts
  • FM Application:12 Advance
  • Platform:Cross Platform
  • Membership:TechNet
  • Time Online: 1d 48m 54s

Posted 27 April 2010 - 06:45 AM

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
  • 0

#5 RodM  journeyman

RodM
  • Members
  • 429 posts
  • LocationEugene OR
  • FM Application:12 Advance
  • Platform:Windows 7
  • Skill Level:Intermediate
  • Time Online: 3d 15h 35m 20s

Posted 03 October 2010 - 06:31 PM

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
  • 0
RodM

#6 RodM  journeyman

RodM
  • Members
  • 429 posts
  • LocationEugene OR
  • FM Application:12 Advance
  • Platform:Windows 7
  • Skill Level:Intermediate
  • Time Online: 3d 15h 35m 20s

Posted 05 October 2010 - 07:22 PM

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 :
  • 0
RodM




FMForum Advertisers