Solved

Excel Crystal Report Numeric Column Conversion

Posted on 2014-03-24
3
1,116 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

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…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
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 …
Suggested Courses

635 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