Wow, Tom!
You weren't kidding when you said taking the "load data infile"
approach was quick! And it's accurate, too! The data seems to
be going in perfectly.
All with just this code: (spot any issues?)
<cfquery name="load_data" datasource="c21ar">
load data infile
'e:/inetpub/webroot/real_estate_data/hmls/data/2008_0626_idx_custom/format1.txt'
into table hmls_residential_temp
lines terminated by '\r\n'
ignore 1 lines
</cfquery>
The file I'm loading is a tab-delimited text file. Records terminated
by CRLN and the file has the column names as the first record.
It also took me a minute to realize that the local Windows path
had to have the forward slashes in the path rather than typical backslashes.
See any problems?
Thanks for the tip!
Rick
> -----Original Message-----
> From: Tom Chiverton [mailto:[EMAIL PROTECTED]
> Sent: Thursday, June 26, 2008 10:31 AM
> To: CF-Talk
> Subject: Re: Trying to read a text file of data and input into db...
>
> On Thursday 26 Jun 2008, Rick Faircloth wrote:
> > Hi, Tom... it's MySQL 5.
>
> By coincidence, I'm doing bulk loads into that too.
> You can use CFQUERY to do:
> LOAD DATA LOCAL INFILE '/a/file/on/the/web.server'
> INTO TABLE demoTable
> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
> LINES TERMINATED BY '\r\n'
> ignore 1 lines
> (aStr,
> @dummy1,
> @dummy2,
> @dummy3,
> aInt)
> set aDate=str_to_date(@dummy1,'%d/%m/%Y'),
> aDatetime=str_to_date(@dummy2,'%d/%m/%Y %T'),
> aTimeStamp=str_to_date(@dummy3,'%d/%m/%Y %T')
>
> It's very very quick.
> --
> Tom Chiverton
>
> ****************************************************
>
> This email is sent for and on behalf of Halliwells LLP.
>
> Halliwells LLP is a limited liability partnership registered in England and
> Wales under registered
> number OC307980 whose registered office address is at Halliwells LLP, 3
> Hardman Square,
Spinningfields,
> Manchester, M3 3EB. A list of members is available for inspection at the
> registered office. Any
> reference to a partner in relation to Halliwells LLP means a member of
> Halliwells LLP. Regulated
by The
> Solicitors Regulation Authority.
>
> CONFIDENTIALITY
>
> This email is intended only for the use of the addressee named above and may
> be confidential or
legally
> privileged. If you are not the addressee you must not read it and must not
> use any information
> contained in nor copy it nor inform any person other than Halliwells LLP or
> the addressee of its
> existence or contents. If you have received this email in error please
> delete it and notify
Halliwells
> LLP IT Department on 0870 365 2500.
>
> For more information about Halliwells LLP visit www.halliwells.com.
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:308238
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
