Brian Dunning's FileMaker Custom Functions

FixedFieldConverter ( Fieldname ; f1 ; f2 ; f3 ; f4 ; f5 ; f6 ; f7 ; f8 ; f9 ; f10 ; f11 ; f12 ; f13 ; f14 ; f15 ; f16 ; f17 ; f18 ; f1

converts Fixed Field Data file to a comma delimited data format

  Average rating: 4.0 (1 vote) Log in to vote

Natalie Fleming   Natalie Fleming
Fleming Consulting
Fleming Consulting

Share on Facebook Share on Twitter

  Sample input:
122333444455555666666777777788888888999999999101010101011111111111
  Sample output:
"1","22","333","4444","55555","55555","666666","7777777","88888888","999999999","1010101010","11111111111","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

While using the GNU AWK is most effective in converting fixed length fields to csv or any importable file format, sometimes I just can't ask clients to get into the scary terminal to prep data files for FM import.

This function is a great band aid for converting fixed length data files into a csv file for reimport into FM.

Although I do recommend GNU AWK whenever possible.


 

Comments

Robert Carrier   Robert Carrier, Bristol, TN
Oct 6, 2017
I have to agree with you on AWK. Especially if you are using Filemaker Server. I process 10,000 - 4,000 character records every day, and split them into 168 fields and I can promise you than no method is faster than AWK at this process. What took a second or so per record to do on a local Filemaker install runs on my server in minutes now. The trick is to maintain your field definitions in Filemaker, and use the data from that table to build the AWK command that will execute on the server. Then whenever there is a change to the data and related field definitions, they are automatically used on the next file processing cycle.

If anyone is interested in learning more about this process, or text preprocessing for Filemaker please comment on this post. I am happy to share.
 

Log in to post comments.

 

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Under construction. Email me your wish list for improvements.