Please see the following code for Exporting data from Excel to SQL.
Step 1 before inserting this code into you VBE (Alt + F11) is to enable the library reference for Outlook so that you have access to Outlook classes for the below code.
Press (Alt + F11) keys, Go to Tools, References, and make sure "Microsoft ActiveX Data Objects 6.1 Library". if you have an earlier or later version than 6.1 that should be fine as well.
How my worksheet looks like
How my Database table looks like after the import
Sub ExportToSQL() SQLsheet.Activate 'Turns off screenupdating for faster script. Application.ScreenUpdating = False 'Imports the "SQL" class and library references called ActiveX Data Objects Library. Dim conn As ADODB.Connection Dim rs As ADODB.Recordset Dim sConnString As String 'Create the connection string to our server. sConnString = "Provider=SQLOLEDB;" & _ "Data Source=Servername;" & _ "Initial Catalog=Database;" & _ "Trusted_connection=yes;" 'Provider = Fix variable. 'Data Source = Type in your servername. 'Initial Catalog = Type in your database name. '2 options of connecting to the server. '1) "Trusted_connection=yes;" = This refers to Windows authentication setting in your database. '2) "User Id=myUsername;Password=myPassword;" = If you are not using Windows authentication, you need to specify your username and password. 'Specify import range to loop. Dim Rng As Range EndPlace = Range("A3").End(xlDown).End(xlToRight).Address(False, False) Set Rng = Application.Range("A3:" & EndPlace) Dim Row As Range 'Create the Connection and Recordset objects. Set conn = New ADODB.Connection Set rs = New ADODB.Recordset 'Setting the name of my SQL table. MyTable = "TestTable" 'Change this to your SQL table name. 'Open the connection and execute. conn.Open sConnString '*********************Inserts new data********************* 'Insert new rows. Throws error on the issue row. For Each Row In Rng.Rows If Row.Cells(1).Value <> "" Then Nr = Nr + 1 'Collect my info here so my 'SQL string looks a bit nicer below. PersonID = Row.Cells(1).Value 'I can recommend using a Primary Key here instead. FirstName = Row.Cells(2).Value LastName = Row.Cells(3).Value Address = Row.Cells(4).Value SQLstringValue = "(" & PersonID & ",'" & FirstName & "', '" & LastName & "', '" & Address & "')" SQLstring = "insert into " & MyTable & " values " & SQLstringValue 'Debug.Print SQLstring ', great way to see if your string turns out correctly. Row.Cells(1).Activate 'Activates row for errorhandler. On Error GoTo ErrorCode: Set rs = conn.Execute(SQLstring, Records) 'Execute upload. End If Next Row '*****************END - Inserts new data******************* ' Clean up, closing the serverconnection. If CBool(conn.State And adStateOpen) Then conn.Close Set conn = Nothing Set rs = Nothing 'Turns on screenupdating now that we are done. Application.ScreenUpdating = True If Nr > 0 Then MsgBox ("Nice Job, Import was successful! " & Nr & " rows were imported") Else MsgBox ("Please change your settings. " & Nr & " rows were imported") End If Exit Sub 'Stops scripts and displays which row to fix. ErrorCode: MsgBox ("Incomplete upload. Please fix row: " & ActiveCell.Row & " and try again") End Sub
Now if you have a lot of rows in your excel sheet and need to optimize the upload time I would suggest the following depending on the volume:
Reduce the SQL uploads by uploading for example a 1000 rows in a single statement. this would require some re write of the sql statement but hopefully the above should give some guidance.
Use formula r1c1 in the rightest available column to to create a formula that would translate to sql code for each row. Then concatenate all rows and add starting and ending syntax for the sql part. Find more about Formula R1C1 here: https://www.pls-fix-thx.com/post/excel-vba-r1c1-absolute-reference
Use python or simply upload the document.
Download Excel Workbook here.
Feel free to download my prepared workbook containing this exact code to get a jumpstart on your project.
Learn more about VBA here for all my posts: https://www.pls-fix-thx.com/vba
Learn more about Python here for all my posts: https://www.pls-fix-thx.com/python
If you have found this article or website helpful. Please show your support by visiting the shop below.