How to parse information from a text file into separate records in File Maker 5.5?

I need to know the max amount of people per company. Fm has a limit for multivalued fields, so you may need to create a person based table instead of company based, or create 2 tables linked together by the company name.
On top, depending on what you need to do afterwards with this base, you have to know that multivalue fields are heavy to handle: for instance the value order cannot be a variable in a script, which limits automatic actions on them.
So far, I've done an example of a table based on persons which you can see there:
http://extranet.valblor.com/~lesouef/import.fp5
It is based on the list I pasted in notepad: http://extranet.valblor.com/~lesouef/import.txt
Use it in search mode, you'll see that even based on persons instead of companies, the FM pop list makes it easy to use (depends how many companies you end up with).
If it is too heavy, I'll work on a 2 tables solution.

Hi Lesouef, Thanks for your help.  I'll clarify a few things to make it easier,

Records based on company name is pretty much a requirement, I'm really sorry
I can not do it based on person.

I quickly read thru the file, there are probably no more than 20 person / company,
so I guess we can assume 20 will be the max.  

If some kind of additional formating is required, I would be happy to do it,
as long as it doesn't involve manually enter 80+ page of data into filemaker  :))

I would like all the fields to be text fields, if possible.  Like all the person+email
in a text box.  I mean put everything into simple text boxes if possible.
The database should look something like this:
http://www.songfeng.com/downloads/sampledb.jpg


Thanks for helping me out!!

OK, you don't want to split personal_info, easy, I just have to remove some cleaning scripts.
For the list of persons, do you want a multivalueed field or a simple list in a text field? (yr screen capture looks like this).
I won't work on it before tonight at the very best...

Hi, Lesouef.  Yeah, I only want the contacts in simple text field,
actually just about everything should be in simple text field :)
they don't have to be a multivalueed field.  

Thanks!

OHH! thanks, I can't wait to try it.  I'll try it tonight when I get home.  :)

I'll see that too-morrow then! (I live on GMT+1)

Hi, Lesouef.  Your script worked really well, I'm really happy! :)
But, there is one more thing I wanted to ask you.

Asssuming we have: A Database(the one we created) and B Database(my other existing database)
How do I write a script to import records from A database into B Database and it should check if
company name already exists in B Database.   If company name already exists do not import.  
Otherwise import.  Thanks!

First create a link from A to B using the company name
then in A create a new field called for instance
AlreadyExist = if (linkB::companyname=companyname;"yes";"no")
Still in A, search all records where AlreadyExist="no"
Go to B, import records from A
A good thing to remember, when you import from another FM base, it takes the found set, searching manually what you want to import in the source file is enough to import records selectively; on the other hand, do not forget to search all records in the source file before importing if you'd likle to import everything.

wow, cool, thanks! it worked very well, I really appreciate all your help,
Thanks again!

I just finished a project with similiarities- parsing raw data and making sense of it in a database. My solution was to use THREE FileMaker files:
bParser- batch process into individual records
aParser- process record into recognizable fields
Final- the final database

Analysis
--------
1. You know that individual subrecords (like your list of names) would be less than 20.
2. I had a number of delimiters to deal with, but you have just NLs (paragraph markers), blanks, and a comma.
3. Your records appear to be separated by double paragraph markers.
4. You know that your names list starts with a number, then a blank, then a name, a comma, and finally a mailto URL.

The key to the solution is using calculated fields to scan for delimiters and plug field values where they are expected.

bParser
-------
1. Be sure your raw data ends with two paragraph markers.
2. Create a handful of essential fields:
- RawData: The gluttonous field that will contain your raw text.
- aRecord: Text field to hold one record at a time.
- RecCount: A numeric calculation field with this formula will tell you how many records must be processed:
PatternCount(RawData, "¶¶")
- i: Numeric loop counter.
- p: a numeric field that will serve as a lexical scan pointer, moving from delimiter to delimiter. It's initially set to 1.
- q: another numeric pointer field which will point to the delimiter AFTER the one pointed to by p.
In operation, p and q will bookend one complete record at a time.
3. Create a Batch Process script that loops through RawData, breaking out individual records. Roughly the script algorithm is:
LOOP
   SET FIELD ["q", "Position(RawData,"¶¶",p+2,1"]
   SET FIELD ["aRecord", "MIDDLE(RawData,p,q-p"]
   COPY [Select,"aRecord"]
   PERFORM SCRIPT [Subscripts,External: "aParser.fm5"]
   SET FIELD ["p", "q+2"]
   SET FIELD ["i", "i+1"]
   EXIT LOOP IF ["i > RecCount"]
END LOOP
p and q point to the double paragraph delimiters surrounding an individual record and suck it into aRecord. The loop then calls a script in aParser.

aParser
-------
The important things to remember about aParser is that (a) it never contains more than one record at a time and (b) all its intelligence is located in calculated fields.
1. Create a number of fields, mostly delimiter pointers, fields for the breakout of names and mailto URLs, and a field that contains the raw record data:
a. aRecord: the text delivered up from bParser.
b Individual line calculated fields with a formula similar to the following that pops out the 14th name/URL info:
user14: Middle(aRecord,Position(aRecord,"¶",2, 14 )+1,Position(aRecord,"      ",2, 14 )-Position(aRecord,"¶",2, 14 )-1)
2. Within those 20 fields, you can snatch out more specific fields using the comma as one delimiter. With the LEFTWORDS, MIDDLEWORDS, RIGHTWORDS functions, you can populate your name, number and mailto URLs. Be just a little careful when using RIGHTWORDS to pick up the URL- It breaks on more than just white space, so you may find that you have to pick up 2 or even 3 of the rightmost words to construct the URL.
3. Done correctly, you'll have one intact record, albeit with uneditable calculated fields. You also have raw data and work fields (like aRecord and user01-user20) that you don't want anymore.
4. The script couldn't be simpler. Remember, it's triggered by the loop from bParser:
   DELETE ALL RECORDS [No Dialog]
   NEW RECORD
   PASTE [Select, No Style,"aRecord"]
   PERFORM SCRIPT [Subscripts,External: "Final.fm5"]

Final
-----
1. Define your required fields and do one experimental import to get your fields aligned with the Final database and you're ready for the script.
2.  It's script is simple too:
   IMPORT RECORDS [Restore, No Dialog, "aParser.fm5"]
That's it.

Summary
-------
   The reason I used 3 FM databases instead of two was that I didn't want unnecessary calc fields hanging around, and I used bParser for crude processing and then aParser for parsing with more detail and finesse.
   The technique has proved remarkable robust. I recently used the same technique to process a batch of raw URLs into a database were keywords and their values (&userid=BARNEY) could be picked apart and stored.

 

lesouef,
you say FM has a limitation to multivalued fields?  what limitation is there?