FileMaker 5 - bulk-imported text not searchable

[Jonathan Hollow]

I have done a bulk import of a text file to a Filemaker Pro 5 database (about 400 records). I am now running into a couple of problems manipulating the data. An important body of text has gone into a "comments" field in every record, and in this comments I field I included various searchable codes so that I could find the imported data records and do some processing on them in FM Pro. The text in the comments field appears perfectly, but when I search for codes and simple words that I can see in the comments field, FileMaker returns "text not found". The text is visible, but somehow because it's imported it's not indexed, or searchable, or something. I have tried reimporting from a BBedit file which I saved as a Unix text file - in case the format was the problem. No difference. I also tried reexporting the text from FileMaker and back in again - the same problem resulted. Has anyone any suggestions?

[IdealData]

The field must have INDEXING SWITCHED ON.

Check your field definition.

[Jonathan Hollow]

Thanks. I did check this - under Field Definitions, it is on, and so presumably was when I imported. Under options I tried switching it off and on again, but it still finds none of the records that have been imported - I have checked my search text and I am definitely entering the correct text!

[Oldfogey]

I'd be amazed if the fact that the data is imported has anything to do with your problem.

Where did the data come from? What format is it in? What do you mean by 'bulk' import? Can you give us a sample of the codes you entered?

Have you tried keying in a copy of one of your comments and searching for that?

Also try a cut and paste.

[Jonathan Hollow]

I was very surprised too, but it is definitely only the imported data that is displaying these properties.

The original source of the data was structured customer enquiry emails in Mac Mail. I then saved a large number to an RTF file, and in word and BBedit, carried out find and replaces to get them in a state ready to export. The result was a text only file in which each new email was separated by a carriage return and each field within the record was separated by a tab.

When I imported this into FileMaker using the import function, it added 400 records in one swoop - this is what I mean by bulk import. I did all the field matching required.

The reason why I know that it is to do with the import is that I can do text searches of the "comments" field in each record that I added manually (about 1600 in all). In each of the imported comments fields, I added xxyxxx as a code I could use to find all the imported records, as there are certain things I must do to tidy the imported records up.

If I add xxyxxx to the comments field of one of the old 1600 records, a text find picks out that record, not one of the 400 new imported ones. If add the same to one of the imported ones, that imported record is still not found.

[Lee Smith]

Why are you saving in RTF instead of Plain Text?

I'm not sure I'm seeing the reason for your problem. From your description, I haven't had this happen to me. If you are sure the data is at fault, I would change the format of saving the file to plain text, and see if that makes any difference. If you can, test this on a different Machine, or Platform, see if your files act differently.

Unfortunately, it sounds like all of the data is confidential, so it probably would be hard for you to post your files involved. However, maybe can post a clone - No Record copy of the file, and a few mockups of the data you are working with, and see if we can figure out anything for you.

HTH

Lee

[Oldfogey]

OK, Jonathan. I'll crawl back under my stone. :sorry:

[Jonathan Hollow]

It's a generous offer to look at a clone - it will take me a little while to prepare, but I will do so and upload it. Please look back over the next day or so!

[Lee Smith]

Just REPLY to your post.

I have a family emergency that came up over night, but I will look for it when I can.

Lee

[Jonathan Hollow]

Here are the dummy files:

- a clone of my database

- a clone of the text file that I have been trying to upload. It's structurally identical but I have edited the data to protect individuals' privacy; and there are just two or three records

- a PDF showing the correct ordering of fields being imported from the text file to fields in the database

I have put two records manually into the database. You'll see that when you search in the comments field for "textyoucanfind2", only one record gets found. However, if you import the text file and search for xxyxxx, which is in the comments field of all imported records, the text is not found.

This is the essence of the problem.

Import_relationships.zip

[Lee Smith]

If I'm understanding you correctly, I did NOT have the problem you described.

Two things though.

The file in Finder had a funny Icon, and when I double click on it, was not recognized as a FileMaker File, but did open with a warning using FM v6. The warning said something to the effect that there was enough information on the file, and gave three choices. Cancel, Update, Continue (I think that was what it said), anyway it was the one I choose. The file opened okay then.

The import was of course effected, so All I did was line up the Email, and the Further Comments, and imported. The Find for

Edited July 9, 2006 by Guest

[Jonathan Hollow]

Looks like your comments were cut off there, Lee - I would like to see the rest!

[Lee Smith]

You were right. I have added in the missing text.

Lee

[LaRetta]

Hi Jonathan,

Both your fp5 file and the text document were read-only. After corrected, they opened fine. But no matter what import method I tried and no matter what extension I used, I couldn't even get a map to it. Maybe it's because I'm using 6.0v3. Here's my results. Sorry I couldn't help you isolate your problem.

[Lee Smith]

H LaRetta,

I used the Tab Separated Text Format for Import, and that was another thing that was chopped off of my post.

Looking at your screen shot, I didn't see that data in the text that I imported.

.

Edited July 9, 2006 by Guest
removed PDF

[Jonathan Hollow]

Not sure what to say here - I still can't see the end of your post, Lee, but I guess the gist of is is that you could search the text you imported?

[Lee Smith]

The gist of it was:

The two finds that didn't work for you, worked for me after import.

Lee

[Jonathan Hollow]

And what version of FM are you using? Maybe I just need to upgrade ... I have had no need until now, though.

[LaRetta]

Yes, my first record in the map for import matches yours, Lee. But it continues to put a pipe between each character as I displayed. Interesting. I again selected tab; same results. I even tried each of the character sets but it made no difference. I have an old vs. 5.5 around here ... maybe I'll give that a go if I get some time ...

[Lee Smith]

I didn't pick up on the version 5.0.0 until just now. IMHO, 5.0 was unstable, and causing my files to be corrupted on several occasions when it Freezes while processing. If FileMaker crashes, or if you have to force quit it, it can corrupt your file. If you have ever had to Run the Recovery on this file to get it to open, then there is a good chance that this file may be.

Once I upgraded to 5.0.3 a lot of problems went away.

Your file acted like it might be corrupted when I opened it.

Have you ever had to use Recover to get it to open? I ran MetadataMagic on the file you posted, and shows that the file was not closed properly 1 time. Anyway, that is about all I can see without sitting at you desk.

HTH

Lee