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. |
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. |
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. |
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. |
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 |
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 |
All times are GMT +1. The time now is 01:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com