Jump to content

Import of strangly formatted records


This topic is 7871 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hello everyone,

I need to import many records that look like this:











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!!!


Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 7871 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.