Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Preserve Quotes for CSV export Post Reply Post New Topic
Author Message
thummel1
Senior Member
Senior Member
Avatar

Joined: 27 Apr 2012
Location: United States
Online Status: Offline
Posts: 140
Quote thummel1 Replybullet Topic: Preserve Quotes for CSV export
     Posted: 17 Jun 2014 at 9:31am
Hi I am using Crsytal 2008. I have a report that requires certain fields be formatted so that when it's exported to CSV, the leading zeroes are preserved. (Examples of field needing this are the SSN and the bank routing number). FYI, exporting to Excel first is not an option I've been told. This is a goverment document and the goal is to have no modifications upon export.
 
If you have a solution, I'd love to hear it. The other solution I was told would work for the end user is to put quotes around the data fields so when exporting to CSV, quotes would actually appear around the SSN or Bank Routing Number.  I'm having issues with that as well. The SSN needs to be formatted without the dashes, so I have the following formula in there for SSN to remove the dashes:
 
replace({EMPLOYEE.FICA_NBR},'-','').
 
Now, how do I get quotes to appear around the resulting number? 
 
I have another formula that uses an If/Then statement:
 
If ({TAXGROUP.HEAD_PROC_LEV}) = "TEST" then "12345" else
 
I can't put double quotes in this because I get an error message. I tried asteriscs and they stay, but I'm still waiting for the end user to see if asteriscs would suffice.
 
Any help on these quote issues would be great!Confused


Edited by thummel1 - 17 Jun 2014 at 9:33am
"Press any key to continue. Where's the 'Any' Key?" ~Homer Simpson
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 17 Jun 2014 at 10:16am
chrw(32) is the quote, so a formula like this work...you could also build a function into the report

chrw(32) + {table.field} + chrw(32)

HTH
IP IP Logged
thummel1
Senior Member
Senior Member
Avatar

Joined: 27 Apr 2012
Location: United States
Online Status: Offline
Posts: 140
Quote thummel1 Replybullet Posted: 18 Jun 2014 at 2:58am
I tried this and when I exported to CSV, the leasding zeros were not saved nor were there quotes around the data. Should either have happened?
"Press any key to continue. Where's the 'Any' Key?" ~Homer Simpson
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 18 Jun 2014 at 6:48am
The really big problem is that CR does not really let you control the export.

I don't know about leading 0, if it is a text field it should respect it...though again, CR does not give much control over exports.
IP IP Logged
thummel1
Senior Member
Senior Member
Avatar

Joined: 27 Apr 2012
Location: United States
Online Status: Offline
Posts: 140
Quote thummel1 Replybullet Posted: 18 Jun 2014 at 6:48am
OK, thanks, that's what I'm concluding as well ,as I've checked with other organizations that must run this same report. We will just have to work within these limitations. I appreciate your efforts!
"Press any key to continue. Where's the 'Any' Key?" ~Homer Simpson
IP IP Logged
Erik
Groupie
Groupie
Avatar

Joined: 05 Dec 2013
Online Status: Offline
Posts: 50
Quote Erik Replybullet Posted: 18 Jun 2014 at 7:00am
Excel also strips leading zeroes when saving to CSV.

You can retain this data by exporting to tab delimited text, but then you'd need a script to find and replace tabs with commas, and rename to CSV.
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 18 Jun 2014 at 12:16pm
How did you check the csv?  Excel will definitely modify the look.  Did you open it in a text editor (Notepad)?
IP IP Logged
thummel1
Senior Member
Senior Member
Avatar

Joined: 27 Apr 2012
Location: United States
Online Status: Offline
Posts: 140
Quote thummel1 Replybullet Posted: 19 Jun 2014 at 1:53am

I did open the CSV in Notepad and the formatting is perfect. I instructed the end user to open the CSV file in Notepad to validate the leading zeros are still there versus opening the file directly. They don't want to do this. They want to open the file and look a the data, make changed as needed, close it, reopen it etc. I've explained the formatting will change if it's opened directly. At this point I believe this is an end-user issue. Perhaps someday this format issue will change, but for now the'll need to save a copy to Excel if they want to open the file. Thank you all!
"Press any key to continue. Where's the 'Any' Key?" ~Homer Simpson
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 19 Jun 2014 at 5:54am
Here is an idea that they will not like, but it will work.  Change the Windows settings so that csv gets opened in Notepad instead of Excel. LOL
IP IP Logged
thummel1
Senior Member
Senior Member
Avatar

Joined: 27 Apr 2012
Location: United States
Online Status: Offline
Posts: 140
Quote thummel1 Replybullet Posted: 19 Jun 2014 at 6:09am
Brilliant!Clap And you are right...they would not like that since they work with a lot of CSV files, but this is the only one (so far) with this formatting need.
 
I'm going to tuck this tip away in my "tips" folder though! Thank you!
"Press any key to continue. Where's the 'Any' Key?" ~Homer Simpson
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.