Data Types when using Jet.OLEDB.4.0 with Excel datasource

  • Thread starter Thread starter Martin Newman
  • Start date Start date
M

Martin Newman

I have a fairly complex export routine that outputs data from access (an adp
actually) to a
Excel spreadsheet (v8 but actually deployed to and works happily with
Excel 2003). This is done in code rather than as an export, it is quite
a complex export and can't be done as a "straight" export, I don't think
(OK might be possible with a very complicated query and/or temporary
tables).

Anyway, it is a two part process in the first part my program creates the
field headers (i.e. row 1, a set of field names, obviously strings) and in
the second it works its way through a recordset extracting data items,
manipulating them and then writing what is required to the excel sheet.

And it works jolly well.

Except that any data that is "obviously" numeric is shoved out as strings
e.g 24 is output as '24, presumably because the oledb provider sees the
row headers (field names) as being strings and says the whole column must
be strings. This is more of an annoyance than anything else, but it is a
serious annoyance and so is there a way round this?

M
 
Hi Martin,

IME if you need full control over what goes on in Excel it's best to
automate the Excel object model and address the cells directly, rather
than to treat the worksheet as a table.

That said, how are you creating the worksheet and inserting the field
names? Without having tested it thoroughly I get the impression that if
you create a worksheet, put the field names in the top row and then
execute queries to append the data you'll get the numbers-as-text effect
you're having. But if you create the worksheet with a make-table query
that returns the appropriate field types, subsequent append queries will
append number field values as numbers. This seems to be the case even if
the initial make-table query created a table with zero rows.

Here are the queries I just tried this with:

Make-table query that returns no records:

SELECT ID, FirstName, LastName INTO
[Excel 8.0;HDR=Yes;Database=C:\Temp\XX.xls;].[Sheet1]
FROM AddrNew WHERE False;

Append query:
INSERT INTO
[Excel 8.0;HDR=Yes;Database=C:\Temp\XX.xls;].[Sheet1]
SELECT ID, FirstName, LastName
FROM AddrNew;
 
Well actually I am not even using a query - I am picking the data for the
spreadsheet up from loads of places in a way that I really can't even begin
to think how I would do in (T)SQL - it;s an adp by the way, the make table
query isn't an option AFAIAA - so I am using ADO addnew and filling the
recordset manually.

I am much less familiar with the Excel object model but what you are saying
is that I should be able to use that to ebnsure numeric values are numeric -
any clues, anyone, on how I do that? This may of course be obvious but I
have rarely played with Excel at the programatioc level.

Thanks for your helpful reposnse, John

M


John Nurick said:
Hi Martin,

IME if you need full control over what goes on in Excel it's best to
automate the Excel object model and address the cells directly, rather
than to treat the worksheet as a table.

That said, how are you creating the worksheet and inserting the field
names? Without having tested it thoroughly I get the impression that if
you create a worksheet, put the field names in the top row and then
execute queries to append the data you'll get the numbers-as-text effect
you're having. But if you create the worksheet with a make-table query
that returns the appropriate field types, subsequent append queries will
append number field values as numbers. This seems to be the case even if
the initial make-table query created a table with zero rows.

Here are the queries I just tried this with:

Make-table query that returns no records:

SELECT ID, FirstName, LastName INTO
[Excel 8.0;HDR=Yes;Database=C:\Temp\XX.xls;].[Sheet1]
FROM AddrNew WHERE False;

Append query:
INSERT INTO
[Excel 8.0;HDR=Yes;Database=C:\Temp\XX.xls;].[Sheet1]
SELECT ID, FirstName, LastName
FROM AddrNew;

I have a fairly complex export routine that outputs data from access (an adp
actually) to a
Excel spreadsheet (v8 but actually deployed to and works happily with
Excel 2003). This is done in code rather than as an export, it is quite
a complex export and can't be done as a "straight" export, I don't think
(OK might be possible with a very complicated query and/or temporary
tables).

Anyway, it is a two part process in the first part my program creates the
field headers (i.e. row 1, a set of field names, obviously strings) and in
the second it works its way through a recordset extracting data items,
manipulating them and then writing what is required to the excel sheet.

And it works jolly well.

Except that any data that is "obviously" numeric is shoved out as strings
e.g 24 is output as '24, presumably because the oledb provider sees the
row headers (field names) as being strings and says the whole column must
be strings. This is more of an annoyance than anything else, but it is a
serious annoyance and so is there a way round this?

