I have the following code........
Insert into Accounting_Temp
(
Id
,EnrollmentNumber
,EnrollmentChannel
,StatusName
,Status
,PromoCode
,QualificationType
,CreationDate
,LastModified
,State
,ZipCode
,UnqualifiedReason
)
Values
(
<cfloop index="data" list="#i#"
delimiters="XXXXXX">
<cfswitch expression="#count#">
<cfcase value="1">
'#data#'
</cfcase>
<cfcase value="2">
, #data#
</cfcase>
<cfcase value="3">
, '#data#'
</cfcase>
<cfcase value="4">
, '#data#'
</cfcase>
<cfcase value="5">
, #data#
</cfcase>
<cfcase value="6">
, '#data#'
</cfcase>
<cfcase value="7">
, '#data#'
</cfcase>
<cfcase value="8">
<cfset DBDate =
#CREATEODBCDATETIME(data)#>
, #DBDate#
</cfcase>
<cfcase value="9">
<cfset DBDate =
#CREATEODBCDATETIME(data)#>
, #DBDate#
</cfcase>
<cfcase value="10">
, '#data#'
</cfcase>
<cfcase value="11">
, '#data#'
</cfcase>
<cfcase value="12">
<cfif data is 'N/A'>
<cfset
LongReasonFlag = 0>
, '#data#'
<cfelse>
<cfset
LongReasonFlag = 1>
, '#data#
</cfif>
</cfcase>
<cfcase value="13">
#data#
</cfcase>
<cfcase value="14">
#data#
</cfcase>
<cfcase value="15">
#data#
</cfcase>
</cfswitch>
<cfset count=#count#+1>
</cfloop>
<cfif LongReasonFlag EQ 0>
)
<cfelse>
')
</cfif>
That I want to take the following data....
d8f7e3e1-2367-42b3-8f12-01460a5781e0,3679187,Web,Qualified,2,mdnow33,ProgramParticipationQualification,2010-05-28,2010-05-28,MD,21225-1502,
63cdd862-fe1d-4099-8833-01d4a24662dd,3678275,Fax,Unqualified,3,CMO0001006,ProgramParticipationQualification,2010-05-28,2010-05-28,MO,63628-8616,"The
form ProgramForms has been rejected because of the following reason:
2001 -Copy of proof illegible (name cannot be read)
"
19e927d2-00ea-41c6-b0d4-022d7ce77f03,3678144,DirectMail,Qualified,2,FEED,ProgramParticipationQualification,2010-05-28,2010-05-28,MI,49401-9516,
When I feed it through, I get this output.
d8f7e3e1-2367-42b3-8f12-01460a5781e0XXXXXX3679187XXXXXXWebXXXXXXQualifiedXXXXXX2XXXXXXmdnow33XXXXXXProgramParticipationQualificationXXXXXX2010-05-28XXXXXX2010-05-28XXXXXXMDXXXXXX21225-1502XXXXXX
63cdd862-fe1d-4099-8833-01d4a24662ddXXXXXX3678275XXXXXXFaxXXXXXXUnqualifiedXXXXXX3XXXXXXCMO0001006XXXXXXProgramParticipationQualificationXXXXXX2010-05-28XXXXXX2010-05-28XXXXXXMOXXXXXX63628-8616XXXXXX
The form ProgramForms has been rejected because of the following
reason: 2001 -Copy of proof illegible [name cannot be read]
19e927d2-00ea-41c6-b0d4-022d7ce77f03XXXXXX3678144XXXXXXDirectMailXXXXXXQualifiedXXXXXX2XXXXXXFEEDXXXXXXProgramParticipationQualificationXXXXXX2010-05-28XXXXXX2010-05-28XXXXXXMIXXXXXX49401-9516XXXXXX
4f9d6257-bbe6-4664-b4cd-02e5dcd7bdefXXXXXX3679987XXXXXXDirectMailXXXXXXQualifiedXXXXXX2XXXXXXFEEDXXXXXXProgramParticipationQualificationXXXXXX2010-05-28XXXXXX2010-05-28XXXXXXMIXXXXXX49930-2170XXXXXX
(XXXXXX is the delimiter)
The issue I have is right here...
"1 -Copy of proof illegible [name cannot be read] 19e927d2-00ea-"
It's bleeding over the other record ahead of it. I know the issue..
There is a record, then on the next line is a " and then it goes to
the next line. I've tried removing all the quotes and such and even
forcing a carriage return...
<cfset newcsvfile = replace(csvfile, ',#chr(13)#', ',N/A#chr(13)#',
"ALL")>
<cfset newcsvfile = replace(csvfile, '"', '"#chr(13)#', "ALL")>
<cfset newcsvfile = replace(newcsvfile, '(', '[', "ALL")>
<cfset newcsvfile = replace(newcsvfile, ')', ']', "ALL")>
<cfset newcsvfile = replace(newcsvfile, ',,', ',0,', "ALL")>
<cfset newcsvfile = replace(newcsvfile, '"', '', "ALL")>
<cfset newcsvfile = replace(newcsvfile, ',', 'XXXXXX', "ALL")>
But it's just not accepting it and I'm going nuts here..
Does anyone know a simple way to say, "Hey.. If there are carriage
returns with only a " on the line, skip to the next one. The second
replace listed doesn't seem to want to do it.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334588
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
