Import records from .txt file in a container field???

[MacHammer]

Hi,

I'm building a DB where I hope to be able to have a user drag a .txt file into a container field and then click a button to have data imported from the file now held in the container. The field holds the data without problems, and if I doubleclick the file in the container, it opens as text, and would I could find the proper link to make it work, I think I might be able to import it.

Have any of you wonderful people solved a problem of this nature? How did you do it?

Or, the main problem I'm trying to solve is to provide users with a simple way of importing data from a file whose name changes every day. I need to do a daily import of a text file that will always reside in the same location, but will have a different run number as part of its file name. I've been able to automate another aspect of this process, because the file name never changes and it is a very simple import. The new process uses a simple import, but with changing filenames, so I can't just point to the file and import away. So, my idea is the container field. If you have a better solution, I welcome your ideas.

Thanks in advance for any help and direction you might provide.

Mac Hammer

[comment]

I am not able to drag and drop a text file into a container, so I cannot test this, but if you have a file in a container, and it opens without exporting first, the path to the file should be available under GetAsText ( Containerfield ). If you parse out the path and use it to define a variable, you should be able to import the file.

Regarding your main question, I believe Applescript may be your best friend here.

[Fenton]

Strange but true. If you open a text file and select its contents, you can drag it to a container field; where it's still just text (it seems). But I can't drag a "file" to a field either. And I don't see why you do the above, as you could just drag it to a text field at that point.

I can think of a few ways to automate getting text files from a folder. It depends on which files you want, and how many, and what you plan to do with them later, etc.. AppleScript (or the Troi File plug-in) can fairly easily do all that, once you know what the logic of the process is; which we don't at this point.

You can also use Import Folder, Text files; you can use the Matching option on the file names or paths to prevent duplication. This matching requires you to Show All Records, hence can get pretty slow with thousands of entries. But most other comprehensive synchronization is not going to be fast either. The best option is to move the darn files when you're done with them; which AppleScript can easily do.

I would usually say "upgrade to 8" to deal with files. But text files are so simple. You can even read them directly with AppleScript, then set a field, so you don't really need to import.

If they want to just drop a text file(s) from any old place, then you could use an AppleScript "droplet," which you could put on their Desktop. It would read the text, then set the contents into a FileMaker field in the current record. Enclosed is a simple example, with some, but not much error checking (it doesn't really look for a partcular FileMaker file or table, so you'd have to have the right one open). It's just to give an idea.

I used the shell "cat" command instead of plain AppleScript to read the file. It seemed to do better with the various encoding we see lately.

Text_Droplet.zip

[MacHammer]

Hmmmmm. Thinking...

More about the process:

We are importing Tab-delimited data files. The files are generated by another process and get named by that process (not FMP). We are trying to use FMP to manage the overall process of tracking requests through the multi-phased system. So, we import data from various points in the process and then relate it together to run reports on where individual pieces are in the process at that time. So, we get text files that are originally named with a .out extension that we have to manually rename to .txt. Then, we have to import the .txt files (at this point, manually) by using the File->Import commands. Then, we have to nav to the file and open it, set our options and perform the import. There is ONE part of the process where the file gets named the same thing each day, so I've been able to script that import and it works great. I just haven't been able to contend with the import of a uniquely-named file, each day.

Would it be better to use the Import Folder command and then build some logic into what actually gets imported? That might be beyond me, as well.

Also, we are using FMP 8 and 8.5, but the primary of this system is one of our Win systems, so Applescript isn't going to help. Sorry.

Thanks for the ideas. I appreciate the support.

Mac Hammer

[Fenton]

No, Import Folder is for importing TEXT files, not TAB-separated files. These are 2 entirely different things; the latter is for putting discrete data into separate fields, 1 per tab, in separate records, 1 per line; whereas the former just puts the whole text field into 1 field in 1 record; very different result. That's what I assumed, from the way you first described it.

You probably should look at the Troi File plug-in (or learn some VB). It can get a list of files in a folder. Then you can decide what to do with them. Also, you probably should upgrade to FileMaker 8. Or do the file name "switcheroo." You will possibly find these techniques here. I know I've explained it a few times; though usually with AppleScript as the tool. The logic is the same whatever tool you use.

[Mikhail Edoshin]

Why not to ask the user to select the file when the import script runs?

[MacHammer]

Thank you. I'm not sure why everyone is operating under the impression that I don't have FMP 8? We are running a mix of 8 and 8.5. The mix is only because we are in the midst of a huge project and some people just can't give me their systems long enough to handle their upgrades. That, and they can't risk losing FMP should there be a fledgling bug.

Mac

[comment]

Who's everyone? I wouldn't have mentioned variables if it wasn't v.8.

Geting back to your process: it's not clear how the file gets named. If you can predict today's file name, then it should be very simple to do competely inside Filemaker. If not, then I don't see how ANY process could pick the correct file - unless it's the only file in the directory (or there is a list of files already imported?). In any case, Filemaker alone will not be able handle this.

[MacHammer]

For now, I'm going with the method of making the user pick the file at the time of import. I actually didn't remember that I could do this, being used to performing the import desired in the script and then using the settings from that import as part of my script. I forgot that if I didn't do an import or didn't specify, it would ask for a file to import. This will work until I can build in some other automation that will either make today's file the only one available, or can figure out a better method (with your help, of course!)

Thanks again,

Mac

[Fenton]

Oops, that would me; I don't know why I didn't see that you'd said 8, it's plain. Here's another idea. If you have the file names of previous imports, and you only have files for import (or already imported) in the folder, then you can use Import Folder to get the new file name and path.

Set up an Import Folder, with the Matching Records option, with Import New Records. Match on the File Name. That should be unique. You can import only the File Name and File Path; you don't have to import the Content. You will have to Show All Records (or at least those with previous file names).

That will create a new record(s) for any new files in the folder; they would be the records with a file name and path, but no other field data. You can add the "file:" prefix to the file path to get a relative path to the file, put it in a script Variable, and use that in the regular Import dialog.

That would work, but only if you have previous file names in your database (or if you manually clear out previous files from the folder). It is not the fastest method however. The fastest is to use either OS tools or a plug-in to move the imported file to another folder afterwards, so that only a new file(s) would be in that folder.

[comment]

Match on the File Name, during folder import - neat idea!