M
Click to expand...
Click to expand...
 
Forget that question - just seen one of your other postings


Martin Newman said:
Well actually I am not even using a query - I am picking the data for the
spreadsheet up from loads of places in a way that I really can't even begin
to think how I would do in (T)SQL - it;s an adp by the way, the make table
query isn't an option AFAIAA - so I am using ADO addnew and filling the
recordset manually.

I am much less familiar with the Excel object model but what you are saying
is that I should be able to use that to ebnsure numeric values are numeric -
any clues, anyone, on how I do that? This may of course be obvious but I
have rarely played with Excel at the programatioc level.

Thanks for your helpful reposnse, John

M


John Nurick said:
Hi Martin,

IME if you need full control over what goes on in Excel it's best to
automate the Excel object model and address the cells directly, rather
than to treat the worksheet as a table.

That said, how are you creating the worksheet and inserting the field
names? Without having tested it thoroughly I get the impression that if
you create a worksheet, put the field names in the top row and then
execute queries to append the data you'll get the numbers-as-text effect
you're having. But if you create the worksheet with a make-table query
that returns the appropriate field types, subsequent append queries will
append number field values as numbers. This seems to be the case even if
the initial make-table query created a table with zero rows.

Here are the queries I just tried this with:

Make-table query that returns no records:

SELECT ID, FirstName, LastName INTO
[Excel 8.0;HDR=Yes;Database=C:\Temp\XX.xls;].[Sheet1]
FROM AddrNew WHERE False;

Append query:
INSERT INTO
[Excel 8.0;HDR=Yes;Database=C:\Temp\XX.xls;].[Sheet1]
SELECT ID, FirstName, LastName
FROM AddrNew;

I have a fairly complex export routine that outputs data from access
Click to expand...
Click to expand...
(an
Click to expand...
 
Glad to help.


Forget that question - just seen one of your other postings


Martin Newman said:
Well actually I am not even using a query - I am picking the data for the
spreadsheet up from loads of places in a way that I really can't even begin
to think how I would do in (T)SQL - it;s an adp by the way, the make table
query isn't an option AFAIAA - so I am using ADO addnew and filling the
recordset manually.

I am much less familiar with the Excel object model but what you are saying
is that I should be able to use that to ebnsure numeric values are numeric -
any clues, anyone, on how I do that? This may of course be obvious but I
have rarely played with Excel at the programatioc level.

Thanks for your helpful reposnse, John

M


John Nurick said:
Hi Martin,

IME if you need full control over what goes on in Excel it's best to
automate the Excel object model and address the cells directly, rather
than to treat the worksheet as a table.

That said, how are you creating the worksheet and inserting the field
names? Without having tested it thoroughly I get the impression that if
you create a worksheet, put the field names in the top row and then
execute queries to append the data you'll get the numbers-as-text effect
you're having. But if you create the worksheet with a make-table query
that returns the appropriate field types, subsequent append queries will
append number field values as numbers. This seems to be the case even if
the initial make-table query created a table with zero rows.

Here are the queries I just tried this with:

Make-table query that returns no records:

SELECT ID, FirstName, LastName INTO
[Excel 8.0;HDR=Yes;Database=C:\Temp\XX.xls;].[Sheet1]
FROM AddrNew WHERE False;

Append query:
INSERT INTO
[Excel 8.0;HDR=Yes;Database=C:\Temp\XX.xls;].[Sheet1]
SELECT ID, FirstName, LastName
FROM AddrNew;

On Fri, 13 Aug 2004 10:58:25 +0100, "Martin Newman"

I have a fairly complex export routine that outputs data from access
Click to expand...
(an
adp
actually) to a
Excel spreadsheet (v8 but actually deployed to and works happily with
Excel 2003). This is done in code rather than as an export, it is quite
a complex export and can't be done as a "straight" export, I don't think
(OK might be possible with a very complicated query and/or temporary
tables).

Anyway, it is a two part process in the first part my program creates the
field headers (i.e. row 1, a set of field names, obviously strings) and in
the second it works its way through a recordset extracting data items,
manipulating them and then writing what is required to the excel sheet.

And it works jolly well.

Except that any data that is "obviously" numeric is shoved out as strings
e.g 24 is output as '24, presumably because the oledb provider sees the
row headers (field names) as being strings and says the whole column must
be strings. This is more of an annoyance than anything else, but it is a
serious annoyance and so is there a way round this?

M
Click to expand...
Click to expand...
Click to expand...
 
Back
Top