1 2 Previous Next 15 Replies Latest reply on Jan 15, 2014 6:40 PM by PSI

    Simple import hangs FM13


      the source data is from PBS ( Pharmaceutical Benefits Scheme). A text file 26MB contains just under 1000 authority criteria ( a number, a text explanation, 3 single character codes)



      To remove the size issue, a small sample (22) criteria were copied and saved asa .txt file

      then opened into a spreadsheet (LibreOffice) using tab dilimiter. The expected result in the import dialogue looks correct

      once imported some text fields appear empty, but clicking on the field shows the value - some quirky space character anomaly....

      save this as .xls


      open the xls into FMA13 and a cyling hang, cpu 100%, occasionaly swalpping cores ( MacBookPro OS 10.8.5)



      I also tried pushing the source text through TextWrangler to show invisibles, and Smultron, it crashed both.


      Attached sample small text file if anyone is game to take a look.

      Suggestions as to alternate methods?

        • 1. Re: Simple import hangs FM13

          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



          • 2. Re: Simple import hangs FM13

            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



            • 3. Re: Simple import hangs FM13

              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.



              • 4. Re: Simple import hangs FM13

                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.

                • 5. Re: Simple import hangs FM13

                  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?

                  • 6. Re: Simple import hangs FM13

                    quite possibly.


                    atatched is a fresh direct import. Would you mind checking that?

                    - would delineate the mac cf pc aspect at least.

                    • 7. Re: Simple import hangs FM13

                      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?

                      • 8. Re: Simple import hangs FM13

                        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.


                        that worked




                        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


                        thanks again

                        • 9. Re: Simple import hangs FM13

                          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?

                          • 10. Re: Simple import hangs FM13


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





                            • 11. Re: Simple import hangs FM13

                              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.





                              • 12. Re: Simple import hangs FM13

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


                                • 13. Re: Simple import hangs FM13

                                  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 ; ¶ ; "" ))

                                  • 14. Re: Simple import hangs FM13


                                    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

                                    1 2 Previous Next