Trying to read a text file of data and input into db

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>

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...

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. >

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 -----

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>

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 -----

> 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."

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>

> 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.

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.

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" >

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".

> 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.

Thanks for the suggestion, Tom. I may need to go that route. Rick ----- Excess quoted text cut - see Original Post for more -----

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.

> 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.

> I explicitly define the field separators That's a good idea... ----- Excess quoted text cut - see Original Post for more -----