OK, so I've been working on my mother's website for...well, too long. One of the reasons is I've been waiting on her to get approval to get a feed of listings, so we can put them directly on her site. Well, she finally got the approval, so I've been having fun this weekend, pulling in data and images, setting up database tables. The Works.

These feeds are tab delimited text files. The first line being a listing of all of the columns, with all of the rest being the data. So, I set up a staging table, with column names that match those in the file (luckily they provide a listing of the columns, along with their data type and length, in a separate .log file). Next, I used the Illudium PU-36 Code Generator to quickly give me some data access objects, and then settled down to write a little code.

Now, my first file has 7,000+ records in it, so I go ahead and give myself a little time for the code to do it's job.

view plain print about
1<cfsetting enablecfoutputonly="true" requesttimeout="600" />

Next thing I wanted were a few variables and objects to work with.

view plain print about
1<cfset VARIABLES.lineNum = 1 />
2<cfset VARIABLES.filePath = expandPath(".") & "\myFile.txt" />
3<cfset VARIABLES.Bean = CreateObject("component","feedRecord") />
4<cfset VARIABLES.DAO = CreateObject("component","feedRecordDAO").init(APPLICATION.dsn) />

And then I setup the loop on the file.

view plain print about
1<cfloop file="#VARIABLES.filePath#" index="VARIABLES.line">
2    <!--- Code to go here --->
3</cfloop>

OK, for those who don't know, the DAO object that is created by the code generator takes a bean object as the argument for the save() method. The bean object has an init() method with all of the column names as non-required arguments. So, how to best initialize my bean? Well, the data file's first row is a tab delimited list of the column names, so I decide to use it. First, I only want the first row to give me a data structure of the column names, in the order I'll need them. Hmmm? Ok, I decide to use an Array.

view plain print about
1<cfloop file="#VARIABLES.filePath#" index="VARIABLES.line">
2    <cfif VARIABLES.lineNum gt 1>
3        <!--- This is for later --->
4    <cfelse>
5        <cfset VARIABLES.propOrder = ArrayNew(1) />
6        <cfset VARIABLES.lineCount = 1 />
7        <cfloop list="#VARIABLES.line#" index="VARIABLES.listItem" delimiters="#Chr(9)#">
8            <cfset VARIABLES.propOrder[VARIABLES.lineCount] = VARIABLES.listItem />
9            <cfset VARIABLES.lineCount++ />
10        </cfloop>
11        <cfset VARIABLES.lineCount = 0 />
12    </cfif>
13    <cfset VARIABLES.lineNum++ />
14</cfloop>

Notice that the first part of my flow control is currently blank. This area I left at the beginning, as most lines will meet this criteria, and that's where the meat of the processing will be handled in the end. This Array, though very important, is only handled on the first row of the file. It will process first, because of the way the flow control is written, but bypassed throughout the rest of the process. BTW, I love the JS style operators;)

Now, I used an Array to maintain the order of the key names, but ultimately I'll need a Struct to pass into the bean's init() method, as an argumentCollection.

view plain print about
1<cfloop file="#VARIABLES.filePath#" index="VARIABLES.line">
2    <cfif VARIABLES.lineNum gt 1>
3        <cfset VARIABLES.resProp = StructNew() />
4    ....

Now, I was going to list loop through each line to set my Struct, but found out the hard way that <cfloop> still doesn't like empty items in a string. I was getting errors all over the place about truncated data and what, before I noticed data wasn't in the right place. What to do? Take a different approach! Instead of looping a list, I'll loop an Array, and make my Array from the list, while using the new includeEmptyFields option.

view plain print about
1<cfloop file="#VARIABLES.filePath#" index="VARIABLES.line">
2    <cfif VARIABLES.lineNum gt 1>
3        <cfset VARIABLES.resProp = StructNew() />
4        <cfset VARIABLES.arrProps = ListToArray(VARIABLES.line,Chr(9),true) />
5        <cfloop from="1" to="#ArrayLen(VARIABLES.propOrder)#" index="VARIABLES.itemCount">
6            <cfset VARIABLES.resProp[VARIABLES.propOrder[VARIABLES.itemCount]] = VARIABLES.arrProps[VARIABLES.itemCount] />
7        </cfloop>
8</code>
9
10Did you see it? Simple, eh? Now I have a Struct, where the data from each line matches up with the keys set from the first line of the file. All that's left is to set my bean and pass it to the save() method of the DAO.
11
12<cfloop file="#VARIABLES.filePath#" index="VARIABLES.line">
13    <cfif VARIABLES.lineNum gt 1>
14        <cfset VARIABLES.resProp = StructNew() />
15        <cfset VARIABLES.arrProps = ListToArray(VARIABLES.line,Chr(9),true) />
16        <cfloop from="1" to="#ArrayLen(VARIABLES.propOrder)#" index="VARIABLES.itemCount">
17            <cfset VARIABLES.resProp[VARIABLES.propOrder[VARIABLES.itemCount]] = VARIABLES.arrProps[VARIABLES.itemCount] />
18        </cfloop>
19        <cfoutput>Saving Record ## #VARIABLES.lineNum#. </cfoutput>
20        <cfset VARIABLES.Bean.init(argumentCollection:VARIABLES.resProp) />
21        <cftry>
22            <cfif VARIABLES.DAO.save(VARIABLES.Bean)>
23                <cfoutput>Record saved.<br /></cfoutput>
24            <cfelse>
25                <cfoutput>Error saving record.<br /></cfoutput>
26                <!--- custom cfthrow here --->
27            </cfif>
28            <cfcatch type="any">
29                <!--- and a custom error handler here --->
30            </cfcatch>
31        </cftry>
32        <cfflush />
33    <cfelse>
34        <cfset VARIABLES.propOrder = ArrayNew(1) />
35        <cfset VARIABLES.lineCount = 1 />
36        <cfloop list="#VARIABLES.line#" index="VARIABLES.listItem" delimiters="#Chr(9)#">
37            <cfset VARIABLES.propOrder[VARIABLES.lineCount] = VARIABLES.listItem />
38            <cfset VARIABLES.lineCount++ />
39        </cfloop>
40        <cfset VARIABLES.lineCount = 0 />
41    </cfif>
42    <cfset VARIABLES.lineNum++ />
43</cfloop>
44<cfsetting enablecfoutputonly="false" />

That's it! Nothing to it! Now, there are probably better ways, and half of this should be encapsulated even further, and it will break if the feed provider changes the column names. But, hey, it was fun! Right?

Example code included below with the Download link.