Solved

CF - How to strip quotes from csv file

Posted on 2014-11-04
12
231 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
need help in sql 4 67
T-SQL:  What won't it just collapse?  It would be the logical thing to do! 20 46
Sql Count with Select Distinct 4 27
Sql Query Datatype 2 19
Objective of This Article In 1990’s, when I was a budding software professional, I had a lot of confusion about which stream or technology, I had to choose to build my career. In those days, I had lot of confusion like whether to choose System so…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The purpose of this video is to demonstrate how to exclude a particular blog category from the main blog page. This is can be used when a category already has its own tab, or you simply want certain types of posts not to show up on the main blog. …
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…

867 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

24 Experts available now in Live!

Get 1:1 Help Now