Solved

CF - How to strip quotes from csv file

Posted on 2014-11-04
12
233 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Slow Connectivity over ODBC 8 35
SQL Help 27 45
Help creating a spatial object in SQL Server 4 22
SQL Server Connection String through a VPN 8 23
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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…
The purpose of this video is to demonstrate how to integrate Mailchimp with WordPress, by placing a Mailchimp signup form on a WordPress Page or Post. This will be demonstrated using a Windows 8 PC. Mailchimp will be used. Log into your Mailchi…
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…

777 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