10 Replies Latest reply on Sep 4, 2009 8:55 PM by JRemsen

    Problem with data import of basic width delimited txt file

    JRemsen

      Title

      Problem with data import of basic width delimited txt file

      Post

      I am relatively new to the scripting side of filemaker pro but I or it seem to be missing something. Why doesn't filemaker pro allow the import of data from a basic width delimited text file? I was pretty sure all database software could carry this out since early 90's. Is there some reason why its hidden or not in the package? More importantly if it is not what is the workaround.

       

      Text file framework:

       

      text:10 text:3 text:20 text:20 text:20 text:20 text:8 text:10

       

      so

       

      "date-sec-comment-comment-comment-comment-file#-comment"

       

      excluding the "-"

       

      To be fed into field 1, field 2, field 3, field 4....field 8

       

      How do I bring this in to Filemaker Pro without using Excel or Access to process it first? I want to take out the middle man and go from TXT right into the database so I can automate the updates I need.

       

      I spent roundabout 4 hours today trying to make the above basic function happen using different scripting methods that were available in Filemaker.   

       

      Thanks,

       

      Jay

        • 1. Re: Problem with data import of basic width delimited txt file
          comment_1
             You do need a middle man - but Filemaker can be it. Import your data into a temp table (it will be all in one field). Define 8 calculation fields in this table, using the Middle() function to extract the individual field data. Then import the records from the temp table into the real table.
          • 2. Re: Problem with data import of basic width delimited txt file
            JRemsen
              

            I think you mean use the set field function with middle. I was working on that yesterday. That meathod requires me to build an additional table to feed the data into...I was hoping that this function would have been there already. Its been 20 years I think it is about time for file maker to add it. :)

             

             

             

            • 3. Re: Problem with data import of basic width delimited txt file
              comment_1
                

              JRemsen wrote:

              I think you mean use the set field function with middle.


              No, I mean use calculation fields.

               

               


              JRemsen wrote:

              I was hoping that this function would have been there already.


              You are welcome to make a feature suggestion. However, I don't think it's very likely they will now add support for an obsolete format. Personally, I'd rather see them concentrate on more pressing issues, of which there is no shortage.

               



              • 4. Re: Problem with data import of basic width delimited txt file
                etripoli
                   Another option would be to substitute a 'tab' character for each of the current delimiters, using a calculation field, export that field in the records as a tab file, then import it back into the proper table.
                • 5. Re: Problem with data import of basic width delimited txt file
                  comment_1
                     No, I'm afraid that wouldn't work at all: first, a fixed width file has no delimiters (other than record delimiters). An even if you inserted tab characters in the correct places, they would be converted to spaces when exported.
                  • 6. Re: Problem with data import of basic width delimited txt file
                    TKnTexas
                      

                    Text file framework:

                    text:10 text:3 text:20 text:20 text:20 text:20 text:8 text:10

                    "date-sec-comment-comment-comment-comment-file#-comment" 

                     

                    F1=DataImport [text type: import everything to this field]

                    Parsed Fields 

                    Date         [calculation with date type: left(f1,2)&"/"&middle(f1,3,2)&"/"&middle(f1,5,4)

                    Sec          [calculation with text type:  middle(f1,11,3)

                    Comment1 [calculation with text type:  middle(f1,14,20) 

                    I am not sure what a 10-digit date would parse out too.  I only "see" 8-digit mmddyyyy.

                     

                    Hopefully this will get you started.  I have just done this on a number of projects.  Has worked perfectly to parse out the pieces of data.  I no longer filter through Excel.

                    • 7. Re: Problem with data import of basic width delimited txt file
                      etripoli
                        

                      comment wrote:
                      No, I'm afraid that wouldn't work at all: first, a fixed width file has no delimiters (other than record delimiters). An even if you inserted tab characters in the correct places, they would be converted to spaces when exported.

                      Right-o, meant to reference the Export Field... commands, not the Export File.  Carry on.


                      • 8. Re: Problem with data import of basic width delimited txt file
                        JRemsen
                          

                        Thank you for all your responses :)

                         

                        I was trying to select all and copy/paste my script to this page but it won't let me.

                         

                        what I tried to do (boy I wish my script would paste)

                         

                        I created a second table and fed the table with the data from the TXT file. lets call it table 2, its only field is f1

                         

                        The first table definition is :

                         

                        f1 = date field

                        f2 through f8 = text

                        f10= number

                         

                        The Second table set is a full import of the txt file into f1 defined as just text

                         

                        The current form of the script:

                         

                        go to layout table 2

                        delete all records

                        go to layout  table 1

                        delete all records

                        import records ( from texttable.txt ) into text table 2   'note this is just 1 field in this table

                        go to layout text table 1

                        go to record request page(first)

                        loop

                        set field(table1 which is calendar date)=date(left(table2:2);middle(table2:4,2);middle(table2:7:4))

                        set field(table1 which is just text )=middle(table2:12;10)  this is to skip a delimiter I tried to add of @ which Excel understands but file maker doesn't :(

                         

                        set field....set field...set field...for 8 repetitions using middle calculation to parse all text definitions

                         

                        set field(table1 which is a number and would be field 10)=getasnumber(middle(table2:162;7))

                        exit loop if(get(recordnumber)=Get(foundcount)

                        go to record/request/page(next;exit after last)

                        end loop

                         

                        After running the above script the records delete works.

                         

                        The record import works.

                         

                        None of the data unfortunately from table 2 ever goes into table one.

                         

                        Should I be using something else besides set field?

                         

                        Only one other note from the above logic table 1 has no records when I try to set field.

                         

                        Thanks

                        Jay

                         

                         

                         

                         

                         

                        • 9. Re: Problem with data import of basic width delimited txt file
                          philmodjunk
                            

                          Copy and pasting scripts:

                           

                          Method 1: If you have advanced, generatea design report and copy from it.

                          Method 2: If you have a PDF generating utility, print your script to a PDF file and copy from it.

                          • 10. Re: Problem with data import of basic width delimited txt file
                            JRemsen
                              

                            That output worked out really well with PDF form.

                            Anybody have any idea what my code issue is up top and why set field does not actually set table 1's field like I think it is supposed to. Are there other options?