Script to import FMP records with variable File Names

[Smidge500]

 

I'm trying to write a script to import records from a secondary FMP database. 

Each FMP file to import will have it's own unique name, but will have the same prefix  eg. ImportFile_230525.fmp12

Is there a way of scripting a wild card filename......like import any file in a specified folder that begins with "ImportFile_23" ?

Any advice would be greatly appreciated. 

thanks

[comment]

In theory, if the folder in question is inside the Documents folder, you could use the Get ( DocumentsPathListing ) function to get a list of the files in the folder. However, in practice, this would be both slow and cumbersome.

A better way would be to use either AppleScript (on macOS) or a plugin (e.g. the free BaseElements) to get the list.

Once you have the list, your script can loop over it and import each file in turn.

 

[Smidge500]

all my files will be saved on a server, rather than my local drive. But I'll download BaseElements and see if that helps..

 

thanks

[Smidge500]

hello, coming back to this topic. 

Any advice on the correct steps to script the Loop, so it imports the first .xlsx file in the folder, then move to the next one?

thanks!

 

 

 

[comment]

Assuming you have a $filepaths variable containing a return-separated list of full macOS paths to the files you want to import, you would do something like:

Loop
    Set Variable [ $i; $i + 1 ]
    Exit Loop If [ $i > ValueCount ( $filepaths ) ]
    Set Variable [ $filepath; ConvertToFileMakerPath ( GetValue ( $filepaths ; $i ) ; PosixPath ) ]
    Import Records [ “$filepath” ] 
End Loop

I am afraid I am not able to test this at the moment.

 

[Smidge500]

thank you, it's not working yet but that may be because I haven't worked out the correct format for my $filepath (it's coming from a remote server). But I'll keep tinkering. 

 

 

[comment]

1 hour ago, Smidge500 said:

I haven't worked out the correct format for my $filepath

I would start by inserting one of the files into a container field, as reference only. Then look at the result of GetAsText ( YourContainerField ). The last line should have the full Filemaker path to the inserted file. 

For more:
https://help.claris.com/en/pro-help/content/creating-file-paths.html

 

[Smidge500]

Ok I have the correct server path now, however it's still not working.

When I run the script debugger, it's Exiting the loop before it even gets to the Import Records step.

This is what I have, does anything look amiss to you ? 

[comment]

Sorry, I don't see the entire picture here.

First, what is the exact path that needs to be produced?

And, even more importantly, where is the list of files to be imported and what exactly does it look like?

 

[Smidge500]

the path is to a folder on our server, that contains multiple .csv files. 

Oh I may have missed a step. I don't have a list of files. Where in the script would I add that?

I've attached the screenshot of my script so far... 

 

thank you !

[comment]

I am afraid we're not making much progress here...

Back in May I suggested a couple of ways to get a list of files in a folder. I don't know what, if anything, you have done on this front since then.

I also asked to see an example of the path produced by inserting an example file into a container field. Instead you are showing me the same screenshot of your script I have already seen.

I am also confused regarding the type of files you intend to import. At first you mentioned .fmp12 files. Then it became .xlsx files. And now you say they are .csv files. This is important, because if they are text files (such as CSV) you may have yet another way to get the list* - see:
https://fmforums.com/topic/110054-fmp-on-sonoma-import-text-from-folder-problems/?do=findComment&comment=491708

 

---
(*) Alas, this can work only if the CSV files have a .txt extension.

 

Edited March 7, 2024 by comment

[Smidge500]

When you said you couldn't see the entire picture, I thought you were referring to the attached picture, hence I attached again. 

Last year I was trying to import multiple FMP files, but now I'd like to import .csv or .xlsx. I'm going to concentrate on .csv for now as they are the files we currently need to import. 

Path to the folder containing the files to import looks like this:

/Volumes/BH_VFX/files_to_import/

I'm using Base Element now as you suggested, so my new script looks like the below. 

Unfortunately it's getting stuck, at the actual Import Records stage. It seems to completely skip that stage. 

Also in your script example above, you use $filepath & $filepaths, is that intentional? 

 

 

 

 

 

[comment]

The BE_FileListFolder() function can return either a list of file names or a list of full file paths. The default (which is what you are using now) is file names only. So you need to either prepend the folder path to each file name in the returned list, or switch the function to return a list of paths.

I would suggest you try the latter first. Then your script could be like this (again, untested):

Set Variable [ $folderPath; BE_FolderSelectDialog ("Select folder ..." ) ]
Set Variable [ $filePaths; BE_FileListFolder ( $folderPath; BE_FileTypeFile; False; True; False)]

At this point, the $filePaths variable should contain a return-separated list of full POSIX paths to the files in the selected folder. You can test this by inserting a step:

Show Custom Dialog ( $filePaths )

which should display a list like:

/Volumes/BH_VFX/files_to_import/abcde.csv
/Volumes/BH_VFX/files_to_import/fghij.csv
/Volumes/BH_VFX/files_to_import/klmno.csv
...

Now your script can continue with:

Loop
    Set Variable [ $i; $i + 1 ]
    Exit Loop If [ $i > ValueCount ( $filepaths ) ]
    Set Variable [ $filepath; ConvertToFileMakerPath ( GetValue ( $filepaths ; $i ) ; PosixPath ) ]
    Import Records [ “$filepath” ] 
End Loop

(Of course, you need to set up the Import Records[] step so that the correct data is routed to the correct fields in the correct table.)

 

37 minutes ago, Smidge500 said:

in your script example above, you use $filepath & $filepaths, is that intentional? 

Definitely yes. You can use your own names, if you find it confusing.

 

 

[Smidge500]

Great thank you ! 

Most of the steps are running now. My only issue is the loop won't move to the next iteration. It keeps importing the first .csv over and over and again. 

I also couldn't get the ConverToFileMakerPath working with the GetValue addition, so I removed that and it works. 

 

 

 

 

[comment]

49 minutes ago, Smidge500 said:

is the loop won't move to the next iteration

Well surely it does, if it keeps doing [anything] over and over ... It just won't move on to the next file because you are not telling it to pick the next value from the variable containing the file paths. That's what the expression:

GetValue ( $filepaths ; $i )

does in my code.

 

55 minutes ago, Smidge500 said:

I also couldn't get the ConverToFileMakerPath working

That's not a useful description. You should say what happened when you tried it. Use the Script Debugger and Data Viewer to establish exactly what is happening. Or at least my poor man's version using Show Custom Dialog.

 

1 hour ago, Smidge500 said:

so I removed that and it works. 

Except it doesn't.

 

[Smidge500]

 

I realised I had a typo in there, so all working now. Thanks for your help!