excel to sql database code example

Example 1: import excel to sql

You can also use OPENROWSET to import excel file in sql server.

SELECT * INTO Your_Table FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                        'Excel 12.0;Database=C:\temp\MySpreadsheet.xlsx',
                        'SELECT * FROM [Data$]')

Example 2: excel accessing sql database

'If the database is MS Access, the most performant way to run SQL
'queries from Excel VBA is by using DAO instead of ADO:

Function QuerySQL_DAO(sql$, dbFile$)
    Const DAO = "DAO.DBEngine.120"
    Set QuerySQL_DAO = CreateObject(DAO).OpenDatabase(dbFile).OpenRecordset(sql)
    QuerySQL_DAO.MoveLast
    QuerySQL_DAO.MoveFirst
End Function
   
'---------------------------------------------------------------------
    
MsgBox QuerySQL_DAO("SELECT * from Titanic", "C:\Ships.accdb").RecordCount         


  

'For all other databases, use ADO:

Function QuerySQL_ADO(sql$, dbFile$)
    Dim cnx
    Set cnx = CreateObject("ADODB.Connection")
    cnx.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbFile
    Set QuerySQL_ADO = CreateObject("ADODB.Recordset")
    With QuerySQL_ADO
        .CursorLocation = 3 'adUseClient
        .CursorType = 1     'adOpenKeyset
        .Open sql, cnx
    End With
End Function
    
'---------------------------------------------------------------------
    
MsgBox QuerySQL("SELECT * from Titanic", "C:\Ships.accdb").RecordCount

Example 3: convert excel to sql

Using SQLizer...
Step 1: Select Excel as your file type
Step 2: Choose the Excel file you want to convert to SQL
Step 3: Select whether the first row contains data or column names
Step 4: Type the name of the Excel worksheet that holds your data
Step 5: Input the cell range of the data that you want to convert
Step 6: Type in a name for your database table
Step 7: Convert your file!