asked on

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

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

Mike McCracken

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

Callis_Ogles

ASKER

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'??

Mike McCracken

This may be useful
http://support.businessobjects.com/communityCS/TechnicalPapers/reportingoffexcel.pdf.asp

mlmcc

Callis_Ogles

ASKER

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'

Mike McCracken

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

Callis_Ogles

ASKER

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 -

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike McCracken

Glad i could help

mlmcc