CF - How to strip quotes from csv file

We have a new file being sent from another company.  This CSV file has a quote at the beginning of each record and one at the end.  I need to strip these out or the import will not work.  I can't find a decent example to modify the array.  Here is the current code

		<cfloop index="record" list="#csvfile#" delimiters="#chr(10)##chr(13)#">

         <cfset arr = ListToArray(record, ",", true)>
         <cfif arrayLen(arr) gte 5>
               <cfset b_id= arr[1] >
               <cfset b_name= arr[2] >
               <cfset b_date= arr[3] >
               <cfset b_nav= arr[4] >
               <cfset b_ytd= arr[5] >
 
					 <cfif NOT listFindNoCase("HEADER RECORD,Fund Number,TRAILER RECORD", trim(b_id))> 
						<cfquery name="importcsv" datasource="dsn">
						INSERT INTO table (FundNumber,Fund_Name,Date,nav_now,YTD) 
                		 VALUES 
                		('#b_id#','#b_name#','#b_date#','#b_nav#','#b_ytd#') 
						</cfquery>  
          			 </cfif> 
		</cfif>
		</cfloop>

Open in new window


What would be the syntax in the VALUES area using listGetAt to remove the "

Any help is appreciated
JohnMac328Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
gdemariaConnect With a Mentor Commented:
If you know you want all quotes out, even if they belong within the data, you can simply do this..

<cfset record = replace(record,'"','',"all")>

Open in new window

place it just inside your loop
0
 
_agx_Commented:
You mean at the beginning and end of the line? Maybe this is too simplistic, but can't you just remove the first and last character of the string?  Then split it into fields.  Untested, but something like...



<cfif left(record, 1) EQ '"' AND right(record, 1) EQ '"'>
    <cfset record = mid(record, 2, len(record)-1))>
</cfif>
... split into array
0
 
gdemariaCommented:
agx, see you're luck is changing, you beat me two times in a row!
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
_agx_Commented:
Haha, not so much. Someone else beat me out on the other thread, and Sid beat me out yesterday ;-)
0
 
JohnMac328Author Commented:
You guys still there?   I'm glad but surprised.  So do we do a multiple or what?
0
 
_agx_Commented:
Did you try both of them? I wasn't sure if the entire line starts and ends a quote - or the individual fields. If it's the latter, then use GD's code. Otherwise, try my example.
0
 
JohnMac328Author Commented:
Hi agx - I used GD's because it was shorter code and did it fine.

Thanks!
0
 
JohnMac328Author Commented:
Thanks!
0
 
_agx_Commented:
Sounds good :)
0
 
gdemariaCommented:
Hmm, you probably should have shared points as they are both good answers
0
 
JohnMac328Author Commented:
I did ask :)
0
 
_agx_Commented:
GD - In this case, I'd go with yours, not mine. Honestly, I wasn't sure about the goal. So my example has excess code he didn't end up needing, so yours is better :)
0
All Courses

From novice to tech pro — start learning today.