This question came up on Macintouch:
Can you import an ASCII (.txt) file into Filemaker Pro 9 that uses a dollar sign ($) as a field delimiter within a record? The file is too big to bring into Excel first (on the way to filemaker).
Unfortunately, FileMaker 9 does not change the supported import formats, and $ delimited is not on the list. On the bright side, there are several ways to deal with this sort of thing in FileMaker.
Import and Parse
You can go ahead and import the file. FileMaker will probably see each line in the file as one field value. Create a temporary text field in your table, then import the file, mapping its one line to the new field. Using a looping script, go through each record, parse the incoming text, and move it to your real fields:
Go to Record/Request/Page [First]
Loop
Set Field [My Field, GetValue(Substitute(TEMP, "$", "¶"), 3)]
# add as many Set Field steps as you need
Go to Record/Request/Page [Next, Exit after Last]
End Loop
In the above code snippet, we use this calculation to parse the incoming text:
GetValue(Substitute(TEMP, "$", "¶"), 3)
It grabs the third value from the $-delimited file. Here’s how it works:
First, it turns every “$” into a newline. So instead of this:
Sophia$Coffey$12/29/2001
You now have this:
Sophia
Coffey
12/29/2001
Then, we call upon the GetValue function to pull one line out of this multi-line bit of text.
Pre-clean the Data
If you prefer, you can clean the file up before import. Many text editors have powerful search and replace functions, and can handle files that bring Excel to its knees.
On Mac OS X, if you’re moderately comfortable on the command line, you can do this:
tr $ '\t' < yourfile.txt > thecleanfile.txt
This command will [tr]anslate every “$” into a tab, and put the new cleaned-up data in a file called thecleanfile.txt. You can then import the file as a tab-delimited file.
Note: If the original file contains tabs, they will get in the way, so you need to convert them to something else (like spaces) first.
Using these techniques, you can bring many atypical data formats into FileMaker, where they’re much easier to work with.
Geoff Coffey
7-11-2007
The TR command line sample had a typo. It was putting a Mac OS style line ending in place of $, instead of a tab. I just fixed it. Sorry ’bout that
Geoff
Othni J. Morales
8-8-2007
Thanks for the tip.
I have a similar problem a little more complicated. I am importing whole folders of text files. Those text files have lines that I want converted to records. I want to accomplish more or less the same thing, and that is, to import the content of the text file in a TEMP field and later parse it.
The only problem is that the content of that TEMP field has to be converted to N number of records and then the data (a couple of fields only) parsed to fields.
How do I start to parse the lines that are going to be records (some lines are junk) and then parse the fields?
Jesse Antunes
8-9-2007
@Othni -
Text parsing is quite challenging in filemaker. I can’t go into detail about exactly how you would do it because it can be quite different depending on the data that you want to parse. I might be able to at least point you in the right direction. Here are a couple questions for you:
What type of delimiter does the file use?
Are the lines you would like to dispose of uniform?