I had this same problem when trying to export my data from excel into an SQL db. There is some info on the IE or MSDN about how ADO with the Microsoft Jet OLE DB looks at the first like 8 entries in a column of the sheet and determines what format it should placed into the db as. See or read the part about Table Headers and Data Type
Excel ADO demonstrates how to use ADO to read and write data in Excel workbooks and
How To Query and Update Excel Data Using ADO From ASP
How To Query and Update Excel Data Using ADO From ASP
The way I worked around this was to create 2 sheets in my excel spreadsheet. The first sheet named tblRM_LabData has 3 columns Date,Signal_Name and Value, I formated these 3 columns as Date, General, General it has 38 rows or records. The second sheet named tblRM_LabGrabData has 3 columns Date,Signal_Name and Value, I formated these 3 columns as Date, General, Text it has 4 rows or records. I then wrote my VBA code queries as text strings using Microsoft Jet OLE to export the excel sheets as a db into an SQL db, see attached code below.
Code:
Private Sub ODBCConnection1()
'
' ODBCConnection1 Macro
' Macro recorded 7/11/2007 by Me
' Macro to update or append excel records into a SQL database
'
Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Data\Excel\File\LabData.xls;" & _
"Extended Properties=Excel 8.0"
'Import by using Jet Provider.
strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
"Server=YOURSERVERNAME:;Database=YOUR_DB_NAME;" & _
"UID=YOURUSERID;PWD=YOURPWD].XLImport9 " & _
"FROM [tblRM_LabData$]"
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff ' , adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff
cn.Close
Set cn = Nothing
End Sub
Private Sub ODBCConnection2()
'
' ODBCConnection2 Macro
' Macro recorded 7/11/2007 by Me
' Macro to update or append excel records into a SQL database
'
Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Data\Excel\File\LabData.xls;" & _
"Extended Properties=Excel 8.0"
'Import by using Jet Provider.
strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
"Server=YOURSERVERNAME:;Database=YOUR_DB_NAME;" & _
"UID=YOURUSERID;PWD=YOURPWD].XLImport8 " & _
"FROM [tblRM_LabGrabData$]"
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff ' , adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff
cn.Close
Set cn = Nothing
End Sub
Private Sub TransferDBData()
'
' TransferDBData Macro
' Macro recorded 7/11/2007 by Me
' Macro to insert lab data from the XLImports DB's into SQL tblRM_LabData and tblRM_Lab_Grab_Samples tables.
'
Application.ScreenUpdating = False
Dim cn As ADODB.Connection
Dim MySQLQuery1 As String
Dim MySQLQuery2 As String
Set cn = New ADODB.Connection ' Open ADODB Connection below
cn.Open "Provider=SQLOLEDB;Data Source=YOURSERVERNAME;" & _
"Initial Catalog=YOUR_DB_NAME;User ID=YOURUSERID;Password=YOURPWD" ' ADODB Connection Info
MySQLQuery1 = "INSERT INTO tblRM_Lab_Grab_Samples SELECT TOP 4 * FROM XLImport8 Drop Table XLImport8" ' Query1 string
MySQLQuery2 = "INSERT INTO tblRM_LabData SELECT TOP 38 * FROM XLImport9 Drop Table XLImport9" ' Query2 string
cn.Execute MySQLQuery1 ' Execute Query 1 Above
cn.Execute MySQLQuery2 ' Execute Query 2 Above
cn.Close ' Close ADODB Connection
Set cn = Nothing ' Set Connection to nothing
End Sub
Hope this helps
01-22-2008, 09:08 PM
ANY dialect question:
ANY answer.
Re: Getting Data From Excel
