Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Crystal Reports export to Excel - Loss of Leading Zeros

On Monday, November 1, 2004 at 11:48:04 AM UTC-4, CR-Tech wrote:
Crystal Enterprise is the web-based front-end server which displays
stored/linked Crystal reports to my target audience. I use an export
function that sends the report data to a .csv file so my users can open and
manipulate it in Excel. The file I'm trying to create MUST maintain the data
types of the underlying data fields, but currently is not retaining leading
zeros.

Leading zeros in various fields (ID#/SSN, Zip Codes, etc.) are not
recognized and retained by Excel because it seems the incoming field data is
seen as numerical instead of text. Using known pre-Excel formatting tricks
employing various combinations of using an apostrophe before the data,
enclosing the data in quotation marks (before and after), and using space(s)
in combination with the ' or " characters proves ineffective.

I am trying to correctly force-format the data containing leading zeros as
TEXT, BEFORE it reaches Excel, in order to retain these leading zeros.

Please offer assistance or solutions. Your help is most appreciated. Thanks.


If I'm not mistaken you can instead of adding the field into the report you can create a formula field with a "ToText({fieldname})" type of language and add that to your report instead. This will ensure the data is pulled into the Crystal Report as text and should export in the same manner.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default Crystal Reports export to Excel - Loss of Leading Zeros

On Monday, November 1, 2004 at 11:48:04 AM UTC-4, CR-Tech wrote:
Crystal Enterprise is the web-based front-end server which displays
stored/linked Crystal reports to my target audience. I use an export
function that sends the report data to a .csv file so my users can open and
manipulate it in Excel. The file I'm trying to create MUST maintain the
data types of the underlying data fields, but currently is not retaining
leading zeros.

Leading zeros in various fields (ID#/SSN, Zip Codes, etc.) are not
recognized and retained by Excel because it seems the incoming field data is
seen as numerical instead of text. Using known pre-Excel formatting tricks
employing various combinations of using an apostrophe before the data,
enclosing the data in quotation marks (before and after), and using space(s)
in combination with the ' or " characters proves ineffective.

I am trying to correctly force-format the data containing leading zeros as
TEXT, BEFORE it reaches Excel, in order to retain these leading zeros.

Please offer assistance or solutions. Your help is most appreciated.
Thanks.


If I'm not mistaken you can instead of adding the field into the report you
can create a formula field with a "ToText({fieldname})" type of language and
add that to your report instead. This will ensure the data is pulled into the
Crystal Report as text and should export in the same manner.


IMO, the import functionality in Excel basically isn't as efficient as it could
be! That said, I prefer to use VBA to import data into pre-formatted tables.
(Only requires a single mouse click rather than all the nonsense associated
with using the Import Wizard!) This ensures the data 'type' for table fields is
what I want/need it to be...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Export Excel tuncating leading zeros while export to excel from da RHBKV Setting up and Configuration of Excel 1 July 15th 09 01:48 PM
How can I export a chart into crystal reports? reberlein Charts and Charting in Excel 0 November 26th 08 10:23 PM
How do I retain cell formatting when I export from Crystal Reports 11into a .xls file? [email protected] Excel Discussion (Misc queries) 0 January 18th 08 07:52 PM
Generate Reports from Excel Using Seagate Crystal Reports PLs... H Chinx21 Excel Programming 0 May 5th 07 02:23 AM
How do I display leading zeros so I can export a fixed in Excel? World Referee and accountant Excel Discussion (Misc queries) 2 January 3rd 05 05:18 PM


All times are GMT +1. The time now is 03:28 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"