SQL Recipes a FREE cookbook of SQL queries and examples - View Single Post

#3 (**permalink**) 06-11-2008, 08:36 PM

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