Using ColdFusion for things other than serving web pages.
by Steve Durette

I just completed a project at work that ended up being simpler by using ColdFusion for something other than a web application server. In a moment of frustration when I was just about ready to do the job manually, it hit me that I may be able to make CF do the job for me.

First, I should give you a little background. Every month we get some tab delimited files from another group in our company. These files, which have data that is a snapshot in time, are then inserted into our MSSQL2K server through bulk inserts kicked off by stored procedures. We then store the files incase we have to rebuild the data, or if the other group tells us that minor updates need to be made to the files. While it doesn?t happen too often, the other group does give us changes. Since the data is a snapshot we can?t go back and pull new files, so we change our data files and import them again.

Our procedures are written so that if we need to load data from a previous month it will delete the data from our tables for that month then it finds the file and does the import. The problem that I was given was that we were told that we had to add a new field to the database. Going forward, all files would include an extra column (added on to the end luckily enough) that would have either a Y or an N for the value. This wouldn?t be a big problem going forward, all that had to be done to get the new data in would be to update our .fmt (MSSQL bulk insert format files) to include the extra column and add the column to the tables. If we ever had to modify and import data from the past, our procedures and inserts would choke because they find a carriage return [chr(13)] where there should be data.

Re-writing the procedures was not an option as they dealt with a lot of other files besides the ones that were changing besides, why break something that is working well. I asked the group providing the files what the pattern for ?Y?s and ?N?s were. It turned out that whatever the value of last column in the first row would be the value used for every line in that file. Trying to associate a Y or N to a filename wouldn?t work because each month the files last column could change. One thing they did do for me though was provide me a list of which files (by month) were Y and which were N.

So, I figured why not just open the files add the column and re-save the file. That?s exactly what I proceeded to do. I opened the first file Microsoft Excel added the column with the appropriate value and resaved the file in tab-delimited format. The whole process took about 2 minutes. I had 48 files to update, so it should take me about 2 hours. At least that?s what I thought until I opened the second file. An alert box popped up stating ?Not all of the data has been loaded.?. The first file was about 38k, the second one was over 6 Megs and greatly exceeded Excel?s 65536 row limit. I then looked at the file sizes for the rest of the files. Most of them were 6 Megs or larger.

Excel was now out of the question. Opening the files in notepad and manually adding the text for the column would take forever and probably give me carpal-tunnel really quick. Next I tried Homesite+ 5.5, I loaded the file and did a replace. I told Homesite to replace the carriage return with TAB Y or N and a Carriage Return. Then I got an out of memory error. On to Dreamweaver MX 2004. Same result. I don?t fault the programs for this problem, I have a lot of stuff running on my computer all at one time and kind of expected something like this.

I was just about ready to open Notepad and watch my productivity drop to almost nothing and then I thought, the problem isn?t actually that tough, why not see if I can let CF do the work for me.

Here is what I came up with (The file names and array values are made up of course, I wouldn?t want to give too much info away):

<cfscript>
    //Here we create a structure of arrays to hold the Y and N list I got from the other group.
    Variables.structFileColVal = structNew();
   Variables.structFileColVal["file1.txt"] = listToArray("Y,N,Y,N,Y,N,Y,N,Y,N,Y,N", ",");
   Variables.structFileColVal["file2.txt"] = listToArray("N,Y,N,Y,N,Y,N,Y,N,Y,N,Y", ",");
   Variables.structFileColVal["file3.txt"] = listToArray("N,N,N,Y,Y,Y,N,N,N,Y,Y,Y", ",");
   Variables.structFileColVal["file4.txt"] = listToArray("Y,Y,Y,N,N,N,Y,Y,Y,N,N,N", ",");
</cfscript>

<!--- Now we loop through the files and the months and update the files --->
<cfloop collection="#Variables.structFileColVal#" item="ynArray">
   <!--- Loop through each array item. Position 1 is January, 12 is December. --->
   <cfloop from="1" to="#arrayLen(Variables.structFileColVal[ynArray])#" index="mthVal">
      <!--- Set the filename of the file we are working on. --->
      <cfset Variables.fixFile = "c:\justATest\" & monthAsString(mthVal) & "\" & mthVal>
      <!--- Open the file and read it into a variable for editing. --->
      <cffile action="READ"
               variable="Variables.addCol"
               file="#Variables.fixFile#">
      <!--- replace all carriage returns with TAB Y/N CARRIAGE RETURN. --->
      <cfset Variables.addCol = replace(Variables.addCol, chr(13), chr(9) & Variables.structFileColVal[ynArray][mthVal] & chr(13), "All")>
      <!--- Rename the original file in case we make a mistake. --->
      <cffile action="RENAME"
               source="#Variables.fixFile#"
               destination="#Variables.fixFile#.old">
      <!--- Write out the file with the extra column. --->
      <cffile action="WRITE"
               file="#Variables.fixFile#"
               output="#Variables.fixFile#">
    </cfloop>
</cfloop>

I opened the page in my browser and 45 seconds later all of the files had been modified, saving my hands and my productivity (therefore my job). This incident reminded me of 2 very important things. First, ColdFusion can be used to do a lot more than just serving up dynamic content. Second, use all of the tools at your disposal to get the job done.