Solved

Excel Crystal Report Numeric Column Conversion

Posted on 2014-03-24
3
980 Views
Last Modified: 2014-03-25
I am using an Excel fie as datasource for Crystal Reports. In the Excel file is a column that contains Account Numbers that is mainly numbers but also have some cells with alpha values.  I formatted the cells / column as text value.  In Crystal Reports, I would use the Excel file ( DAO )  as datasource.  The Account Number column is somehow defined as Numbers
( when I add the field and browse data, the field is type : Number  and I only see numeric values ).  How do I get Crystal Reports to see this column as text and not number  ?
Only numbers are appearing on the report and the text values are being bypassed.
0
Comment
Question by:phiadmin
3 Comments
 
LVL 9

Accepted Solution

by:
BlueYonder earned 200 total points
ID: 39951531
Add an apostrophe before all of the account numbers.  This will change the format to text and will import correctly.
0
 
LVL 18

Assisted Solution

by:vasto
vasto earned 200 total points
ID: 39951569
The ODBC driver will check the first 7 lines and try to detect the type of the values. Add a non-numeric character ( for example a letter)  to the cells in the first row, which you want to be text and verify database in crystal reports. Once the database is verified you can remove the added letter and to restore the original value of the cell.
0
 

Author Closing Comment

by:phiadmin
ID: 39953928
I already knew about the apostrophe to convert numbers to text but was trying to around it since the already exist and did not want to manually convert each row.  Answer 2 regarding the ODBC driver provided an inside that I did not know.  I did convert the first few row by adding an alpha character but the then the crystal report ignore all the numeric rows.  I change the file values several times to try different options but ended up using a combination of both answers.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SAPE.heur.AE935 Visual Studio 3 39
Problem to file 3 67
Visual Studio: built-in keystroke automation 2 51
Need an intro to -- .Net SQL Authorization Manager 7 80
For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Viewers will learn the different options available in the Backstage view in Excel 2013.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

863 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

20 Experts available now in Live!

Get 1:1 Help Now