Parsing Help

[tom978]

I'm trying to come to grips with parsing comma delimited text in a global field to populate a record. I understand the basic concept - locate the marker (,) and extract the text after it and place it in the proper field (at least I THINK that's the general idea).

My Question - the data to be parsed will vary in the number of items to store. The fields will always be the same; section, Fname, Lname, and School but there will be from one to 20 records in each. How do I provide for that in my parsing script?

Also, if anyone can point me towards a tutorial in parsing text I'd appreciate it.

Thanks

Tom Street

[RalphL]

You might be better off using a text editor to replace every 4th comma with a return. Then import that into your database.

[Lee Smith]

The answer to your last question,

if anyone can point me towards a tutorial in parsing text I'd appreciate it.

would be there isn't one, but if you search the Forum for +pars +comma +text (or +Extract +comma +Text) and you could also search for +tab as a alternate to +comma, you will find that this topic as been discussed numerous times in the past.

As far as your specifics, it is easier for us to help you if you would attach a sample of the file.

Lee

[tom978]

Thanks for the comments. Attached is the file the form creates. It is delimited with the pipe symbol. There are a total of 80 fields on the form; sec1-20, fname1-20, lname1-20 and sch1-20. The user must enter at least one record and may enter as many as 20. What I want to be able to do is to look at that file and create a new Filemaker record for every entry in the file. An entry is sec1, fname1,lname1,sch1, etc. So, on the file attached to this message it would create a total of 20 new records each with the data on one student.

I have no problem in pulling in a single record. I just can't figure out how to repeat the process for the other entries. It isn't practical to submit students one at a time.

If I'm goin' about this the wrong way I'd appreciate a nudge.

Thanks again,

Tom Street

form_data.txt

[Lee Smith]

Please resubmit this file and zip it.

Also, the file has pipes, I would like to see what the file looks like before you touch it.

Lee

[tom978]

Here is the file as it comes from the form. I mentioned it being delimited with comma's in my original post but that was after I opened it in Excel and saved it that way. This is straight from the source.

Thanks,

Tom Street

data.zip

[Lee Smith]

Are the Field Name the first record and the data the second record?

Is this file all of the fields?

It seems pretty straight forward, and wouldn't be that hard to separate the record according to the field names.

I just replaced the the pipes | with a Tab, saved the file as text and opened it in FileMaker.

Lee

[Fenton]

Using a grep-capable text editor, I was able to parse this is 2 operations. I did it on a Mac however, so you'd have to get a Windows text editor that supports Perl syntax grep (EditPad Plus?), or do it with command line (?). Open the file as text. Delete all the field names at top. You don't need them in the data. Just create them in FileMaker; there's only 4.

Find:

(d+|[^|]+|[^|]+|[^|]+)(|)

Replace:

1r

Find:

|

Replace

t

Humanly readable explanation -]

(d+|[^|]+|[^|]+|[^|]+)(|)

1st pattern (within parenthesis): Find all the stuff on a line starting with a number (or numbers), the a pipe, then anything except a pipe until the next pipe [^|]|. Then do this again a couple times, up to the 4th pipe.

2nd pattern (2nd parenthesis): The 4th pipe

1r

Replace with the 1st pattern, and add a Return. Don't include the 2nd pattern, the 4th pipe (it's extra).

|

Find all the pipes

t

Replace with a Tab

Delete the extra return at the end. Import as tab-separated text.

| is needed, because | is a special character, meaning "or"

[^|] means "anything but |", which doesn't need a backslash escape inside of brackets; it's not a special character there.

[tom978]

Problem solved I THINK. I went a different route.In addition to saving the form data in a flat file it sends an email to the form owner with all of the data. From there it was a matter of exporting the email to a tabbed file, opening that in Filemaker, and then through trial and error I wrote scripts that pull the data out of the f1 field where it puts them all and places them in the proper fields.

Not sure if that's the most elegant solution but it works.

Thanks for all the help!

Tom Street