Ask Ben: Creating A Fixed-Field-Width Data File With ColdFusion
Posted December 4, 2008 at 5:06 PM by Ben Nadel
I need to create a fixed length data file. Is there an easy way to make each #field# a fixed length?
For a web developer living in an environment that ignores white space, this can seem like a daunting task. Unless, of course, you know about the LJustify() and the RJustify() functions that ColdFusion comes with. The LJustify() and RJustify() methods justify a value to the left and right side of a fixed-width string respectively. Both take two arguments: the value and the length of the fixed-width string. With these two methods in-tow, the task becomes quite straightforward.
In this demo, I am going to build up a ColdFusion query and then convert that query into a fixed-field-width data value. This final data string can then be written to a file or used in a web service or whatever; for the sake of the demo, I am simply going to output it to the web browser:
- <!--- Create the data query. --->
- <cfset qData = QueryNew( "" ) />
-
- <!--- Create the ID column. --->
- <cfset QueryAddColumn(
- qData,
- "id",
- "cf_sql_integer",
- ListToArray( "1,5,78,3,232" )
- ) />
-
- <!--- Create the name column. --->
- <cfset QueryAddColumn(
- qData,
- "name",
- "cf_sql_varchar",
- ListToArray( "Anna,Kit,Libby,Sarah,Franci" )
- ) />
-
- <!--- Create the astrology sign column. --->
- <cfset QueryAddColumn(
- qData,
- "sign",
- "cf_sql_varchar",
- ListToArray( "Virgo,Libra,Scorpio,Scorpio,Leo" )
- ) />
-
-
- <!---
- Now, we are going to move the data in our constructed
- query into a fixed-with data file.
- --->
-
-
- <!---
- Create an array to hold our complete set of data.
- Each index in this array will represent a row in
- our final data file.
- --->
- <cfset arrData = [] />
-
- <!---
- Now, we are gonna loop over the query to convert each
- record into a fixed width data row.
- --->
- <cfloop query="qData">
-
- <!---
- Create an array to hold all the fields for this row
- of data. In this array, each index of the array will
- represent a single data field.
- --->
- <cfset arrRowData = [] />
-
- <!--- Create a 10 digit ID. --->
- <cfset arrRowData[ 1 ] = LJustify( qData.id, 10 ) />
-
- <!--- Create a 30 digit name. --->
- <cfset arrRowData[ 2 ] = LJustify( qData.name, 30 ) />
-
- <!--- Create a 20 digit sign. --->
- <cfset arrRowData[ 3 ] = LJustify( qData.sign, 20 ) />
-
-
- <!---
- Now that our array is full of the field data, each of
- which is "padded" to be fixed length, we need to add
- it to the master data array. As we do this, however,
- we are gonna collapse this row into a single string
- by converting it into a list that has no delimiter.
- --->
- <cfset ArrayAppend(
- arrData,
- ArrayToList( arrRowData, "" )
- ) />
-
- </cfloop>
-
-
- <!---
- At this point, our master data array has a finalized row of
- data in each index. We now need to collapse the master data
- array down into a single data string. We will do this by
- converting the data array into a list that uses line breaks
- as a delimiter.
- --->
- <cfset strFinalData = ArrayToList(
- arrData,
- (Chr( 13 ) & Chr( 10 ))
- ) />
-
-
- <!---
- When we output the data, let's replace the spaces with
- periods so that we can see on the web page how the fields
- were padding using LJustify().
- --->
- <cfoutput>
- <pre>#Replace( strFinalData, " ", ".", "all" )#</pre>
- </cfoutput>
Because we the web does not acknowledge more than one piece of white space in a row (my blog doesn't support the PRE tag), I am replacing the spaces with periods (.) such that the padding can be easily observed. When we run the above code, we get the following output:
1.........Anna..........................Virgo...............
5.........Kit...........................Libra...............
78........Libby.........................Scorpio.............
3.........Sarah.........................Scorpio.............
232.......Franci........................Leo.................
As you can see, ColdFusion's LJustify() method padded each of our fields to the appropriate fixed width. As a note of caution, if you have a value that is longer than the designated field length, ColdFusion will return the full string; it does not execute any sort of automatic value truncation. However, I assume that when working with fixed-field-width files, all the length validation is already done before outputting the data.
I hope this helps.
Reader Comments
You can workaround your lack of pre in this case by specifying a fixed-width/monospaced font. Set the font-family to something like "Courier, monospace" - the W3C has a decent page on it:
http://www.w3.org/Style/Examples/007/fonts
I'm really partial to the Proggy Fonts (http://www.proggyfonts.com/index.php?menu=download), but you can't count on everyone having those installed.
@Jason,
Thanks for the tip. I definitely appreciate a good mono-space font. However, I am not sure this will help with the multiple-white-spaces issue.
Ben,
I am having trouble duplicating your suggestion.
We are on CF7 if that makes a difference.
the line <cfset arrData = [] /> generates an error
"
ColdFusion was looking at the following text:
[
"
I already have a query but when applying the code to that query I get the same error.
On a different direction, I was outputting the query to the screen (no array) and the <Ljustify did not seem to work - it did not pad with spaces.
Ok what am I doing wrong.
@Ben,
I guess I was a bit tired and didn't articulate my thought well; my bad.
What I meant was that for the display in your blog post, if you used a monospaced font, it would better convey that everything was equal in character count. Of course, you still need the dots, but your columns would line up with a fixed-width font.
@Allen,
Sorry, the syntax error you are getting is ColdFusion 8 specific issue. I am using an implicit array. This is the same thing as:
<cfset arrData = ArrayNew( 1 ) />
You will need to do some minor modifications to get this to CF7 standards. I simply stopped doing CF7 code samples because I cannot test them (all our boxes are now CF8).
@Jason,
No worries my man. I agree, though - mono-space is a CSS class that I should have readily available.
I guess I need CF for dummies
disregarding fonts
why does this code not work:
<CFoutput query='q_DeptBill'>
#replace(Ljustify(client,10),' ','.')#
#replace(Ljustify(matter,10),' ','.')<br />
</CFoutput>
client numbers are typically 6 digits, it outputs the client number and 1 dot
matter numbers are typically 4 digits, it outputs the matter number and 1 dot
I need the field to be 10
@Allen,
First of all, you do NOT have to do the replace of spaces and dots (.). I did that only for the web-based output for debugging. When you are actually creating the CSV file, you do not want to do that otherwise the dots will be re-read in later as part of the field value. Leave the spaces.
Try this:
#Ljustify(client,10)#
#Ljustify(matter,10)#<br />
That should work.
I did that first (no dots) and it displayed the variable and added 1 space - I just put in the dots to make sure it was just 1 space.
Ljustify is clearly not working for me and I don't know how to trouble shoot that .
Allen
@Allen,
That is not good. This is an old function and should not be buggy.
Are you actually writing the CSV file? How are you testing this?
I am just writing to the screen
<cfoutput query='q_DeptBill'>
#replace(Ljustify(client,10),' ','.')##replace(Ljustify(matter,10),' ','.')#
</cfoutput>
or
#Ljustify(client,10)##Ljustify(matter,10)#
I kept all the complications out of the way that I could.
There are of course many more fields but I thought 2 would be good to start. It is getting the client and matter numbers from the database so I know that is working.
@Allen,
Check the "source" of the page. If you are just outputting to screen, the spaces won't be acknowledged. But, they should register on the source of the rendered page.
That was it. The spaces are there in the source. Wow, so what you see is not always what you get.
What is the easiest (shortest) way to write this to a file that will keep the spaces. Can I put the functioning <cfoutput inside a <cffile tag?
What I am asking; is there a way to avoid the array?
@Allen,
You could avoid the master array by doing a CFFILE [action=append] after each row loop. That way, each row is written to the file as it is created.
As far as writing the end data to a file, you could just use the output:
<cffile action="write" output="#strFinalData#" />
@Ben, It may be faster for Allen to build the string in memory rather than writing to the file on each iteration, depending on the size of the string that he will create.
Just something to think about!
@Allen,
Just for future information, any number of whitespace characters in a row are always condensed by web browsers into a single blank character. This is typical behavior on the web, so you are correct that what you get is not always what you see. There are plenty of other coding 'gotchas' as well, such as & shows as & and so on.
:)
@Hatem,
I agree. I only suggested that because he had asked about getting rid of one of the arrays.
Thanks all for the help. I guess I knew that about browsers but it didn't come to mind. Using CFMAIL, I successfully sent my new fixed width file to the person in charge of the next system for testing. Creating the body of an email doesn't condense the spaces. I may have to go back to 'creating the file' for the actual automation but I will deal with that next week.
Allen
@Allen,
Glad you got things working. Let us know if you get stuck any where.
Why wouldn't you just use CFTable?
<cfset crlf = #chr(13)#&#chr(10)#>
<cfquery name="myData">select ID, account, type, status FROM MyDB</cfquery>
<cfsavecontent variable="export"><cftable query="myData">
<cfcol width="2" text="#myData.ID#">
<cfcol width="10" text="#myData.account#">
<cfcol width="2" text="#myData.type#">
<cfcol width="10" text="#myData.status#">
</cftable></cfsavecontent>
<!--- clean up pre tags & newlines --->
<cfset export = replacelist(export,"<pre>#crlf#,#crlf##crlf#</pre>#crlf#","")>
<cffile action="write" addnewline="no" file="#GetDirectoryFromPath(GetBaseTemplatePath())#myData.txt" output="#export#" fixnewline="no">
@JC,
To be honest, I've never heard of the CFTable tag. I just looked it up and it seems it's been in there since MX6.1 or something.
While a nice solution, I would rather just wrap my algorithm up in a UDF than have to play around with stripping out PRE tags. Again, it's nice, but it feels like a use of something that was not intended to be used for such things.
Actually, Ben, it's been around since at least coldfusion 3, and it pretty much was intended to be used for such things. Didn't have UDFs until version 5. :)
*shrug* The other way works, but you must admit this is a far, far simpler solution -- and that kind of simplicity is what made CF a success in the first place.
@JC,
Dang, since CF3!! I can't believe I haven't heard of it! I am not saying this isn't a simple solution - for displaying a fixed-width table on screen certainly, you cannot get more simple that this. However, since you are modifying the output, you would probably want to wrap the modification up in a UDF. And, at that point, you can, if you want to, use any solution you want.
Awesome stuff. Thanks for taking the time and putting this up.
once again Ben saves the day, cheers man!
