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
Fleming Consulting
Fleming Consulting

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)

/*9-14-2017 Natalie Fleming  Fleming Consulting
Custom function to convert fixed length data file into csv delimited format.  
*/

Let  ( [fl1 = f1 ; 
fl2 = fl1 + f1 ; 
fl3 = fl2 + f2 ; 
fl4 = fl3 + f3 ; 
fl5 = fl4 + f4 ; 
fl6 = fl5 + f5 ; 
fl7 = fl6 + f6 ; 
fl8 = fl7 + f7 ; 
fl9 = fl8 + f8 ; 
fl10 = fl9 + f9 ; 
fl11 = fl10 + f10 ; 
fl12 = fl11 + f11 ; 
fl13 = fl12 + f12 ; 
fl14 = fl13 + f13 ; 
fl15 = fl14 + f14 ; 
fl16 = fl15 + f15 ; 
fl17 = fl16 + f16 ; 
fl18 = fl17 + f17 ; 
fl19 = fl18 + f18 ; 
fl20 = fl19 + f19 ; 
fl21 = fl20 + f20 ; 
fl22 = fl21 + f21 ; 
fl23 = fl22 + f22 ; 
fl24 = fl23 + f23 ; 
fl25 = fl24 + f24 ; 
fl26 = fl25 + f25 ; 
fl27 = fl26 + f26 ; 
fl28 = fl27 + f27 ; 
fl29 = fl28 + f28 ; 
fl30 = fl29 + f29 ; 
fl31 = fl30 + f30 ; 
fl32 = fl31 + f31 ; 
fl33 = fl32 + f32 ; 
fl34 = fl33 + f33 ; 
fl35 = fl34 + f34 ; 
fl36 = fl35 + f35 ; 
fl37 = fl36 + f36 ; 
fl38 = fl37 + f37 ; 
fl39 = fl38 + f38 ; 
fl40 = fl39 + f39 ; 
fl41 = fl40 + f40 ; 
fl42 = fl41 + f41 ; 
fl43 = fl42 + f42 ; 
fl44 = fl43 + f43 ; 
fl45 = fl44 + f44 ; 
fl46 = fl45 + f45 ; 
fl47 = fl46 + f46 ; 
fl48 = fl47 + f47 ; 
fl49 = fl48 + f48 ; 
fl50 = fl49 + f49

] ; 

 "\"" & Left  (  Fieldname  ;  f1  )  & "\",\"" &
Middle  ( Fieldname ; fl2 ; f2 )  & "\",\"" &
Middle  ( Fieldname ; fl3 ; f3 )  & "\",\"" &
Middle  ( Fieldname ; fl4 ; f4 )  & "\",\"" &
Middle  ( Fieldname ; fl5 ; f5 )  & "\",\"" &
Middle  ( Fieldname ; fl5 ; f5 )  & "\",\"" &
Middle  ( Fieldname ; fl6 ; f6 )  & "\",\"" &
Middle  ( Fieldname ; fl7 ; f7 )  & "\",\"" &
Middle  ( Fieldname ; fl8 ; f8 )  & "\",\"" &
Middle  ( Fieldname ; fl9 ; f9 )  & "\",\"" &
Middle  ( Fieldname ; fl10 ; f10 )  & "\",\"" &
Middle  ( Fieldname ; fl11 ; f11 )  & "\",\"" &
Middle  ( Fieldname ; fl12 ; f12 )  & "\",\"" &
Middle  ( Fieldname ; fl13 ; f13 )  & "\",\"" &
Middle  ( Fieldname ; fl14 ; f14 )  & "\",\"" &
Middle  ( Fieldname ; fl15 ; f15 )  & "\",\"" &
Middle  ( Fieldname ; fl16 ; f16 )  & "\",\"" &
Middle  ( Fieldname ; fl17 ; f17 )  & "\",\"" &
Middle  ( Fieldname ; fl18 ; f18 )  & "\",\"" &
Middle  ( Fieldname ; fl19 ; f19 )  & "\",\"" &
Middle  ( Fieldname ; fl20 ; f20 )  & "\",\"" &
Middle  ( Fieldname ; fl21 ; f21 )  & "\",\"" &
Middle  ( Fieldname ; fl22 ; f22 )  & "\",\"" &
Middle  ( Fieldname ; fl23 ; f23 )  & "\",\"" &
Middle  ( Fieldname ; fl24 ; f24 )  & "\",\"" &
Middle  ( Fieldname ; fl25 ; f25 )  & "\",\"" &
Middle  ( Fieldname ; fl26 ; f26 )  & "\",\"" &
Middle  ( Fieldname ; fl27 ; f27 )  & "\",\"" &
Middle  ( Fieldname ; fl28 ; f28 )  & "\",\"" &
Middle  ( Fieldname ; fl29 ; f29 )  & "\",\"" &
Middle  ( Fieldname ; fl30 ; f30 )  & "\",\"" &
Middle  ( Fieldname ; fl31 ; f31 )  & "\",\"" &
Middle  ( Fieldname ; fl32 ; f32 )  & "\",\"" &
Middle  ( Fieldname ; fl33 ; f33 )  & "\",\"" &
Middle  ( Fieldname ; fl34 ; f34 )  & "\",\"" &
Middle  ( Fieldname ; fl35 ; f35 )  & "\",\"" &
Middle  ( Fieldname ; fl36 ; f36 )  & "\",\"" &
Middle  ( Fieldname ; fl37 ; f37 )  & "\",\"" &
Middle  ( Fieldname ; fl38 ; f38 )  & "\",\"" &
Middle  ( Fieldname ; fl39 ; f39 )  & "\",\"" &
Middle  ( Fieldname ; fl40 ; f40 )  & "\",\"" &
Middle  ( Fieldname ; fl41 ; f41 )  & "\",\"" &
Middle  ( Fieldname ; fl42 ; f42 )  & "\",\"" &
Middle  ( Fieldname ; fl43 ; f43 )  & "\",\"" &
Middle  ( Fieldname ; fl44 ; f44 )  & "\",\"" &
Middle  ( Fieldname ; fl45 ; f45 )  & "\",\"" &
Middle  ( Fieldname ; fl46 ; f46 )  & "\",\"" &
Middle  ( Fieldname ; fl47 ; f47 )  & "\",\"" &
Middle  ( Fieldname ; fl48 ; f48 )  & "\",\"" &
Middle  ( Fieldname ; fl49 ; f49 )  & "\",\"" &
Middle  ( Fieldname ; fl50 ; f50 )  & "\",\"" 
 )

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

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

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

Comments

Support this website.

Support this website.