Solved

CF - How to strip quotes from csv file

Posted on 2014-11-04
12
229 Views
Last Modified: 2014-11-04
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
0
Comment
Question by:JohnMac328
  • 5
  • 4
  • 3
12 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 40422274
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
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 40422276
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 40422278
agx, see you're luck is changing, you beat me two times in a row!
0
 
LVL 52

Expert Comment

by:_agx_
ID: 40422290
Haha, not so much. Someone else beat me out on the other thread, and Sid beat me out yesterday ;-)
0
 

Author Comment

by:JohnMac328
ID: 40422358
You guys still there?   I'm glad but surprised.  So do we do a multiple or what?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 40422507
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:JohnMac328
ID: 40422577
Hi agx - I used GD's because it was shorter code and did it fine.

Thanks!
0
 

Author Closing Comment

by:JohnMac328
ID: 40422578
Thanks!
0
 
LVL 52

Expert Comment

by:_agx_
ID: 40422588
Sounds good :)
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 40422644
Hmm, you probably should have shared points as they are both good answers
0
 

Author Comment

by:JohnMac328
ID: 40422645
I did ask :)
0
 
LVL 52

Expert Comment

by:_agx_
ID: 40422693
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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…
Viewers will learn how the fundamental information of how to create a table.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now