Import of strangly formatted records

August 30, 200124 yr

Hello everyone,

I need to import many records that look like this:

Record123

Fieldname1[spaces]fieldcontents

Fieldname2[spaces]fieldcontents

Fieldname3[spaces]fieldcontents

Record124

Fieldname1[spaces]fieldcontents

Fieldname2[spaces]fieldcontents

Fieldname3[spaces]fieldcontents

...

using FM5.0 latest update.

May anyone suggest how to do this? Plug-in, reformat in whatever program? I have only access to the data.

Thank you in advance!!!

Daniel

August 30, 200124 yr

Start by replacing the [space] with a [tab] or [comma], so that Filemaker will understand where the seperators are. Use Excel or any text editor with the replace capability. If using Excel, simply open the file using the [space] as the seperator, then save as a CSV file.

Then import into Filemaker, you will now have data in 2 fields (Field1 and Field2). Field1 will store both the record number as well as the actual field names, which will end up not being used for anything; Field2 will store the actual field contents. Add 6 calculated fields, as well as a serial number field. The serial number field should simply be sequential serial number.

3 of the calc fields will be used to tell you what Filemaker record has the correct field data. The other 3 calc fields will actually get that data via relationships.

Start with the 3 calcs that tell you where the data is located:

calc1 = SerialNumber + 1

calc2 = SerialNumber + 2

calc3 = SerialNumber + 3

Now create 3 relationships:

rel1 = calc1::SerialNumber

rel2 = calc2::SerialNumber

rel3 = calc3::SerialNumber

Finally the 3 calcs that actually get the data:

Field3 = if (PatternCount ( Field1, "Record"), rel1::Field2, "")

Field4 = if (PatternCount ( Field1, "Record"), rel2::Field2, "")

Field5 = if (PatternCount ( Field1, "Record"), rel3::Field2, "")

What is happening here is that if the record is identified as an actual "data" record (via the PatternCount), then we know that the next 3 records contain the actual data elements for this record, and we are using the relationships to pull that data into the correct fields.

Finally you will need to perform a find on Field1 for "record", then export Field1 (record number), Field3, Field4 and Field5. This will export only the "data" records, and will export the record number as well as you 3 data fields.

Complex? Yes, but once setup it will handle any number of records.