Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 26th 04, 09:04 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2004
Posts: 4
Default ADO inserts text strings not numbers when table is empty

Bit of a stab in the dark this one. We're using standard ADO to access data
stored in an Excel workbook (don't ask why) and have a slight problem.
Consider a simple sheet containing the following heading and data:

Col1,Col2
1,3
2,4

If you execute a command like this:

Dim cmd As New ADODB.Command
cmd.Connection=ActiveConnection
cmd.CommandText="Insert Into [Data$] (Col1,Col2) Values (3,5)"
cmd.Execute

It works fine - the extra row is added contain the two *numbers* 3 & 5.
HOWEVER, if you delete the data rows out of the worksheet/table leaving just
the headings and carry out the same command, two *text* strings are written,
i.e. '3 and '5. I'm assuming that this is because Excel is unable to
determine the data types of the fields when the table is empty and therefore
defaults to text.

This can be confirmed by opening up a recordset on the empty table and
delving around the Fields collection. When the table is empty, Fields(0) is
a text string. However, then when with numbers, Fields(0) is a double.

Is there anyway around this? Some way to tell Excel the data types of the
fields in an empty Excel table?

Cheers, Rob.



  #2   Report Post  
Old March 2nd 04, 09:07 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2003
Posts: 599
Default ADO inserts text strings not numbers when table is empty

Rob

I'm stumped. I tried CAST and CONVERT but the SQL used by Excel doesn't
seem to support those functions.

I think what you'll need to do after your Execute statement is something
line

Sheets("Data").Range("A65536").End(xlUp).Value = _
CLng(Sheets("Data").Range("A65536").End(xlUp).Valu e)

But that would require that the destination workbook be open - which kind of
negates the benefit of using ADO in the first place.

Sorry I couldn't be more helpful.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Rob Nicholson" wrote in message
...
Bit of a stab in the dark this one. We're using standard ADO to access

data
stored in an Excel workbook (don't ask why) and have a slight problem.
Consider a simple sheet containing the following heading and data:

Col1,Col2
1,3
2,4

If you execute a command like this:

Dim cmd As New ADODB.Command
cmd.Connection=ActiveConnection
cmd.CommandText="Insert Into [Data$] (Col1,Col2) Values (3,5)"
cmd.Execute

It works fine - the extra row is added contain the two *numbers* 3 & 5.
HOWEVER, if you delete the data rows out of the worksheet/table leaving

just
the headings and carry out the same command, two *text* strings are

written,
i.e. '3 and '5. I'm assuming that this is because Excel is unable to
determine the data types of the fields when the table is empty and

therefore
defaults to text.

This can be confirmed by opening up a recordset on the empty table and
delving around the Fields collection. When the table is empty, Fields(0)

is
a text string. However, then when with numbers, Fields(0) is a double.

Is there anyway around this? Some way to tell Excel the data types of the
fields in an empty Excel table?

Cheers, Rob.




  #3   Report Post  
Old March 3rd 04, 09:41 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2003
Posts: 459
Default ADO inserts text strings not numbers when table is empty

Dick, With Excel as the data source, you must use MS Jet syntax. The
INT function works for me e.g.

cmd.CommandText="INSERT INTO [Data$] (Col1,Col2) Values
(INT(3),INT(5))"

--

"Dick Kusleika" wrote in message ...
Rob

I'm stumped. I tried CAST and CONVERT but the SQL used by Excel doesn't
seem to support those functions.

I think what you'll need to do after your Execute statement is something
line

Sheets("Data").Range("A65536").End(xlUp).Value = _
CLng(Sheets("Data").Range("A65536").End(xlUp).Valu e)

But that would require that the destination workbook be open - which kind of
negates the benefit of using ADO in the first place.

Sorry I couldn't be more helpful.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Rob Nicholson" wrote in message
...
Bit of a stab in the dark this one. We're using standard ADO to access

data
stored in an Excel workbook (don't ask why) and have a slight problem.
Consider a simple sheet containing the following heading and data:

Col1,Col2
1,3
2,4

If you execute a command like this:

Dim cmd As New ADODB.Command
cmd.Connection=ActiveConnection
cmd.CommandText="Insert Into [Data$] (Col1,Col2) Values (3,5)"
cmd.Execute

It works fine - the extra row is added contain the two *numbers* 3 & 5.
HOWEVER, if you delete the data rows out of the worksheet/table leaving

just
the headings and carry out the same command, two *text* strings are

written,
i.e. '3 and '5. I'm assuming that this is because Excel is unable to
determine the data types of the fields when the table is empty and

therefore
defaults to text.

This can be confirmed by opening up a recordset on the empty table and
delving around the Fields collection. When the table is empty, Fields(0)

is
a text string. However, then when with numbers, Fields(0) is a double.

Is there anyway around this? Some way to tell Excel the data types of the
fields in an empty Excel table?

Cheers, Rob.


  #4   Report Post  
Old March 3rd 04, 02:02 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2003
Posts: 599
Default ADO inserts text strings not numbers when table is empty


"onedaywhen" wrote in message
om...
Dick, With Excel as the data source, you must use MS Jet syntax. The
INT function works for me e.g.

cmd.CommandText="INSERT INTO [Data$] (Col1,Col2) Values
(INT(3),INT(5))"

--


It does not for me, using

Sub InsInto()

Dim Conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim stConn As String
Dim stSQL As String
Dim LAIdx As Long, i As Long


'Set up connection string
stConn = "DSN=Excel Files;DBQ=c:\Dick\ADOTest.xls"
stConn = stConn & ";DefaultDir=c:\Dick;DriverId=22;"
stConn = stConn & "MaxBufferSize=2048;PageTimeout=5;"

'Set up sql to insert
stSQL = "Insert Into [Sheet2$] (US) values (INT(8))"

'Create new connection
Set Conn = New ADODB.Connection

'Open the connection
Conn.Open stConn

'Execute the sql
Conn.Execute (stSQL)

Conn.Close

End Sub

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.


  #5   Report Post  
Old March 4th 04, 11:42 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2003
Posts: 459
Default ADO inserts text strings not numbers when table is empty

Dick,
I re-tested myself and kept getting mixed results. Sometimes I got the
apostrophe ('3), other times not (just 3). So I dug deeper...

I conclude the INT function indeed makes no difference. However, how
the worksheet was created seems to make a difference.

Here's my test function which demonstrates. I'm not entirely sure how
to recreate this 'manually' i.e. without using CREATE TABLE DDL.
Rather than use a recordset to test the data type, I open the workbook
and test the cell's Value property (and set a breakpoint and eyeball
it just to make sure!). Change the path to the file to suit and note
the code uses the Kill function on this file:

<Code-------------------
Option Explicit

Public Function IsInsertTypeString( _
Optional ByVal UseIntFunction As Boolean = False, _
Optional ByVal UseCreateTableInteger As Boolean = False _
) As Boolean

Const FILE_PATH As String = "C:\Tempo\TestDB.xls"
Const CON_PROVIDER As String = "Provider=Microsoft.Jet.OLEDB.4.0;"
Const CON_SOURCE As String = "Data Source="
Const CON_EXTENDED_HDR_YES As String = _
";Extended Properties='Excel 8.0;HDR=Yes'"
Const CON_EXTENDED_HDR_NO As String = _
";Extended Properties='Excel 8.0;HDR=No'"

Dim oWb As Excel.Workbook
Dim oCon As ADODB.Connection
Dim oRs As ADODB.Recordset
Dim strConHdrYes As String
Dim strConHdrNo As String
Dim strType As String

' Create new blank workbook
Kill FILE_PATH
Set oWb = Application.Workbooks.Add()
With oWb
.SaveAs FILE_PATH
.Close
End With

' Construct connection strings
strConHdrYes = CON_PROVIDER & CON_SOURCE & _
FILE_PATH & CON_EXTENDED_HDR_YES
strConHdrNo = CON_PROVIDER & CON_SOURCE & _
FILE_PATH & CON_EXTENDED_HDR_NO

Set oCon = New ADODB.Connection
With oCon

' Open connection (no headers)
.ConnectionString = strConHdrNo
.Open

' Create column headers
If UseCreateTableInteger Then
.Execute "CREATE TABLE Data" & _
" (Col1 INTEGER, Col2 INTEGER)"
Else
.Execute "CREATE TABLE Data" & _
" (Col1 CHAR(10), Col2 CHAR(10))"
End If
.Close
End With

Set oCon = New ADODB.Connection
With oCon

' Open connection (with headers)
.ConnectionString = strConHdrYes
.Open

' Insert data
If UseIntFunction Then
.Execute "INSERT INTO [Data$] (Col1, Col2) VALUES (INT(3),INT(5))"
Else
.Execute "INSERT INTO [Data$] (Col1, Col2) VALUES (3,5)"
End If

.Close

End With

Set oWb = Application.Workbooks.Open(FILE_PATH)

strType = TypeName(oWb.Worksheets("Data").Range("A2").Value)

oWb.Close

IsInsertTypeString = CBool(strType = "String")

End Function
</Code-------------------

In the Immediate Window:

? IsInsertTypeString(True,True)
False
? IsInsertTypeString(False,True)
False
? IsInsertTypeString(True,False)
True
? IsInsertTypeString(False,False)
True

--
"Dick Kusleika" wrote in message ...
"onedaywhen" wrote in message
om...
Dick, With Excel as the data source, you must use MS Jet syntax. The
INT function works for me e.g.

cmd.CommandText="INSERT INTO [Data$] (Col1,Col2) Values
(INT(3),INT(5))"

--


It does not for me, using

Sub InsInto()

Dim Conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim stConn As String
Dim stSQL As String
Dim LAIdx As Long, i As Long


'Set up connection string
stConn = "DSN=Excel Files;DBQ=c:\Dick\ADOTest.xls"
stConn = stConn & ";DefaultDir=c:\Dick;DriverId=22;"
stConn = stConn & "MaxBufferSize=2048;PageTimeout=5;"

'Set up sql to insert
stSQL = "Insert Into [Sheet2$] (US) values (INT(8))"

'Create new connection
Set Conn = New ADODB.Connection

'Open the connection
Conn.Open stConn

'Execute the sql
Conn.Execute (stSQL)

Conn.Close

End Sub



  #6   Report Post  
Old March 5th 04, 06:10 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2003
Posts: 599
Default ADO inserts text strings not numbers when table is empty


"onedaywhen" wrote in message
om...
Dick,
I re-tested myself and kept getting mixed results. Sometimes I got the
apostrophe ('3), other times not (just 3). So I dug deeper...

I conclude the INT function indeed makes no difference. However, how
the worksheet was created seems to make a difference.


Interesting. So if you identify the data type in CREATE TABLE, it will
work. I've never created a table using CREATE TABLE, so that's a new one on
me. I wonder if there's any way to define the data type in a manually
created table.

I know that ADO has a tendency to inherit the data type from the previous
row. Instead of Col1 and Col2, I made my headings 1234 and 5678, but I
couldn't get the SQL to read it as a column heading. It appears that column
headings have to be strings.

When I did an INSERT INTO using

stSQL = "Insert Into [Sheet2$] (1234) values (8)"

(after changing my heading to '1234) it put in 8 as '8. When I replaced it
with a numeric 8, future INSERT INTOs showed as numeric, not strings.

Next, I deleted all the rows of the table (sans header) and formatted Row 2
of the sheet as a number data type just using cell formatting. I didn't
enter anything into the cells in Row 2. The I hid Row 2. When I ran the
INSERT INTO again, it came in as numeric. So maybe for setting up tables
manually, you define the data type a cell format in the first row, hide it,
and everything's keen.

Dick




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
find and replace numeric strings in larger text strings Mr Molio Excel Worksheet Functions 8 November 9th 11 05:17 PM
Empty strings appear as zeros Paul Martin[_2_] Charts and Charting in Excel 5 May 9th 08 12:37 AM
How can I convert empty strings to empty cells? Shane Excel Discussion (Misc queries) 2 July 19th 05 12:10 PM
2 more questions about extracting numbers from text strings andy from maine Excel Discussion (Misc queries) 0 March 28th 05 09:47 PM
Clear Empty Strings Tim Tabor Excel Programming 3 October 6th 03 02:12 AM


All times are GMT +1. The time now is 08:58 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017