Excel ODBC Number/Text problem

[CarolineS]

Hi,

I'm connecting to an Excel spreadsheet via ODBC, trying to link it to some SQL tables. The field on which I can link to the SQL tables is "Sales Rep" which, although it is a number, is a String field.

So I altered my data in Excel to make "Text" cells which happen to contain numbers. But when I connect to it via ODBC that set of data has been read as Number again.

I saw a similar post in the archives about memo vs. text fields...Is this a Crystal thing, or an ODBC thing? I'm using CR8.5

thanks in advance for any help,
Caroline

 

[chrisjev]

Hi there,

This is an EXCEL thing actually. The bahaviour will be the same in OLEDB as well, outside of Crystal reports the column will be defined as the original format before the 1st piece of data was entered into that column.

Once this is changed after data is entered the drivers do not seem to recognize these changes.

Some possible work arounds are, import the Excel spreadsheet into the SQL Database and convert the datatype in a Stored Proc.

The second is to move that entire column into MS Word, Insert a new column, define it as TEXT and then paste the data back in from Word.

The third option is to insert the Excel spreadsheet into Crystal through a subreport and link it base on a formula that will TOTEXT the field and convert it into a string field for linking purposes.

CJ a.c.e.

 

[kenhamady]

Welcome Chrisjev. You should add your thoughts about the ACE to one of our recent threads on the topic.

Another option is two add a new column to the spreadsheet, copy a formula like:

=Text (B3, "0&quot

to convert the numeric column to text values. And then use the new column. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[CarolineS]

Thanks to you both for your suggestions. I tried your idea, Ken, which is an (easier) variation on chrisjev's second suggestion.

And that worked fine - however! I just discovered that with Crystal 8.x there is that nice Excel add-in (mine was not enabled because Excel had been re-installed after Crystal Reports). So I brought the Excel data over using the Crystal Reports wizard in Excel, saved the report, and then added any SQL-via-ODBC tables I wanted. Simple! No more ODBC Excel!

Caro