Importing Data Into Filemaker from a Formatted Text (TXT) File

[Otis]

Hi all.

I need help. I am a FileMaker Developer and I need to create a script to import data into filemaker from a text (TXT) file.

Here's the rub: the data is being exported from some ANCIENT pc-based application called DataCenter Plus, and it only exports data into a nice neatly formatted txt file. That's great for the final text file, but it's not ideal for importing raw data. (There is tab or comma-separated exporting option, etc.) This is the ONLY way I can get data out of DataCenter Plus. There's no other way to export data from DataCenter Plus except for in this formatted text file.

DataCenter Plus can export data as a single record, or as multiple records. When it exports data from multiple records, it just adds a "_____" at the end to show the end of the record. I'd like to have my script automatically know if this is one record or multiple records, just by looking for the "_____" at the end.

Thus, in FileMaker, I don't know how to import these lines of text and properly tell FileMaker which text is a field name, which text is data for the fields, and which lines of text I can group together as an entire paragraph for a "notes" field in FileMaker, etc.

Again, I just want to create a script to import this data and avoid any confusion, but without comma or tab separated text, I'm a bit confused how to make this happen.

I have attached a simple text file with two records which were exported from DataCenter Plus. You can get an idea which text is the field name and which is the data by looking at the formatted file. You can also see which formatting patterns are consistent over multiple records, etc.

Any help is appreciated, thanks.

Workout_Data.txt

Edited August 28, 2007 by Guest

[Fenton]

What does the tab or comma-separated look like? Because this one is going to need a lot of parsing work. Please zip the file, so the browser doesn't mess with it.

I don't know why, but in BBEdit it is mostly old-style carriage returns, but there are a very few Unix line feeds also. Which is odd. In between these is a line feed, not a return (Safari translated it):

Average Incline

4 sets Curls

You might want to open it in BBEdit if you have it; or get its little brother TextWrangler; free, from:

http://www.barebones.com/products/textwrangler/index.shtml

[Otis]

Thanks for your reply.

I opened it in BBEdit and Zipped the file.

Please let me know if this looks a bit better.

I'm anticipating a bunch of parsing that will need to take place in order to get it "clean" for use with FileMaker, but I just need some pointers to get set in the right direction. Thanks.

Workout_Data2_txt.zip

[Fenton]

BBdit could do some preliminary clean-up of the file. This could also be done in FileMaker, but BBedit's grep can do it all at once, and likely faster. You might as well, because you've got quite a bit to do still in FileMaker.

I just recorded one Search/Replace as an AppleScript, then duplicated it for the others. This can be saved as an AppleScript and put in BBEdit's Scripts folder. Or it can be run from FileMaker, in a Perform AppleScript step. Though, do so separate from the import, or call the Import; because you may have timing issues otherwise. (I'll explain better mañana.) Most of the extra stuff are just default options BBEdit writes into recorded AppleScripts; might as well leave 'em in. The main Search/Replace is right at the beginning:

tell application "BBEdit"

	activate

	-- replace line feeds with returns

	replace "n" using "r" searching in text 1 of text document 1 options {search mode:grep, starting at top:true, wrap around:false, backwards:false, case sensitive:false, match words:false, extend selection:false}

	-- remove extra spaces at the beginning or end of lines

	replace "^ +| +$" using "" searching in text 1 of text document 1 options {search mode:grep, starting at top:true, wrap around:false, backwards:false, case sensitive:false, match words:false, extend selection:false}

	-- remove the extra return after Categories (bring up the hanging category on the next line; could use a tab t instead of a bunch of spaces; but that's what the others are)

	replace "(Categories    :.*)®([A-Za-z])" using "1              3" searching in text 1 of text document 1 options {search mode:grep, starting at top:true, wrap around:false, backwards:false, case sensitive:false, match words:false, extend selection:false}

end tell

[Otis]

Fenton, thanks for your reply.

I'm at work now, but I'll take a peek when I get home and go over things.

For me, I'd like to do everything in FileMaker, even if it requires a plug-in, not BBEdit, since the machine running this may not have BBEdit at the time, etc.

Thanks again for your reply, and I'll take a peek when I get home.

Edited August 29, 2007 by Guest