JohnMac328
asked on
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
What would be the syntax in the VALUES area using listGetAt to remove the "
Any help is appreciated
<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>
What would be the syntax in the VALUES area using listGetAt to remove the "
Any help is appreciated
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
agx, see you're luck is changing, you beat me two times in a row!
Haha, not so much. Someone else beat me out on the other thread, and Sid beat me out yesterday ;-)
ASKER
You guys still there? I'm glad but surprised. So do we do a multiple or what?
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.
ASKER
Hi agx - I used GD's because it was shorter code and did it fine.
Thanks!
Thanks!
ASKER
Thanks!
Sounds good :)
Hmm, you probably should have shared points as they are both good answers
ASKER
I did ask :)
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 :)
<cfif left(record, 1) EQ '"' AND right(record, 1) EQ '"'>
<cfset record = mid(record, 2, len(record)-1))>
</cfif>
... split into array