Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 458
  • Last Modified:

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





0
Callis_Ogles
Asked:
Callis_Ogles
  • 5
  • 3
1 Solution
 
mlmccCommented:
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
 
Callis_OglesAuthor Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Callis_OglesAuthor Commented:
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
 
mlmccCommented:
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
 
Callis_OglesAuthor Commented:
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
 
mlmccCommented:
Could be done that way and you could set the field to be text

mlmcc
0
 
mlmccCommented:
Glad i could help

mlmcc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now