Solved

Excel Database has one Column with Numbers and String Values - Crystal assigns the Column the Number Field Type

Posted on 2004-10-08
8
437 Views
Last Modified: 2006-11-17
I am using Crystal Reports to print a report from data in an excel sheet. One of the columns in the excel sheet has the follwoing values (for example):

Col A
5
4
3
2
Unknown
5
4

the word "unkown" is what is giving me problems.
When I connect the excel sheet - Crystal automatically makes Col A - Number - Field Type.
Question 1: How can I tell crystal to force the field type to : String [255]

Question 1: If I leave the field type as Number - how can I detect a blank value. In my report the data for the "unknown" value is just left blank - no zero - no NULL value - it is just blank. what value is that? Can I write a formula to detect it?

thanks





0
Comment
Question by:Callis_Ogles
  • 5
  • 3
8 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 12265096
Do you have any control over the spreadsheet?

What version of Crystal?

I am not sure if you can have Crystal detect the empty cell
Try setting convert null to 0 in the options

mlmcc
0
 

Author Comment

by:Callis_Ogles
ID: 12269304
Crystal Version 9.2.0.448
I do not have control over the spreadsheet.

The cells in ColA are not empty. I have 20% of the cells with numbers, 20% empty and then 20% with the word "Unknown".

When crystal imports the data it decides that ColA = FieldType of Number. When I browse the data in Crystal it shows me all the numbers but it does not show me anything else.

I tried to detect it using the isNull() function but it did not work.
I set Convert Null to Zero and it does work - but it does not solve my problem.

I need to be able to see all the data (including the word "unknown"). So the real question is - how do I tell Crystal to treat ColA as all string values - and then I can convert the strings to numbers when I need to.

Is there a way to force Crystal to set the FieldType for ColA to 'string' rather then 'number'??
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 12271073
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:Callis_Ogles
ID: 12293841
This link is ok but it still leaves me with the same problem.
I connect to the Excel database in Crystal using
Database
-- Database Expert
---- Create New Connection
------- Access/Excel (DAO)                  //Note - then I select the Excel 8.0 type and browse to the excel file and connect.


After I am connected I see the following in the field exploror (in crystal)
Field Explorer
-- Database Fields
---- Sheet1                                                                // Note - right click on the sheet and select 'Show Field Type'
------- Customer Name: String [255]
------- Customer Phone Number: String[255]                //Note - the 'String 255' is the field type
------- Customer ZipCode: Number                              //Note - here the field type is 'Number'

Is there a way to force Crystal to categorize the ZipCode column, imported from Excel, as 'String 255'



0
 
LVL 100

Expert Comment

by:mlmcc
ID: 12294816
I don't know.  If you could put a DUMMY record as the first line of the spreadsheet with a format for the fields then have the report import it but suppress it you might get what you want.

mlmcc
0
 

Author Comment

by:Callis_Ogles
ID: 12348296
Well, inserting a dummy record as the first row of the Excel File does work. In this way I can apparantly force Crystal to set the field type to: String[255] for every excel column.

However I am not really happy with this solution. Would upgrading to a newer version of Crystal make a difference?

Note: I am using Excel 9.0.3821, and when I attach to the excl sheet - the highes excel version crystal shows in Excel 8.0. I tried saving the excel file as Excel 5.0 and then attaching Excel 5.0 in crystal - that did not help.

I am very disapointed that crystal just assumes that this column is a number field - just because the first cell happens to contain a number.

The raw data comes to me in a comma delimited text file. And then I copy and paste the data into excel. Maybe I should copy and paste in an Access database. Any comments?

Otherwise this call is done -
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 12354421
Could be done that way and you could set the field to be text

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 12479121
Glad i could help

mlmcc
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

837 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