I don't know if this will help but I wrote this code to do a 1 time file
load. It's not as generic as it could be but it might give you some ideas.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Import Text</title>
<basefont face="Verdana" size="-1">
</head>
<body>
<!--- Get the file in memory. Can the server handle this? It handled 2mb
without any problem --->
<cffile action="READ" file="d:\Inetpub\wwwroot\dir1\file.txt"
variable="import">
<cfset dblquote = Chr(34)&Chr(34)>
<cfset quote = Chr(34)>
<cfset row = 0>
<!--- The file being read has carriage returns at the end of each record and
I put "|" in as the
field delimiter. The big text fields have line feeds in them instead
of CR's which I convert
in Oracle after the data is loaded. --->
<!--- Loop through the import file grabbing a record on each pass then cut
that record off...
... the front of the import file before the next loop --->
<CFLOOP CONDITION="Len(import) GT 0">
<cfset row = row + 1>
<!--- This grabs a single record --->
<cfset temp = SpanExcluding(import,Chr(13))>
<!--- Now parse out the fields and save to Oracle. The news body will need
to be chopped into
4k parts and saved in the child table. --->
<cfset pos1 = Find("|",temp)>
<cfset cnt1 = pos1 - 2><!--- subtract 2 because we're starting at char2 to
lose the opening " and then to lose the ending | --->
<cfset field1 = Mid(temp,2,cnt1)>
<cfset pos2 = Find("|",temp,pos1+1)>
<cfset field2 = Mid(temp,pos1+1,pos2 - pos1-1)>
<cfset pos3 = Find("|",temp,pos2+1)>
<cfset field3 = Mid(temp,pos2+1,pos3 - pos2-1)>
<cfset pos4 = Find("|",temp,pos3+1)>
<cfset field4 = Mid(temp,pos3+1,pos4 - pos3-1)>
<cfset pos5 = Find("|",temp,pos4+1)>
<cfset field5 = Mid(temp,pos4+1,pos5 - pos4-1)>
<cfset pos6 = Find("|",temp,pos5+1)>
<cfset field6 = Mid(temp,pos5+1,pos6 - pos5-1)>
<cfset pos7 = Find("|",temp,pos6+1)>
<cfset field7 = Mid(temp,pos6+1,pos7 - pos6-1)>
<cfset pos8 = Find("|",temp,pos7+1)>
<cfset field8 = Replace(Mid(temp,pos7+1,pos8 -
pos7-1),dblquote,quote,"ALL")>
<cfset pos9 = Find("|",temp,pos8+1)>
<cfset field9 = Mid(temp,pos8+1,pos9 - pos8-1)>
<cfset pos10 = Find("|",temp,pos9+1)>
<cfset field10 = Mid(temp,pos9+1,pos10 - pos9-1)>
<cfset pos11 = Len(temp)>
<cfset field11 = Mid(temp,pos10+1,pos11 - pos10-1)>
<cfoutput>
#row#
<!--- <table border="1">
<tr><th>Temp</th><td>#temp#</td></tr>
<tr><th>Pos1</th><td>#pos1#</td></tr>
<tr><th>Field1</th><td>#field1#</td></tr>
<tr><th>Pos2</th><td>#pos2#</td></tr>
<tr><th>Field2</th><td>#field2#</td></tr>
<tr><th>Pos3</th><td>#pos3#</td></tr>
<tr><th>Field3</th><td>#field3#</td></tr>
<tr><th>Pos4</th><td>#pos4#</td></tr>
<tr><th>Field4</th><td>#field4#</td></tr>
<tr><th>Pos5</th><td>#pos5#</td></tr>
<tr><th>Field5</th><td>#field5#</td></tr>
<tr><th>Pos6</th><td>#pos6#</td></tr>
<tr><th>Field6</th><td>#field6#</td></tr>
<tr><th>Pos7</th><td>#pos7#</td></tr>
<tr><th>Field7</th><td>#field7#</td></tr>
<tr><th>Pos8</th><td>#pos8#</td></tr>
<tr><th>Field8</th><td>#field8#</td></tr>
<tr><th>Pos9</th><td>#pos9#</td></tr>
<tr><th>Field9</th><td>#field9#</td></tr>
<tr><th>Pos10</th><td>#pos10#</td></tr>
<tr><th>Field10</th><td>#field10#</td></tr>
<tr><th>Pos11</th><td>#pos11#</td></tr>
<tr><th>Field11</th><td>#field11#</td></tr>
</table> --->
</cfoutput>
<!--- Insert the record --->
<cfquery name="GetID" datasource="#Application.News_DSN#">
Select GetMaxID from dual
</cfquery>
<cfif GetID.GetMaxID EQ ""> <!--- NOthing is returned if there are zero
records so deal with that here --->
<cfset varID = 1>
<cfelse>
<cfset varID = GetID.GetMaxID>
</cfif>
<cfquery name="insertnews" DATASOURCE="#Application.News_DSN#">
Insert into news.news
(news_id, release_date, publish, contact, news_type, headline,
offset, insert_date, insert_userid)
Values
(#varID#, TO_DATE('#DateFormat(field2,'mm/dd/yyyy')#','MM/DD/YYYY'),
'Y', '#field1#', '#field4#', '#field6#',100, sysdate, 3286)
</cfquery>
<cfset partcnt = 0>
<cfloop condition="Len(field8) GT 0">
<cfset partcnt = partcnt + 1>
<cfset bodylength = Len(field8)>
<!--- Grab a 4k chunk --->
<cfif bodylength GT 4000>
<cfset bodypart =
RemoveChars(field8,4001,bodylength-4000)>
<cfelse>
<cfset bodypart = field8>
</cfif>
<cfquery name="insertbodypart" DATASOURCE="#Application.News_DSN#">
Insert into news.news_body
(news_id, part_id, part)
Values
(#varID#, #partcnt#, '#bodypart#')
</cfquery>
<cfset field8 = RemoveChars(field8,1,3900)>
</cfloop>
<!--- Chop this record off the import variable --->
<cfset import = RemoveChars(import,1,Len(temp)+1)>
</CFLOOP>
</body>
</html>
----------- END FILE ------------
Duncan Hays
-----Original Message-----
From: Kay Smoljak [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, April 25, 2001 11:02 AM
To: CF-Talk
Subject: textfile parsing
Hi all,
I'm trying to parse the contents of a text file, with the ultimate aim of
putting the data in a database table. The text file contains email messages
(which may contain html code), with each new message beginning with the text
'=newmessage=' (minus quotes). I'm trying to use the listappend function
like this:
<cfset agentlist = listappend("agentlist", uploadedagentfile,
"=newmessage=")>
to split the file into individual messages. But, it doesn't seem to be using
the specified delimiter to split the messages. In fact I'm not sure what
it's using. The test file contains three messages but when I try to use
listtoarray and output the array there are 700 odd elements!
Any idea what's going on here? Any better way to do it?
Thanks,
Kay.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
