Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel Crystal Report Numeric Column Conversion

Posted on 2014-03-24
3
Medium Priority
?
1,191 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 600 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 600 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Viewers will learn the different options available in the Backstage view in Excel 2013.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

782 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