6 Replies Latest reply on Aug 18, 2017 7:10 AM by themax

    Parse tab delimited text in a field into multiple records

    JulianJohnson

      Hi There,

       

      I have looked through the archive for this and can only find partial threads covering it. I may well be missing the obvious here!

       

      I have a script, which inserts a set of tab delimited text into a variable from a URL. This text contains tab delimited columns, which I need to parse into fields and carriage return separated rows, that I need to put into new records.

       

      The text is formatted as follows:

      Venue 1     Area 1     Site 1     Image Name

      Venue 1     Area 2     Site 2     Image Name

      Venue 1     Area 2     Site 3     Image Name

      Venue 2     Area 1     Site 4     Image Name

       

      What's in the fields doesn't matter, I can sort all that out, it's just getting the values into the fields and records that I'm blank on!

       

      Is there a straightforward way to script this?

       

      Thanks

      Jules

        • 1. Re: Parse tab delimited text in a field into multiple records
          Mike_Mitchell

          Hello, Jules.

           

          There are a couple of ways to parse this out. Both involve looping over the rows of values and extracting each column; they just differ a bit in execution.

           

          A "standard practices" method uses the GetValue ( ) function twice. First, you extract a row using GetValue ( field ; rowNumber ). This would give you something like this:

           

          Venue 1     Area 1     Site 1     Image Name

           

          Then you use the Substitute function to change the row into a list. That would be Substitute ( row ; Char ( 9 ) ; "¶" ). (Char ( 9 ) returns the Tab character.) That gives you:

           

          Venue 1

          Area 1

          Site 1

          Image Name

           

          Then you have the ability to use GetValue ( ) again to extract each row and stick it in the appropriate destination.

           

          The other method is to use a custom function to extract one column at a time, then loop over it to stick it into the needed fields, looping over the records as necessary. (You'd use a single loop, most likely, putting each value into the necessary field as you loop over the records.) Here's a custom function I use:

           

          ------------------------------------------------------------------------------------------------------

          /*

          GetColumn function

           

           

          Author

          *COMMENT Visual Realisation

           

           

          Format

          cfGetColumn ( text ; columnNumber ; columnDelimiter )

           

           

          Parameters

          text - any text expression or text field

          columnNumber - any numeric expression or field containing a number

          columnDelimiter - any text expression or text field

          result - initially empty; used for tail recursion

           

           

          Data type returned

          text

           

           

          Description

          Returns a carriage return-delimited list of values from the requested column in a tabular text array.

           

           

          July 7, 2008

           

           

          Modified February, 2014 to use tail recursion

          Mike Mitchell, Net Caster Solutions

          www.netcastersolutions.com

           

           

          */

           

           

          Let ( [

          row  = GetValue ( text ; 1 ) ;

          cell = MiddleValues ( Substitute ( row ; columnDelimiter ; ¶ ) ; columnNumber ; 1 ) ;

          cell = Substitute ( cell ; ¶ ; "" ) ;

          countRows = ValueCount ( text )

          ] ;

           

           

          Case (

          countRows > 0 ;

          cfGetColumn ( RightValues ( text ; countRows - 1 ) ; columnNumber ; columnDelimiter ; List ( result ; cell )) ;

           

           

          result

           

           

          )

          )

          ------------------------------------------------------------------------------------------------------

           

          HTH

           

          Mike

          • 2. Re: Parse tab delimited text in a field into multiple records
            erolst

            Julian Johnson wrote:

            Is there a straightforward way to script this?

             

            Here is the more “straightforward” version:

             

            Set Variable ( $myList ; … )

            Set Variable [ $lineCount ; ValueCount ( $myList ) ]

            Loop

              Exit Loop if [ Let ( $lineCounter = $lineCounter + 1 ; $lineCounter > $lineCount ) ]

              New Record/Request

              Set Variable [ $thisLine ; GetValue ( $myList ; $lineCounter ) ]

              Set Variable [ $listThisLine ; Substitute ( thisLine ; Char(9) ; ¶ ) ]

              Set Field [ YourTable::venue ; GetValue ( $listThisLine ; 1 )

              Set Field [ YourTable::area ; GetValue ( $listThisLine ; 2 )

              Set Field [ YourTable::site ; GetValue ( $listThisLine ; 3 )

              Set Field [ YourTable::imageName ; GetValue ( $listThisLine ; 4 )

            End Loop


            You could abstract the value distribution, too, by looping through a list of field names and using Set Field by Name[]; for a scenario with just four fields it doesn't seem worth the effort, though.

            • 3. Re: Parse tab delimited text in a field into multiple records
              monkeybreadsoftware

              As GetValue is very slow on the long run, you may want to check the QuickList functions from MBS Plugin

              http://www.mbsplugins.eu/component_List.shtml

               

              I could even imaging to make functions to handle this import better. Like giving plugin the text, table name and field matching list and the plugin makes the import. Should be even faster.

              • 4. Re: Parse tab delimited text in a field into multiple records
                easyaspi

                How about this...

                Set the variable into a text field.

                Export that field to a file using the Export Field Contents script step.

                Import the file.

                • 5. Re: Parse tab delimited text in a field into multiple records
                  JulianJohnson

                  Hi All,

                   

                  That's brilliant, all three options would work. I shall have a play with each and see how I get on but thanks once again.

                   

                  Kind Regards

                  Jules

                  • 6. Re: Parse tab delimited text in a field into multiple records
                    themax

                    I was running into the same problem trying to update/add/delete records in a table, but on Filemaker Go (no plugin) !

                     

                    On a web server:

                     

                    I uploaded a text file :

                    http://www.webblabla.com/files/file1.txt

                     

                    File content :

                    1 Jo Finlay

                    2 Mary Stuart

                    3 Fred Flintstone

                    4 ...

                     

                     

                    On my Filemaker solution

                     

                    1- I set up a text field. (nothing particular) : ru_field_wb

                    2-I wrote simple script :

                     

                    Clear [Select; ru_field_wb]

                    insert from URL [Select; with dialog:off; temp_table::ru_field_wb; http://www.webblabla.com/files/file1.txt ]

                    Set variable [$exportimport; Value: Get(TemporaryPath) & "exportimport.txt"]

                    Export field contents [temp_table::ru_field_wb; $exportimport"]

                    import Records [with dialog:off; "$exportimport"; unicode]

                     

                    (select a table and fields : id, first, name)

                     

                    3-All the records will be in new table to play with.