Solved

CF - How to strip quotes from csv file

Posted on 2014-11-04
12
245 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This guide will walk you through the essential considerations and tech stack for building scalable websites. Know how to grow your business the smart way!
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
The purpose of this video is to demonstrate how to set up basic WordPress SEO. This will be demonstrated using a Windows 8 PC. The plugin used will be WordPress SEO by Yoast. Go to your WordPress login page. This will look like the following: myw…
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…

632 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