Creating a Report from Excel

[#1]

I'm trying to create a report from an Excel spreadsheet and am having some issues with how Crystal is reading in the data. I have 7 fields that are all the same, I want them to be Numeric fields. However, when I import the Excel sheet as the data source, 4/7 of the fields import as string fields into Crystal. At first I didn't think this would be an issue, but some of the fields, and it doesn't seem to be consistently the "string" only or the "numeric" only fields, do not seem to be printing to the report at all - the space where they should be ends up blank. I have gone back to the Excel sheet several times to change the cell format to Numeric, but that has not helped. I also tried changing the format in Crystal, using ToNumeric(fieldname) or ToText(fieldname), and neither of those functions seem to work either.

Has anyone had this issue before or know how to fix it?

[Former Member]

Hi Jeremy,

Which Crystal Report version are you using?

Have you applied any service pack for Crystal Reports?

Which Operating system are you using?

Is the issue is with all the report or single report?

For testing purpose:

-Create new excell sheet with two columns one is numeric and other one string.

-Create report using thie excell sheet.

-Preview report and export.

-Now print report

Are you getting same results?

Have you tried to print other document through that printer is it working properly.

Regards,

Shweta

[Former Member]

I'm using Windows XP and Crystal 2008. I tried to create a new test file and had similar results. Even though I made one of the fields a "text" field, they both came over as numeric. All of the values are showing, but it didn't import as the correct field type.

[Former Member]

I'm using Excel 2003 - but when Crystal asks what Excel version I'm using it offers 3.0, 4.0, 5.0, or 8.0. I always choose Excel 8.0. I'm not sure if this correct, but just some more info to help me out....thanks.

[Former Member]

Hi ,

Could you explain more on when Crystal asks what Excel version.

How you are adding datasource?

From Database expert.

Click on Create new connection.

Database files

Select your Excel datasource sheet.

Now create report.

Place fields.

Preview report.

Export in XLS format.

Select destination.

See are you getting same exported xls as your datasource?

Regards,

Shweta

[Former Member]

Sorry - I was away for a few days. Here is how I added it as a datasouce: Create New Connection - Access/Excel(DAO) - Then it asks for a Database Name where I browsed to the excel spreadsheet - then for database type I selected Excel 8.0 and hit finish the wizard.

Is there a better way to connect to an excel sheet or any other suggestions?

[Former Member]

I got it straightened out. There were (*) in some of the fields which were giving me grief. Not on every field where there was an *, but some. I removed them any things worked as I had hoped they would from the beginning.