Programming ColdFusion MX, 2nd Edition

Parsing Data

As shown in the previous examples, cfhttp is great for importing the contents of a text file into a query object and allowing for output of results to the screen. This process is useful if you want to dump the results of the file into another database or output the results to a simple HTML table. But what if the data you import requires a little cleaning up beforehand? Take, for example, the following delimited text file containing employee information for a fictitious company:

Name,Title,Department,Extension,JobCode
Joe Smith,Lead Salesperson,Sales,5515,A:001
Nancy Jones,Liaison,Marketing,5596,b:003
Tom White,Mechanical Engineer I,Engineering,5525,A:002
Jen Brown,Collection Specialist,Billing,5543,C:004
Mike Johnson,Security Guard,Security,5512,E:012

Note that this text file is very similar to the one we used earlier, but there is an added field called JobCode. This new field is used for a number of purposes within the company, including classifying an employee’s position. Suppose that we want to use cfhttp to put the contents of this text file into a query object. Nothing tricky needed; it’s just a simple cfhttp GET operation.

Once we have the contents of the file in a query object, however, we will want to append an additional column of data called Status and populate it based on each employee’s job code. Because the data is already contained within a query, there is no simple way to make the additions. We can wait until the results are output and make the additions ...