Excel Datatype Mismatch 1

[dleewms]

Hi,

I'm using Crystal Reports 2013 Support Pack 11 on a 64-bit Windows 7 Professional Operating System. I'm creating a new report using an Excel spreadsheet as my data source. I am using the native Access/Excel (DAO) connection. The termination column in Excel is formatted as "Date". However, when browsing field data or placing field on to my report in CR, the field is a string. This occurs whether I use Excel 2013 or Excel 97-2003 workbook.

Any insight?

 

[SkipVought]

Hi,

The key bit of information is that Date in Excel is a NUMBER that is displayed as a date. That NUMBER is the number of days since December 31, 1899. So the [highlight #FCE94F]NUMBER[/highlight] value for the date Jan 1, 1900, is [highlight #FCE94F]1[/highlight].

Your BO system can't use the NUMBERS from Excel dates. It needs the STRING "1/1/1900" representation or some similar string.

My guess.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[kray4660]

And thus if you want that 'date' to be a date, you can convert in Crystal Reports to a date.

 

[dleewms]

Thanks for the responses. However, this field was once recognized as a date in an existing report and is currently embedded in several formulas within that same report. I would rather not create a brand new formula converting the string to a date within Crystal and then have to find each place where the original field was referenced and replace it with the new formula field.

By the way, Crystal can recognize cells formatted as dates in Excel. When the spreadsheet is used as the datasource, those fields have a datetime datatype in Crystal. I have another report which does this perfectly. Not sure why the date format is recognized and retained from one spreadsheet and not from the other. I was curious to know if anyone had encountered a similar issue.

Thanks!

 

[kray4660]

Not sure, I know that Crystal probably uses a similar engine as Excel does to try to determine the data type from the first few rows of data.

 

[SkipVought]

Okay, then do this in Excel to test whether you actually have DATES in Excel.

1) In Excel, select any cell containing a date in question
2) Right-click and select Format cells...
3) Select GENERAL in Category:
4) If the DATE does not change to a NUMBER, then you do not have DATES but STRINGS.

Post back for the next step if you have STRINGS in Excel.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[dleewms]

Skip, I previously formatted the entire column to date.

Following your instructions above, I right clicked on one cell which displayed the date 1/4/2019. In the Format Cells dialog box, I confirmed the category was "Date". I then changed the category to "General" and the number 43469 appeared.

I've attached screenshots to post.

I think perhaps it has something to do with the drivers on my machine......then I'm not sure about that because I would think it would affect all reports with Excel Data Sources and not this specific one.....

 

[SkipVought]

You appear to have exactly what I would expect to see with Excel dates.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein