Inconsistent Import Results

[LaRetta]

I am attempting to import a text file. Sometimes the import works and sometimes not, ie, it will skip a record that actually matches.

Match fields:

Left: TransNo (text). It contains one of two numbers (InvoiceNumber or PONumber).

Right: TransNo (text). Auto-Enter (Replace) by calculation of: InvoiceNo &

[Fenton]

This is somewhat distressing. It may be that there is some kind of bug that is only kicking in with your older file; because you say it works correctly with test files. I still see bugs in converted files, which never appear in new files (such as deleting related records freezing FileMaker).

Would it be possible to do these 2 imports separately? Is there something in the ID of each that distinguishes it from the other; so that one import will not overlap the other?

[LaRetta]

Hi Fenton,

Oh, distressing indeed. This import is critical - pulling from web.

Well, this current design copy is from scratch in 7 and has never had any problems (well, it has but I've grabbed backup copies to continue). I've been working on this version for almost a week so I've backed up (possibly) a trashed version (15 times). Going backwards now will be painful.

I'm using the same .txt file to test-import into my real design and also the newly created test file. I've verified the fields match perfectly (data type). Just tried it again. The same record breaks in my real design and works in the test file. Out of 300 records imported, 3-4 will break at random. Oh. Not good.

Well, unless someone has any other ideas, I think the prognosis is grim - something is trashing in my design. I will try to put aside a decision until I sleep on it and then review it thorougly again tomorrow and then test each backup backwards until I find a version that doesn't break. Thanks for helping me, Fenton.

And this sure points again to the value of the separation model. I have 42 tables in this one.

LaRetta

[geod]

I too was having problems with this. You might try opening the text file directly into FM, ie. Open File, select comma or tab seperated text, then point to your txt file and open it. It should put you directly in a Table View of the data and then you can see what the data looks like. If OK then try importing from this File (Table) into the Table where you want it. In other words you might need an intermediary File (Table). It might work !

Good Luck

Graham

[Fenton]

geod's idea may help. Another idea occurs to me. You said "pulling from the web", what exactly does this mean? Are there perhaps hidden characters in the text file? I often open data copied straight from the web, and find odd characters, non-breaking spaces, etc., which can cause problems if you don't see them, which you often don't. A hidden character in the key field is going to break the match, especially in 7. I would recommend getting hold of a real text editor, turning on "view invisibles, including spaces," and see what you've got. I don't really know of one for PCs, EditPadPro?

[LaRetta]

Graham, Fenton - thank you so much.

The reason I doubted invalid characters is because an import of the identical .txt file works in the test file. If the problem stemmed from the import file, it would have behaved the same in both.

Going backwards in time, the last backup (from last night) also breaks. But the prior backup doesn't - nor the one before that!! I repeated it three times - works perfectly. My system did freeze up last night (about that time). Design was opened stand-alone but didn't appear to have a problem when re-opened (except it performed it's scanning routine).

I'm actually considering trashing a design if it's ever closed improperly. Is this overkill? I thought that, unless being hosted at the time, a file would remain sound after a crash. Does it make a difference whether I am in Layout mode at the time? Or in Define Fields? I want to be as safe as possible without being paranoid. Your personal feelings on this would be most appreciated.

LaRetta

[Fenton]

I'm glad that you were able to find a backup that worked. And you're right, the text file couldn't really been at fault, or it would have never worked; I was clutching at straws. Though I would still want to look at any text file from an untrusted source for hidden characters before importing into FileMaker.

Here is an email I saved from another list, from a long-time FileMaker engineer, Jimmy Jones, which pretty clearly lays down the law about using crashed files:

Background:

I was the FMI technical support person responsible for getting corrupted files repaired back in the FMP 3/4/5 era. I have seen literally hundreds of files that were corrupted in more ways than I can count.

Please believe me when I say files do get corrupted and in ways unidentifiable by the user. (No surprise there because FMP doesn't have any tools to show corruption). The primary way a served file exhibits most forms of corruption is as an instability in serving the file. In other words the file unexpectedly closes. The second most common is the file won't stay open on a client's computer.

There are many forms of corruption. Some may sound like they shouldn't be a problem, for example a font reference to a non-existent font. But this belief is wrong. A bad reference to a font can cause a whole layout to become unusable because it closes the file whenever that layout is used. Text in a field that has a bad font reference can cause that record to become inaccessible because the file closes every time the data of that record is displayed.

Using a file that is improperly closed or that has been recovered is taking a chance with the customer's data. I cannot in good conscience use a file that could result in loss of data and possibly thousands of dollars in recovery costs, if it is even possible. Having had to do this with a corrupted file on a user's system I know it can cost that much in labor and lost productivity.

[LaRetta]

Wow. I appreciate that. I've had files refuse to open; files that open with layouts wonking out; and files which crashed when being hosted. In each case, I've used a backup and have never used the file again. I assumed (wrongly it seems) that a simple loss of power (as in 3-finger-salute) wouldn't damage it (unless being hosted).

This was an inexpensive lesson indeed. I will never again trust a file which has closed improperly. I never would have put 2 + 2 together (my lockup and a wonky import) if not for your opening response. And, if this hadn't been newly implemented (start of last week) and worked when first tested but now didn't, I probably wouldn't have recognized the problem for quite some time.

God, I love inexpensive lessons!

LaRetta

[LaRetta]

Considering ways to resolve lockups and crashes ...

Time to get a UPS for my box at home. Queue pointed me to that for our host at work, but having one at home would cut down on 10% of the problem (power loss).

I also overload my home system quite frequently (15 programs open simultaneously, etc) and need to restart. I guess I shouldn't be in design when doing 25 other things as well. Too costly.