Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Export Excel tuncating leading zeros while export to excel from da | Setting up and Configuration of Excel | |||
How can I export a chart into crystal reports? | Charts and Charting in Excel | |||
How do I retain cell formatting when I export from Crystal Reports 11into a .xls file? | Excel Discussion (Misc queries) | |||
Generate Reports from Excel Using Seagate Crystal Reports PLs... H | Excel Programming | |||
How do I display leading zeros so I can export a fixed in Excel? | Excel Discussion (Misc queries) |