I opened the file in excel and you are correct it looks correct. However if you open it notepad and there is a huge amount of space between the Text Explanation and the 3 codes.
You need to contact the source and get them to clean up the explanation field
yes the space character paddding was evident in TextWrangler.
Source is government, so the chance of getting any productive result out of them is zero.
FM should be able to handle importing extra space, the test file is only small. Perhaps to be some other hidden character issue, or encoding.
If all else fail ( assuming there is some all else), may have to resort to trying to source the xml exquivalents and deconstruct that, but a very long way around what should be a simple open from excel format
I took a different approach. I created a database by dropping the text file on FM 13. I then manually imported the data from the text file and it imported correctly. all the fields have data.
Maybe it didn't hang? I took 3 seconds to import 22 records so i would guess awhile to import 26 Megs.
yes, I had tried that too. It gives the appearance of escaping the hang, but try clicking into a field; it trips another cpu 100%/hang, even after severeal minutes.
Each time the beach ball disaapears, and activity monitor cpu graph returns to baseline, click the cursor into a field, and another cpu saturuation/hang cycle occurs.
The spaces are revoved by the direct open process though.
I'm not finding that issue. I have imported the 22 records a few times and there is no problem clicking into the fields. I am on windows so maybe that's the reason?
I am noticing a bit more cpu usage when clicking in f2. bounces from 3% to maybe 30% but doesn't hang.
As a test i did a replace Trim ( f2 ) and now when i click into the field no cpu change happens.
Maybe you should set the auto-enter for explanation field to Trim() and have that run on import?
I was hoping to get to that point (trim) but couldn't.
can't set auto enter at initial open the text into FM, as the fields do not exist
however, can create a second empty processor FM file, with Trim(self) on f1 and f2 fields, and import the hanging fm file into the second processor.
1. text file ( src.txt)
2. open txt file into fm, wait to get past hang and close and save as fm_a
3. create fm_b, with fields to match fm_a, but as auto enter calcs trim(self)
4. import fm_a into fm_b
screen shots of the cpu activity during hang attached
I don't understand why it would require all those steps. Can't you setup a temp import table with the trim () then import the file then transfer the records to the main system?
I don't understand why it would require all those steps
neither do I, should just be able to open the text into fm, and replace trim the fields, then import that into solution table
Can't you setup a temp import table with the trim () then import the file then transfer the records to the main system?
that is basically what I am doing, but again, one can not open the source text file into FM and trim at the same time, as the fields are not defined. Hence 2 stage/file process
text fille >> fm_a >> fm_b
once fm_b is clean (trimmed at import of a), import the scrubbed data into the solution
re my prior post, the scub woeks nicely on teh test limited recor file, having just done the prcessed the full text source, fm_b still hangs, although not so extended.
fm_b is only 2.8MB, with 1522 records, and 2 fields, yest it still hangs!
I removed the f2 (note) field from the layout, and no more hangs. So that open up possibilities for scrubbing with a loop script
but all this should not be necesary, it is a tiny file with a small number of records
Has to be something in the text encoding (?) or residual invisible characters...
trim did not remove the hidden (space) characters
calc field to show the length of the note field (f2)
f2 text displayed = Hypocalcaemia
length f2 = 16368
Interestingly, removing the f2 (note) field from the layout , removes the trip to hang. So that's a hint.
still the space characters?
omit one record, and show omitted
replace f2 - Substitute( f2 ; " " ; "#" )
so the field is as per atatched ss_1 filled with hash marks
on another record, ( omit and show omitted so speed is practical)
replace f2 = Trim( f2)
then test the effectiveness of trimm with the same prior hash substitution a, and trim has not removed the spaces
So, there may be a last character , other than a space character, taht is preventing Trim function from removing trailing spaces ( as techincally there are none, theer are intervening spaces); maybe there is a pilcrow last character
and indeed that is what there is, as replace f2 = Substitute( f2 ; "¶" ; "#" ) illustrates; a clocing last character pilcrow; so no trailing spaces to trim, instead space padding closed by a pilcrow.
ss-1.png 22.4 K
Finally a fix
autoenter trim at import is not effective as the last character in many fields is a pilcrow
the calc is not effective as an auto enter calc; it produces empty fields at import
what works is
open the source text file into FM, wait for the hangs to subside (form view seems to work better, rather than table - less to display)
click into a field of short length ( so as to avoid triggereing another hang)
and replace the note field (f2) as below
FLD = f2;
L = Length (FLD) ;
LAST = Right( f2; 1)
Trim( If( LAST = ¶ ; Left( FLD ; L-1) ; FLD ) )
There aren't any ¶ chars in the test.txt file.
I did a fresh import from test.txt. Length ( f2 ) = 17,040
Then I did a Trim ( f2 ) replace and Length ( f2 ) returned the actual length of the remaining text.
I would use Trim ( Substitute ( f2 ; ¶ ; "" ))
There aren't any ¶ chars in the test.txt file.
yes there are, but they are invisible, or there would not be any rows, it would all be run on text in a single paragraph
copy and pasting test.txt into LibreOffice , and show invisibles (img_1)
I did similar re length calc to check earlier.
I also did a Substitute( f2 ; "¶" ; "#" ) and the characters were clearly there
having just done another direct open of the full text into FM, and then a trim, that worked as you suggested. So it must have been a grubby intermediate amongst the myriad of testing
Anyway, resolved at last.
thanks for the input