Datatype problems when Excel is one of the datasources

[Timbe]

HI all:
I have a report that uses an Excel Spreadsheet for the list of id numbers to look up. I link it (Excel DSN) to an Oracle table (DSN) in our warehouse, and it all works pretty well.

Here's the problem: some of the datatypes being returned from Oracle are screwey. I convert some of the Excel fields to text, and use them to lookup a text field in Oracle. In addition to the matched filed, I retrieve some other fields. The returned numbers from other fields in Oracle are being translated, for example, what is usually a "0" or a "1" is now a "49" or a "50".

Anyone got any theories? (I'm in Crystal Developer 8.5), we use Enterprise Professional too. Oracle is v9.

--Timbe

 

[synapsevampire]

I'd guess that the problem is with Excel, not Oracle.

How do you *convert* and link the Excel values to Oracle? This is suspect...

Anyway, Excel assumes datatypes based on the first row of the spreadsheet, so a standard cheat is to place a dummy row which qualifies the data. A presumed "1" in column A may result in a data type of number, etc., causing problems.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[dgillz]

SV is right on here. Ihave experienced this many times and have used the same solution of a dummy row that indicates the datatype.

I do not understand how a "1" and a "0" get converted to 49 and 50 however. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com