Replace...
<cfset newcsvfile = replace(newcsvfile, '"', '', "ALL")>
with
<cfset newcsvfile = replace(newcsvfile, '"', ',', "ALL")>
You are missing your delimiter on that string.
You could also replace your double quotations with some other character you
wont see in your csv file, like "~" and add it to your delimeters list in
your loop.
Jim Eisenhauer
On Tue, Jun 15, 2010 at 2:17 PM, Phillip Vector
<vec...@mostdeadlygame.com>wrote:
>
> 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:334589
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
