What database are you using, Bret?
I use MySQL 5 and I import MLS data from two different MLS's every day,
but I use SQL to do it in a cfquery, rather than CFML only. Very, very fast
this way.
For example, after downloading all the data from one of the MLS sites,
I first run a cfdirectory function on the folder that has the files:
<cfdirectory
directory='e:\inetpub\webroot\real_estate_data\smlc\daily_downloads\text_files\#today#'
action = 'list'
type = 'file'
name='filelist'>
Then I loop over the filelist and process the various delimited text files:
<cfloop query="filelist">
<cfquery name="load_data" datasource="#application.dsn#">
load data infile
'e:\inetpub\webroot\real_estate_data\smlc\daily_downloads\text_files\#today#\#name#'
<cfif #name# contains 'ACR'>
into table smlc_acr_temp
<cfelseif #name# contains 'COM'>
into table smlc_com_temp
<cfelseif #name# contains 'LOT'>
into table smlc_lot_temp
<cfelseif #name# contains 'MUL'>
into table smlc_mul_temp
<cfelseif #name# contains 'OFF'>
into table smlc_off_temp
<cfesleif #name# contains 'REN'>
into table smlc_ren_temp
<cfelseif #name# contains 'RES'>
into table smlc_res_temp
</cfif>
fields terminated by '|'
lines terminated by '\r\n'
</cfquery>
That gets all the data from the MLS text files and into
my database. From there, I run further queries to extract
just the columns of data that I want to use on a client's site
and insert that data into my final production database tables.
If you're not using MySQL 5, then perhaps your database has
something equivalment to "load data infile".
hth,
Rick
On Sun, Apr 19, 2009 at 12:52 PM, Bret McDermitt <[email protected]>wrote:
>
> Hey all, struggling with a large Real Estate data load. I have 6 text files
> with a couple hundred rows and a couple hundred columns which need to make
> their way into a database.
>
> I loop over the rows to get the actual data from the files, and I'm having
> to loop over each column to determine how to format the data for (int's are
> Val(value), strings are '#value#', etc...). My question is, can anyone think
> of a way where I could simplify the process of looping over the rows and
> columns? Instead of needing to loop over every row just to replace the data,
> I want to be able to
>
> -Set all data in column F = "'#value#'"
> -Set all data in column K = Val(value)
>
> The process just looks real inefficient as-is, CF must have some
> capabilities in data manipulation I'm just not familiar with. Any ideas
> would be great.
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321793
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
