Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
Trying to read a text file of data and input into db
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56847#308311
> I explicitly define the field separators
That's a good idea...
----- Excess quoted text cut - see Original Post for more -----
Author: Tom Chiverton
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56847#308301
> 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.
Yup, it's ace.
> All with just this code: (spot any issues?)
Looks like what we've got - except I explicitly define the field separators in
case the defaults MySQL uses changes.
--
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.
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56847#308238
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
----- Excess quoted text cut - see Original Post for more -----
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.
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56847#308200
Hi, Dave, and thanks for the feedback...
Yes, the tasks are registered and I did change the startdate year format to
'yyyy'
per your suggestion.
I also ended up having to set the action to "update" rather than run, which
also required operation="HTTPRequest", the url, and interval.
But now it's working fine. Here's the final solution:
<cfschedule
action = "update"
task = "02_Process HMLS Office Data Text File"
operation = "HTTPRequest"
url =
"http://localhost/real_estate_data/tasks/02_process_hmls_offices_text_file.cfm"
startdate = "#DateFormat(Now(), 'm/d/yyyy')#"
starttime = "#TimeFormat(DateAdd('n', 5, now()), 'h:mm tt')#"
interval = "once" >
Rick
> From a CF5 lang ref, so may be out of date... Is the task registered?
> Also try yyyy for startdate format. And you don't need "Interval".
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56847#308198
Thanks for the suggestion, Tom.
I may need to go that route.
Rick
----- Excess quoted text cut - see Original Post for more -----
Author: Tom Chiverton
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56847#308191
> 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.
Author: Dave Francis
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56847#308186
From a CF5 lang ref, so may be out of date... Is the task registered?
Also try yyyy for startdate format. And you don't need "Interval".
Hi, Tom... it's MySQL 5.
The code in my last email is working properly.
However, now I have a problem executing this:
<cfschedule
action = "run"
task = "02_Process HMLS Office Data Text
File"
startdate = "#DateFormat(Now(),
'm/d/yy')#"
starttime = "#TimeFormat(DateAdd('n', 5,
now()), 'h:mm tt')#"
interval = "once" >
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56847#308184
Hi, Tom... it's MySQL 5.
The code in my last email is working properly.
However, now I have a problem executing this:
<cfschedule
action = "run"
task = "02_Process HMLS Office Data Text File"
startdate = "#DateFormat(Now(), 'm/d/yy')#"
starttime = "#TimeFormat(DateAdd('n', 5, now()), 'h:mm tt')#"
interval = "once" >
I'm trying to run code like this at the end of each document that is
processed as a scheduled task to set up the next task one the current task
is complete. (The tasks are dependent)
I keep getting this error: Error 500: Internal Server Error
Is there anything wrong with the code above?
Thanks,
Rick
----- Excess quoted text cut - see Original Post for more -----
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.
Author: Tom Chiverton
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56847#308175
> I'm trying to read a tab-delimited text file of data
> and separate it into field data so I can input it into a database.
Almost all databases have a special bulk import tool or command, which may or
may not require cfexecute.
What database is this ?
--
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.
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56847#308174
Here's my solution, for anyone interested:
<cfquery name="delete_offices" datasource="#application.dsn#">
delete from hmls_office_temp
</cfquery>
<cfset filepath =
"e:\inetpub\webroot\real_estate_data\hmls\data\#DateFormat(now(),
'yyyy_mmdd')#_idx_custom\office.txt">
<cffile action = "read" file = "#filepath#" variable = "hmls_offices">
<cfset hmls_offices = #Replace(hmls_offices, "#chr(9)#", ", ", "All")#>
<cfset hmls_offices = #Replace(hmls_offices, "#chr(13)##chr(10)#", "|",
"All")#>
<cfset firstline = "true">
<cfloop index = "record" list = "#hmls_offices#" delimiters = "|">
<cfdump var = "#record#"><br>
<cfdump var = "#firstline#"><br>
<cfif firstline is "false">
<cfset temp_office_id = #listgetat(record, 1, ",")#>
<cfset office_name = #listgetat(record, 2, ",")#>
<cfset mail_address_1 = #listgetat(record, 3, ",")#>
<cfset mail_address_2 = #listgetat(record, 4, ",")#>
<cfset mail_city = #listgetat(record, 5, ",")#>
<cfset mail_state = #listgetat(record, 6, ",")#>
<cfset mail_zipcode = #listgetat(record, 7, ",")#>
<cfset phone = #listgetat(record, 8, ",")#>
<cfset fax = #listgetat(record, 9, ",")#>
<cfset office_email = #listgetat(record, 10, ",")#>
<cfquery name="insert_office" datasource="#application.dsn#">
insert into hmls_office_temp
( temp_office_id, office_name, mail_address_1,
mail_address_2, mail_city,
mail_state,
mail_zipcode, phone, fax, office_email
)
values ( <cfqueryparam cfsqltype="cf_sql_integer"
value="#temp_office_id#">,
<cfqueryparam cfsqltype="cf_sql_varchar"
value="#office_name#">,
<cfqueryparam cfsqltype="cf_sql_varchar"
value="#mail_address_1#">,
<cfqueryparam cfsqltype="cf_sql_varchar"
value="#mail_address_2#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#mail_city#">,
<cfqueryparam cfsqltype="cf_sql_varchar"
value="#mail_state#">,
<cfqueryparam cfsqltype="cf_sql_varchar"
value="#mail_zipcode#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#phone#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#fax#">,
<cfqueryparam cfsqltype="cf_sql_varchar"
value="#office_email#">
)
</cfquery>
<cfelse>
<cfset firstline = "false">
</cfif>
</cfloop>
Author: Charlie Griefer
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56847#308168
> I think I see what's happening... when the file is read
> with cffile, the variable, "hmls_offices", apparently has
> all the chr(9)'s, chr(10)'s, and chr(13)'s stripped out.
those characters aren't rendered by the browser, so you won't see
them. doesn't mean they're stripped out.
try changing your delimiters attribute of your <cfloop>
from:
delimiters = "#chr(13)#, #chr(10)#"
to:
delimiters = "#chr(13)#&#chr(10)#"
--
A byte walks into a bar and orders a pint. Bartender asks him "What's
wrong?" Byte says "Parity error." Bartender nods and says "Yeah, I
thought you looked a bit off."
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56847#308167
I think I see what's happening... when the file is read
with cffile, the variable, "hmls_offices", apparently has
all the chr(9)'s, chr(10)'s, and chr(13)'s stripped out.
Do I need some kind of "preserveHTML" function or something?
Rick
----- Excess quoted text cut - see Original Post for more -----
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56847#308166
Oops... list="hmls_offices" should have been list = "#hmls_offices#"
Now, I get an error stating:
Expression Exception - in
E:\Inetpub\webroot\c21-ar-homesite\tasks\0_process_hmls_offices_text_file.cfm :
line 14
Invalid list index 3.
(Index 3 is the "<cfset street_no_street_name... line)
Thoughts?
<cfset filepath =
"e:\inetpub\webroot\real_estate_data\hmls\data\#DateFormat(now(),
'yyyy_mmdd')#_idx_custom\office.txt">
<cffile action = "read" file = "#filepath#" variable = "hmls_offices">
<cfset firstline = true>
<cfloop index = "record" list = "#hmls_offices#" delimiters = "#chr(13)#,
#chr(10)#">
<cfif firstline is false>
<cfset office_id = listgetat(record, 1, "#chr(9)#")>
<cfset office_name = listgetat(record, 2, "#chr(9)#")>
<cfset street_no_street_name = listgetat(record, 3, "#chr(9)#")>
<cfset address_02 = listgetat(record, 4, "#chr(9)#")>
<cfset city = listgetat(record, 5, "#chr(9)#")>
<cfset state = listgetat(record, 6, "#chr(9)#")>
<cfset zipcode = listgetat(record, 7, "#chr(9)#")>
<cfset phone = listgetat(record, 8, "#chr(9)#")>
<cfset fax = listgetat(record, 9, "#chr(9)#")>
<cfset email = listgetat(record, 10, "#chr(9)#")>
<cfoutput>#record#</cfoutput><br><br>
<cfoutput>
office_id = #office_id#<br>
office_name = #office_name#<br>
street_no_street_name = #street_no_street_name#<br>
address_02 = #address_02#<br>
city = #city#<br>
state = #state#<br>
zipcode = #zipcode#<br>
phone = #phone#<br>
fax = #fax#<br>
email = #email#<br>
</cfoutput>
<cfelse>
<cfset firstline = false>
</cfif>
</cfloop>
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56847#308165
Well, here's the code I'm trying, but I get nothing on the
screen... no errors, no output, either...
Suggestions, anyone?
Rick
<cfset filepath =
"e:\inetpub\webroot\real_estate_data\hmls\data\#DateFormat(now(),
'yyyy_mmdd')#_idx_custom\office.txt">
<cffile action = "read" file = "#filepath#" variable = "hmls_offices">
<cfset firstline = true>
<cfloop index = "record" list = "hmls_offices" delimiters = "#chr(13)#,
#chr(10)#">
<cfif firstline is false>
<cfset office_id = listgetat(record, 1, "#chr(9)#")>
<cfset office_name = listgetat(record, 2, "#chr(9)#")>
<cfset street_no_street_name = listgetat(record, 3, "#chr(9)#")>
<cfset address_02 = listgetat(record, 4, "#chr(9)#")>
<cfset city = listgetat(record, 5, "#chr(9)#")>
<cfset state = listgetat(record, 6, "#chr(9)#")>
<cfset zipcode = listgetat(record, 7, "#chr(9)#")>
<cfset phone = listgetat(record, 8, "#chr(9)#")>
<cfset fax = listgetat(record, 9, "#chr(9)#")>
<cfset email = listgetat(record, 10, "#chr(9)#")>
<cfoutput>#record#</cfoutput><br><br>
<cfoutput>
office_id = #office_id#<br>
office_name = #office_name#<br>
street_no_street_name = #street_no_street_name#<br>
address_02 = #address_02#<br>
city = #city#<br>
state = #state#<br>
zipcode = #zipcode#<br>
phone = #phone#<br>
fax = #fax#<br>
email = #email#<br>
</cfoutput>
<cfelse>
<cfset firstline = false>
</cfif>
</cfloop>
----- Excess quoted text cut - see Original Post for more -----
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56847#308163
Hi, Dave...
Yes, it is a tab-delimited file. I've already been
manually importing several different documents into MySQL 5
using Navicat. I've got the manual approach down pat, but
need to automate this part.
I did find this technique written up online from 2003, which
I'll try to incorporate into the solution. (And I did try
using #record# instead of #hmls_offices#, but the entire file
was output again...)
<cfset bln_FirstLine = true>
<cfloop
list="#str_Content#"
delimiters="#chr(13)##chr(10)#"
index="str_Line">
<!--- Ignore the column name
line --->
<cfif bln_FirstLine is false>
<cfset str_Name =
listGetAt(str_Line, 1, ",")>
<cfset str_Phone =
listGetAt(str_Line, 2, ",")>
<cfset str_Email =
listGetAt(str_Line, 3, ",")>
<cfelse>
<cfset bln_FirstLine = false>
</cfif>
</cfloop>
> Off the top of my head, shouldn't it be
<cfoutput>#record#</cfoutput>?
> And I know you said tab-delimited, but have you confirmed that it really
> is? ie what does listlen(list,Chr(09)) give you.
>
Author: Dave Francis
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56847#308162
Off the top of my head, shouldn't it be
<cfoutput>#record#</cfoutput>?
And I know you said tab-delimited, but have you confirmed that it really
is? ie what does listlen(list,Chr(09)) give you.
Hi, all...
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56847#308159
Hi, all...
I'm trying to read a tab-delimited text file of data
and separate it into field data so I can input it into a database.
I can read the file, and output the entire contents,
but nothing I tried worked to output the contents into
separate pieces of data and separate records. The code
below just outputs everything.
Anyone have a little help for what I should add next to this code?
(At first I'm just trying to separate the data into the individual
parts for the fields and records...after that I can insert it into my db...)
I picked up the code below online, but couldn't find a way to make it work.
Thanks for any clues!
Rick
<cfset filepath =
"e:\inetpub\webroot\real_estate_data\hmls\data\#DateFormat(now(),
'yyyy_mmdd')#_idx_custom\office.txt">
<cffile action = "read" file = "#filepath#" variable = "hmls_offices">
<cfloop index = "record" list = "hmls_offices" delimiters = "#chr(9)#,
#chr(10)#">
<cfoutput>#hmls_offices#</cfoutput>
</cfloop>
|
May 24, 2012
|
Latest Fusion Authority Articles
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||
