Re: Problem loading data from text file...
you could do a 3-step process and use LOAD DATA INFILE: 1) loop through your file, checking the data and saving the output in a var using cfsavecontent: #line & chr(13) & chr(10)# [note: linebreaks inside cfsavecontent have een purposefully removed to prevent cf inserting empty lines. you can also use cfsetting enablecfoutputonly=true around the whole block for good measure ] then do to get rid of any trailing emptyness 2) save the massaged data into another temp file 3) use the temp file in your LOAD DATA INFILE and then delete it if necessary Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Rick Faircloth wrote: > Sounds like a good idea, Eric...if I could just figure out > how to handle the data! > > I read the file with this code: > > file = "e:\active_photos.txt" >variable = "active_photos" /> > > >From here, I've tried listtoarray, etc., but I can't get the > info in the text file into an array properly. > > The text file is a list of photos. > > The first line is column headings; Photo Filename, Photo Label. > > I can use listtoarray with chr(10)&chr(13) as the delimiters, > but that gives me each row as an element. > > I need the photo filename as array(1,1) and photo label as array(1,2). > > The problem I'm running into is that the photo filename and the photo label > are tab delimited...chr(9)...and each row is delimited by chr(10)&chr(13). > > I can't figure out how to use both delimiters to parse the rows and the row > elements. > > Suggestions? > > Thanks, > > Rick > > > >> -----Original Message- >> From: Eric Roberts [mailto:[EMAIL PROTECTED] >> Sent: Thursday, September 18, 2008 2:26 AM >> To: CF-Talk >> Subject: RE: Problem loading data from text file... >> >> Why not use cffile and then load it into the db...then you could easily >> process the data to be however you want it? >> >> Eric >> >> /*-Original Message- >> /*From: Rick Faircloth [mailto:[EMAIL PROTECTED] >> /*Sent: Wednesday, September 17, 2008 7:40 PM >> /*To: CF-Talk >> /*Subject: Problem loading data from text file... >> /* >> /*I'm using this query to load data into a table from a text file: >> /* >> /* >> /* >> /* load data infile >> /*'e:/inetpub/webroot/real_estate_data/hmls/data/#today#_idx_custom/active_p >> /*hotos.txt' >> /* into table hmls_active_photos_temp >> /* lines terminated by '\r\n' >> /* ignore 1 lines >> /* >> /* >> /* >> /*It runs fine except for the fact that at the end of each day's new >> /*"active_photos.txt" file, >> /*there are a couple of blanks lines, like the key has been struck a >> /*couple of times >> /*after the last line of data. >> /* >> /*The load data infile command is trying to read these lines and throws an >> /*error because of the >> /*empty lines. >> /* >> /*Is there some way to cause this query not to process, or to skip, empty >> /*lines? The empty lines >> /*are always at the end of the file and prevent me from automating the >> /*loading of data. >> /* >> /*Suggestions? >> /* >> /*Thanks, >> /* >> /*Rick >> /* >> /* >> /* >> >> >> > > ~| 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:312816 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Problem loading data from text file...
Sounds like a good idea, Eric...if I could just figure out how to handle the data! I read the file with this code: >From here, I've tried listtoarray, etc., but I can't get the info in the text file into an array properly. The text file is a list of photos. The first line is column headings; Photo Filename, Photo Label. I can use listtoarray with chr(10)&chr(13) as the delimiters, but that gives me each row as an element. I need the photo filename as array(1,1) and photo label as array(1,2). The problem I'm running into is that the photo filename and the photo label are tab delimited...chr(9)...and each row is delimited by chr(10)&chr(13). I can't figure out how to use both delimiters to parse the rows and the row elements. Suggestions? Thanks, Rick > -Original Message- > From: Eric Roberts [mailto:[EMAIL PROTECTED] > Sent: Thursday, September 18, 2008 2:26 AM > To: CF-Talk > Subject: RE: Problem loading data from text file... > > Why not use cffile and then load it into the db...then you could easily > process the data to be however you want it? > > Eric > > /*-Original Message- > /*From: Rick Faircloth [mailto:[EMAIL PROTECTED] > /*Sent: Wednesday, September 17, 2008 7:40 PM > /*To: CF-Talk > /*Subject: Problem loading data from text file... > /* > /*I'm using this query to load data into a table from a text file: > /* > /* > /* > /*load data infile > /*'e:/inetpub/webroot/real_estate_data/hmls/data/#today#_idx_custom/active_p > /*hotos.txt' > /*into table hmls_active_photos_temp > /*lines terminated by '\r\n' > /*ignore 1 lines > /* > /* > /* > /*It runs fine except for the fact that at the end of each day's new > /*"active_photos.txt" file, > /*there are a couple of blanks lines, like the key has been struck a > /*couple of times > /*after the last line of data. > /* > /*The load data infile command is trying to read these lines and throws an > /*error because of the > /*empty lines. > /* > /*Is there some way to cause this query not to process, or to skip, empty > /*lines? The empty lines > /*are always at the end of the file and prevent me from automating the > /*loading of data. > /* > /*Suggestions? > /* > /*Thanks, > /* > /*Rick > /* > /* > /* > > ~| 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:312778 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Problem loading data from text file...
Why not use cffile and then load it into the db...then you could easily process the data to be however you want it? Eric /*-Original Message- /*From: Rick Faircloth [mailto:[EMAIL PROTECTED] /*Sent: Wednesday, September 17, 2008 7:40 PM /*To: CF-Talk /*Subject: Problem loading data from text file... /* /*I'm using this query to load data into a table from a text file: /* /* /* /* load data infile /*'e:/inetpub/webroot/real_estate_data/hmls/data/#today#_idx_custom/active_p /*hotos.txt' /* into table hmls_active_photos_temp /* lines terminated by '\r\n' /* ignore 1 lines /* /* /* /*It runs fine except for the fact that at the end of each day's new /*"active_photos.txt" file, /*there are a couple of blanks lines, like the key has been struck a /*couple of times /*after the last line of data. /* /*The load data infile command is trying to read these lines and throws an /*error because of the /*empty lines. /* /*Is there some way to cause this query not to process, or to skip, empty /*lines? The empty lines /*are always at the end of the file and prevent me from automating the /*loading of data. /* /*Suggestions? /* /*Thanks, /* /*Rick /* /* /* ~| 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:312747 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Problem loading data from text file...
I'm using this query to load data into a table from a text file: load data infile 'e:/inetpub/webroot/real_estate_data/hmls/data/#today#_idx_custom/active_photos.txt' into table hmls_active_photos_temp lines terminated by '\r\n' ignore 1 lines It runs fine except for the fact that at the end of each day's new "active_photos.txt" file, there are a couple of blanks lines, like the key has been struck a couple of times after the last line of data. The load data infile command is trying to read these lines and throws an error because of the empty lines. Is there some way to cause this query not to process, or to skip, empty lines? The empty lines are always at the end of the file and prevent me from automating the loading of data. Suggestions? Thanks, Rick ~| 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:312730 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
