6 Replies Latest reply on Nov 15, 2013 6:01 AM by wimdecorte

    Help needed with export to fixed length txt file

    bartprins

      Hi there,

       

      I'm new to Filemaker and am using FM 12 Pro, i'm making a database which i made a couple of years back in Acces with some Vbacode.

      I want to export tables to textfiles with a fixed length, so no comma's or tabs. Is this possible and how can i do this?

       

      The fields in the table are already the right length so for instance the field customerId is 7 characters and the field customerName is 5 characters then the export should be "1234567Prins" without quotation marks of course.

       

      The only exports i can make are comma or tab delimited such as 1234567,Prins or 1234567 Prins

       

      Could you please help me out and thanks for reading !

       

      Bart Prins

      The Netherlands

        • 1. Re: Help needed with export to fixed length txt file
          Mike Duncan

          It sounds like all you need is a calculation or auto enter field that concatenates all the required fields into one string, then just export that one field as tab delimited (no tabs will be exported since there's only one field) and save it with whatever name you like.

           

          If you need to pad out the proper lengths for the fixed length fields, you can use something like this...

           

          Right ( "          " & field1 ; 10 ) &

          Right ( "          " & field2 ; 10 )

           

          where field 1 and 2 are fixed length values of 10.

           

          Does that work?

           

          Mike

          • 2. Re: Help needed with export to fixed length txt file
            RonSmithMD

            Hi, Bart.

             

            You'll need to make calculation fields that fix the length. These will be ghosts of the real field but will pad with spaces or whatever character that you want to force the necessary length.

             

            Here are two custom function that you are welcome to use. One is for text and the other for dates. Feel free to modify them. 'Text' and textdate would be the fields that you want to pad. 'Item' would be the item in the text say if you it consisted of a multi-itemed text object where each item is separated by the ¶ character. This may not be useful to you, but it was to me in my implementation. A further example of how I use this follows the two custom functions. Say you had a field that contained "Smith, Ronnie Earl" where you would substitute the spaces with the ¶ character before you pass it to the custom function.

             

             

            Pad ( text ; item ; padto ) =

             

            Let ([

            text = GetValue ( text ; item );

            padto = If (padto = "" ; Length (text) ; padto);

            text = Left ( text & Spaces (60) ; padto )

            ];

            text

            )

             

             

            PadDate ( textdate ; item : padto ) =

             

            Let ([

            textdate = GetValue ( textdate ; item );

            padto = If ( padto = "" ; 8 ; padto );

            textdate = Left ( Right (If( Month(textdate)  ≠ "" ; "0" ; "") & Month(textdate) ; 2 ) & " " & Right (If( Day(textdate)  ≠ "" ; "0" ; "")& Day(textdate) ; 2 ) & " "  & Right (Year(textdate) ; 2 )& Spaces (60) ; padto )

            ];

            textdate

            )

             

             

            In the database field definition for a calculated field you could have something like the following as an example where name consisted of a person where it was expected to be entered as 'last, first middle' for example:

             

            name_first_calculated = Pad ( Substitute ( Substitute ( name; "," ; "" ) ; " " ; "¶" ) ; 2 ; 15 )

             

            In practicality though if you wanted to have an export file where each line that contained a fixed length record which consisted of the last name padded to 20 characters, the first name padded to 15 characters and the middle name (if it exists) with only the first letter (i.e. one character length) then create one field for the entire export line. The following is a bit more complicated example, which I'll let you pore over and tear apart yourself as part of your learning curve. I would probably use a Let structure myself, but there are more than one of doing this to be sure. Notice I add the extra "¶¶" to make sure that I can get a value for each of the parts of the name. The Let structure is more efficient at this of course, but I'm not teaching you that here.

             

            name_fixedlength_formatted = (

             

            Pad ( Substitute ( Substitute ( name; "," ; "" ) ; " " ; "¶" ) & "¶¶" ; 1 ; 20 ) &

            Pad ( Substitute ( Substitute ( name; "," ; "" ) ; " " ; "¶" ) & "¶¶" ; 2 ; 15 ) &

            Left ( GetValue ( name & "¶¶" ; 2 ; 1 ) ; 1 )

             

            That will turn this:

             

            Smith, Ronnie Earl

             

            into this fixed length field:

             

            Smith               Ronnie         E

             

             

            Now what I've shown you is how to create a field definition which would automatically take data from another field which fits FileMaker's use and put it into something that another application would need as a fixed length field. You could even do more if necessary. Perhaps all the characters would need to be upper case. You just 'surround' the key parts of the definition with the Upper function then.

             

            Exporting then involves exporting only this one field. The calculated example that I showed you resulted in three separate data items, but you could have every other field included in that calculation. You can see how the calculation field type is quite powerful and very versatile in my example. I've done fixe length exporting before so I understand the need.

             

            Now you could just as well have created a script without using a field definition that looped through each of the records in the current set to do the same thing, up to then limit of a text variable size (I can't remember offhand what that is but it would likely be sufficient for each line in a record). You would need a plugin (and there are several out there) to write the code directly to the text file (of course you have to make sure that you insert the correct end of line character that your needs dictate) without using FileMaker's export feature.

             

            I think all in all, these options show the amount of muscle in FileMaker and why I've been using it since 1985. Hope that helps!

             

            Ron

             

            P.S. I don't think that I have those custom functions in my personal documents section, but you can check and see what I do have by searching for the tag ronsmithmd.

            1 of 1 people found this helpful
            • 3. Re: Help needed with export to fixed length txt file
              bartprins

              Hi Mike and Ron,

               

              Thank you both for your quick replies !! They were very helpfull, i'll go for Mike's answer for the short run but Ron thanks you for your extended answer i brings up some idea's i want to intergrate in the db i will try them later on this week thanks!!

               

              Filemaker looks and feels great !

               

              Bart

              • 4. Re: Help needed with export to fixed length txt file
                wimdecorte

                Both Mike and Ron point at using additional calc or auto-enter fields for the padded version.  I think you can probably do without those and do the padding in a script (in variables) whenever the export is needed and set the result in a global field for the exporting.

                 

                That would minimize the burden on your schema.

                • 5. Re: Help needed with export to fixed length txt file
                  bartprins

                  Hi Wim,

                   

                  Thanks for your reply, could you explain this al little bit more or do you perhaps have a sample of some kind ?

                   

                  Much oblidged

                   

                   

                  Bart

                  • 6. Re: Help needed with export to fixed length txt file
                    wimdecorte

                    No example but the approaches are straightforward.   You could loop through the found set and and collect the data into one variable, padding as you collect data from each field. At the end of the process you can dump the content of that variable in one global text field and do an "export field contents" from there.

                     

                    To speed things up and avoid looping through the actual records you can choose to use:

                    - getNthRecord

                    - or even do an ExecuteSQL to get all the data in one go, split it by your chosen field delimiter to do the padding