I'm extracting external numeric data from tables where the field type is
defined as a fixed length string (ie there are extra spaces in the field if
the length of the number is less than the field size).

Using ADO and the getRows method of a recordset to pass the results to an
array before populating the range in the worksheet results in the strings
being automatically converted to a number (except for entries that are
definitely strings, such as all spaces of na etc).

That was a feature of the .value method of a range I was pleasantly
surprised by.

However, if I use the .copyFromRecordset to pass the results directly to a
range, the string for each number is written to each cell (eg "4.5 "
instead of 4.5 that happens when using .value)

My knowledge of SQL is limited, and I can't see anything in the recordset
property that would allow for converting the type of the retrieved data
from strings to numerics (really - just trimming the data of whitespace).

my current workarround is to write the data with .copyFromRecordset, read
the data back into an array and write it out immediately.

Is it possible to avoid that last step either with some SQL or something in
ADO that I've missed?

cheers,
Christophe

ADO will make a best-guess at the data type, and it must be thinking these
are strings.

Why not just continue with the first way that surprised you and worked for
you?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Chris Short" wrote in message
...
I'm extracting external numeric data from tables where the field type is
defined as a fixed length string (ie there are extra spaces in the field
if
the length of the number is less than the field size).

Using ADO and the getRows method of a recordset to pass the results to an
array before populating the range in the worksheet results in the strings
being automatically converted to a number (except for entries that are
definitely strings, such as all spaces of na etc).

That was a feature of the .value method of a range I was pleasantly
surprised by.

However, if I use the .copyFromRecordset to pass the results directly to a
range, the string for each number is written to each cell (eg "4.5 "
instead of 4.5 that happens when using .value)

My knowledge of SQL is limited, and I can't see anything in the recordset
property that would allow for converting the type of the retrieved data
from strings to numerics (really - just trimming the data of whitespace).

my current workarround is to write the data with .copyFromRecordset, read
the data back into an array and write it out immediately.

Is it possible to avoid that last step either with some SQL or something
in
ADO that I've missed?

cheers,
Christophe

Hi Chris,

What provider are you using? SQL Server, Access, other? What does your SQL
statement look like? I'm asking because I think it may be easier/more
efficient to have the db convert the string to numeric - that way, your
recordset field will be numeric, and Excel should treat the corresponding
column as numeric.

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

Chris Short wrote:
I'm extracting external numeric data from tables where the field type
is defined as a fixed length string (ie there are extra spaces in the
field if the length of the number is less than the field size).

Using ADO and the getRows method of a recordset to pass the results
to an array before populating the range in the worksheet results in
the strings being automatically converted to a number (except for
entries that are definitely strings, such as all spaces of na etc).

That was a feature of the .value method of a range I was pleasantly
surprised by.

However, if I use the .copyFromRecordset to pass the results directly
to a range, the string for each number is written to each cell (eg
"4.5 " instead of 4.5 that happens when using .value)

My knowledge of SQL is limited, and I can't see anything in the
recordset property that would allow for converting the type of the
retrieved data from strings to numerics (really - just trimming the
data of whitespace).
my current workarround is to write the data with .copyFromRecordset,
read the data back into an array and write it out immediately.

Is it possible to avoid that last step either with some SQL or
something in ADO that I've missed?

cheers,
Christophe

What provider are you using?

Ingres

What does your SQL statement look like?

very simple:

SELECT value, period
FROM relevant table
WHERE (identifier = X) or (identifier = Y) or etc
ORDER BY period

(and the number of identifiers can be very large)

I'm asking because I think it may be easier/more efficient to have the db
convert the string to numeric

That's what I was hoping.

Bob Phillips asked why try to use .copyFromRecordset. It's because some
testing shows that the speed differences are several orders of magnitude.
I'm trying to get people to change some habits, so this speed carrot is
important for me.

thanks,
Christopher

Hi Chris,

Chris Short wrote:
What does your SQL statement look like?

very simple:

SELECT value, period
FROM relevant table
WHERE (identifier = X) or (identifier = Y) or etc
ORDER BY period

You could try a CAST on the value column - I'm assuming that's the one that
comes back as a string value? If all values within that column will be
numeric, you could do this:

SELECT CAST(value AS decimal(18,4)), period
FROM relevant table
WHERE (identifier = X) or (identifier = Y) or etc
ORDER BY period

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

Jake said:

You could try a CAST on the value column - I'm assuming that's the one
that
comes back as a string value?

Yup.

Thank you very much for that. It's not working properly in that I'm getting
an error:

"Incorrect column expression: 'CAST(value AS Decimal(20,10))'"

(I also tried casting it as float4 and float8 with the same error message.

But that's enough for me to know where to start to look.

much appreciated,
Christopher

Hi Chris,

Chris Short wrote:
You could try a CAST on the value column - I'm assuming that's the
one that
comes back as a string value?

Yup.

Thank you very much for that. It's not working properly in that I'm
getting an error:

"Incorrect column expression: 'CAST(value AS Decimal(20,10))'"

(I also tried casting it as float4 and float8 with the same error
message.
But that's enough for me to know where to start to look.

It looks like the CAST function won't work in Ingres. Try using float4 or
similar instead:

SELECT float4(value), period
FROM relevant table
WHERE (identifier = X) or (identifier = Y) or etc
ORDER BY period

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]