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: <cfoutput> <cfsavecontent variable="myvar"><cfloop file="pathtoyourfile" index="line"><cfif len(trim(line))>#line & chr(13) & chr(10)#</cfif></cfloop></cfsavecontent> </cfoutput>
[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 <cfset myvar = trim(myvar)> to get rid of any trailing emptyness 2) save the massaged data into another temp file <cffile action="write" file="fullpath/temp.csv" output="#trim(myvar)#"> 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: > > <cffile action = "read" > 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: >> /* >> /*<cfquery name="load_data" datasource="c21ar"> >> /* >> /* 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 >> /* >> /*</cfquery> >> /* >> /*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 <ENTER> 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
