Converting Return-delimited text prior to import

[nfregistrar]

Is there a way to use scripting in filemaker that will do the job of converting return-delimited text to tab-delimited text proir to importing it into a filemaker database. FileMaker Pro 7 Bible (page 523) suggests using MS Word to do the job. However it would be much more useful to skip that step. I need to do it for each new piece of data or groups of data that I receive on a daily basis.

[BobWeaver]

If the file uses returns to separate fields, what does it use to separate records, a blank line (two returns)?

I've done imports like this by importing into a separate table so everything goes into one field, and then run a looping script to collect the data and put it into the correct fields in the main table.

[Lee Smith]

Hi nfregistrar,

Bob is correct, when he states that there are ways of parsing the text once it is FileMaker, and he asked a very Key Question about the Record separators.

There are a some sample files scattered around the Forums, that may be helpful, although you will have to do a Search using some of the Keywords such as Parsing (+pars) or Extracting (+Extrac) to find them. Also, Ugo contributed a Tutorial file in the Samples File Topic Area.

I agree with FileMaker Pro 7 Bible here, you should be able to do this easily in Word. However, if you are going import text that needs File Manipulation in the future, you should get one of the Text Editors that are available. Be sure and get one that can do Grep Patterns and Regular Expressions (i.e. BBedit or TextWrangler (Mac) or EditPad (Win)).

Using one of the programs (i.e. BBEdit, EditPad) and the suggestion that it might be a Double Returns separating the Records. I would approach it this way. First of all for clarity:

1 Return = r

1 Tab = t

Temp Replace Character = | (Pipe Character)

Do the following find and replaces:

Find rr & Replace with |

Find r & Replace t

Find | & Replace r

The result will be a Tab Delimitated file that is easily imported into FileMaker.

HTH

Lee

[Barbecue]

There's a nifty utility from the Unix world called sed that can be really useful for this kind of thing. Sed is a sort of text filtering program that's widely used for exactly this sort of text processing. Although it originated in the unix world, there are versions of sed for all the popular operating systems, including Windows and Mac OS.

The only tricky part of using sed is setting up the regular expression syntax for your particular case. Regular expressions are not exactly straightforward, but they are incredibly powerful, and there are plenty of examples available.

Once you've created the right instructions, you can then set up a batch file that will run the sed filter on any given file, outputting the results to a specific known filename. From there, FileMaker can import the results into a table. You can even create a FileMaker script that calls the sed command and imports the results in one swoop.

[Søren Dyhr]

Once you've created the right instructions, you can then set up a batch file that will run the sed filter on any given file,

Do you have a link to a good tutorial??

--sd

[BobWeaver]

sed is built into OSX and can be run from the terminal or using "do shell script" applescript command, however I've had some trouble with it because it doesn't seem to recognize escaped control characters. You have to enter the literal carriage returns into the search string which can make it ugly to script.

My preference is to keep everything in Filemaker, especially when this operation is being done regularly.

[nfregistrar]

I though that I had replied to this, but I don't see it anywhere.

My apologies if this is a repeat.

Thanks for all the good ideas.

My basic questions now boils down to this:

Can the find/replace in Filemaker 7 script be used to find "returns"?

If so, how does one specify a return.

[-Queue-]

[Brian C]

My personal favorite Text editor on the Mac is Tex-Edit Plus. It is much easier to use IMO for text manipulation over BBEdit.

[nfregistrar]

Thanks for you help. I think that I am almost there. I have a filemaker file that has just one field and one record. A script pastes the raw data in that field and using find and replace, it removes extraneous stuff and puts tabs between the fields in the data. There is a unique string at the beginning of each record in the raw data. The script removes the first one and puts returns at the beginning of the rest. Result, a return at the end of each record except the last one. All this takes place in that one field in that one record in Filemaker. It is then exported to a tab file.

In the main Filemaker file this script is called and then the tab file is imported. The problem is that is apparently doesn't see the returns and tries to put it all in one record. Oddly, if I manually enter returns in the tab file and import it, it acts as if it sees 2 returns. Alternately deleting the return manually in the tab file and putting it back back, it now sees the return.

:???

[Barbecue]

S

[Barbecue]

nfregistrar, I think what you're seeing is by design. Filemaker doesn't represent line breaks within a field internally the same way it does in an exported field.

The standard line terminator for a delimited text file is a Carriage Return followed by a Line Feed (In hex, 0A 0D) and that's what's still used in Windows. Mac OS used to use just the carriage return by itself, and Unix has used just the line feed by itself for years. I guess Mac OS X with its Unix underpinnings and Classic Mode probably uses some weird mix of either/or/both now, depending on context.

When I export standard FileMaker data to a tab-delimited file on my Windows machine and open the resulting file in a hex editor, I can see that the individual fields are separated by actual tab characters (hex 09) and each line ends with a CR LF combination (hex 0D 0A) as it should. I assume FileMaker on Mac Os 9 would probably use a CR alone, and I don't know what FM7 on OS X would do.

BUT...

if I take the exact same tab-and-return delimited data and put it into a single FileMaker text field, then export just that text field to a TAB file and examine it with a hex editor, things are very different. The tab characters have been converted into regular spaces, and the return characters are represented as hex 0B, which is actually the seldom-used vertical tab character. In other words, it's not putting real data separators in the line.

If you think about it, this actually makes sense, because otherwise the final structure of a tab delimited file generated by FM might not match that specified during export. If some records had tabs or returns in them and others didn't, the resulting file would basically be garbage, with different numbers of fields on some lines, and spurrious records throughout.

Of course in your case this wouldn't be a problem since you have just one field and one record in your export, but the program doesn't have any way of knowing that.

So the short story is, you can't create a tab-delimited text file by exporting a single field containing tabs. This is an inherent limitation of FileMaker, and probably a good thing too. You have to create the tab-delimited file outside of FileMaker itself.

Fortunately, there are at least three approachs to this. One is to generate the file as you have, then use a search-and-replace function in another program, either manually or automatically, to put the file in proper order. Another option is to use a plugin which allows you to read and write directly to a text file, such as the free File Toolbox plugin from ProtoLight

http://www.geocities.com/siliconvalley/network/9327/plPLUGs.html

This is the closest you will get to keeping the solution completely within FileMaker, but will require the most elaborate scripting.

There's a third approach, at least under Windows, and while it's a bit dodgy, it's probably the quickest and easiest to implement.

When you copy the contents of a text field in FileMaker to the Windows clipboard, the tabs come across as tabs, and the returns come across as returns.

Paste the clipboard into a text editor, and you get a perfectly good tab file.

So all you really need to do is use a FileMaker script to copy the contents of your finished field field, then call an external utility that will paste the text into a text editor and save it with a known filename in a known place. Then you can import the known filename with another script.

Attached is a small utility written in AutoIt 3 script that demonstrates this concept. It's pretty simplistic. Feel free to use it if you like, modify it, or throw it away as you will. This example expects all files to be placed in the My Documents folder of your computer. You can edit the script to change this if desired.

It works on my computer, but of course there is no guarantee it will work for you, use at your own risk, don't sue me, etc.

AutoIt3 is a scripting language for Windows that can automate the Windows user interface, and can do some of the sorts of things AppleScript does on the Mac.

It's free, available from http://www.hiddensoft.com

It's a great way to add extra functionality to FileMaker without plugins.

pastensave.zip