Posted in

Export data from Excel to SQL Server

by Tomas Lind••106

Need to get data from Excel to SQL Server?

Normally this can be achieved by using SSIS. Excel can be used as a source and/or target in a Data flow. But there may be situations where you need more granular control over which data gets imported to SQL Server.

Perhaps there is a process where a user is involved in deciding which data gets inserted to the database, and the import may be initiated be the user. Or maybe some advanced calculations needs to be made to decide which values get inserted.

Anyway, using VBA (Visual Basic for Applications) in Excel macro, it is rather simple to open a connection to a SQL Server database, generate the statements that will select values from the Excel cells and import them to a table. This blog post will show a simple example of how to export data from Excel to SQL Server.

I am using Excel 2013 and SQL Server 2012 in the example.

First, we need a database and a table. In the example I am going to use a simple Customer table with a primary key, and first and last name of the customer:

CREATE DATABASE ExcelDemo
GO

USE ExcelDemo
GO

CREATE TABLE dbo.Customers
	(
		CustomerId VARCHAR(10) NOT NULL CONSTRAINT CustomerId_PK PRIMARY KEY CLUSTERED
		,FirstName VARCHAR(100) NOT NULL
		,LastName VARCHAR(100) NOT NULL
	)
GO

Next, we need a matching “table” in Excel:

Excel To SQL Server VBA Macro Sample Table
Excel To SQL Server VBA Macro Sample Table

We are going to create a button that, when clicked, imports the data from the Excel sheet into the Customer table.

To create a button in Excel, we first need to enable the Developer menu in Excel (if not done already). Open the “Excel Options” menu, and select the “Customize Ribbon” page. Make sure the “Developer” checkbox is set and click “Ok”:

Excel Options Developer Checkbox
Excel Options Developer Checkbox

Now we can create the button. From the “Developer” menu, select “Insert” and “Button”:

Excel Button
Excel Button

Draw a button anywhere in the sheet and an “Assign macro” dialog will appear. To create a macro for the button, click “New”:

Excel Assign Macro To Button
Excel Assign Macro To Button

This will open a code window for Visual Basic for Applications (VBA):

Excel Macro VBA code window
Excel Macro VBA code window

Replace all the code in the window with the code below:

Sub Button1_Click()
    
    Dim conn As New ADODB.Connection
    Dim iRowNo As Integer
    Dim sCustomerId, sFirstName, sLastName As String
 
    With Sheets("Sheet1")
           
        'Open a connection to SQL Server
        conn.Open "Provider=SQLOLEDB;Data Source=ASUSBOOK\SQL2012;Initial Catalog=ExcelDemo;Integrated Security=SSPI;"
           
        'Skip the header row
        iRowNo = 2
           
        'Loop until empty cell in CustomerId
        Do Until .Cells(iRowNo, 1) = ""
            sCustomerId = .Cells(iRowNo, 1)
            sFirstName = .Cells(iRowNo, 2)
            sLastName = .Cells(iRowNo, 3)
               
            'Generate and execute sql statement to import the excel rows to SQL Server table
            conn.Execute "insert into dbo.Customers (CustomerId, FirstName, LastName) values ('" & sCustomerId & "', '" & sFirstName & "', '" & sLastName & "')"

            iRowNo = iRowNo + 1
        Loop
           
        MsgBox "Customers imported."
           
        conn.Close
        Set conn = Nothing
            
    End With

End Sub

On row 10 above, you need to replace my server and instance (ASUSBOOK\SQL2012) with your own.

We also need to add a reference to “Microsoft ActiveX Data Objects 2.8 Library” from the “Tools” -> “References” menu:

Excel VBA Add Reference
Excel VBA Add Reference

Close the code window and save the Excel workbook. To include the macro in the save, you need to save as “Excel Macro-Enabled Workbook” with an *.xlsm extension.

Now press the button (“Button 1”). If everything works as expected, the “Customers imported” message box should be shown:

Excel Macro Message Box
Excel Macro Message Box

If you look in the table in SSMS, our customers are imported as expected:

Excel Data To SQL Server Table
Excel Data To SQL Server Table

Note that this is just a simple demonstration. For simplicity I have hard coded the connection string and there is no error handling.

[yellow_box]
***** UPDATE *****

This simple demonstration tries to insert new records into the table each time the macro is run.

But I have been getting a lot of questions like:

* If the customer already exists in the database, how do I update it?
* How can I remove a customer from the database if it is removed from Excel?

So I have expanded this demonstration to use the T-SQL Merge statement instead. Now this solution can handle inserts, updates and deletes that you do in the Excel file. Check out the blog post here!
[/yellow_box]

Tomas Lind - Consulting services as SQL Server DBA and Database Developer at High Coast Database Solutions AB.

106 thoughts on “Export data from Excel to SQL Server”

  3. Hey Thomas,

    Will this work for all datatype, or should we declare different datatypes in the macro for different datatypes in the sql server table.

    1. That’s a good question 🙂 As you can see I only used VARCHAR datatypes in SQL Server and String datatypes in the macro precisely to avoid datatype issues which would otherwise make the solution more complex. I wanted to avoid that in this post since it was meant to show the basics only. But in a more “serious” project I would definitely use more exact datatypes than strings.

      However a more complex solution is required then since there isn’t an exact match between SQL Server datatypes and the datatypes in VBA. Further, you’d need some way to handle datatype constraint violations. Perhaps via error handling in the VBA macro.

      1. Can you suggest same for numeric or float no…
        I try the same but always type error 13
        Showing…
        2: suppose in you example static three fields one is
        In forumal .
        I’d column name is id_2 as I’d*Len(lastname) then what will be the solution

  11. Hi Tomas,

    Thanks for this post.
    I have one problem with macro. Can you please tell me how to insert URL value into SQL table column. When i run macro all columns value of excel gets imported in SQL except column which has URL links which is shown as blank in SQL table.
    I have used string data type to store reference. URL Value sample “http://www.w3schools.com/html/html_headings.asp”
    Please advice where I am wrong.

    Thanks,
    Rohan

    1. Hi, to update a specific cell in a VBA macro is easy. The following code changes cell A1 to the value of string sSomeValue:

      Cells(1, 1).Value = sSomeValue

      But if you want to update the value of a cell to a value from a database, that would require a longer answer…. But perhaps the following (untested) code could point you in the right direction:

      Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim fldEach As ADODB.Field
Dim sSomeValue, sConn, sSQL As String

sConn = "Provider=SQLOLEDB;Data Source=<YourInstance>;Initial Catalog=<YourDatabase>;Integrated Security=SSPI;"

Set rs = New ADODB.Recordset
conn.Open sConn
        
sSQL = "SELECT 'ABC' AS SomeValue"
Set rs = conn.Execute(sSQL)
rs.MoveFirst
            
For Each fldEach In rs.Fields
    sSomeValue = fldEach.Value
Next
        
Cells(1, 1).Value = sSomeValue

      Regards,
      Tomas

  13. Hi Tomas, i’m not too knowledgable about vba and mysql but this seems be what i’m looking for. If I wanted to point the above code to my sql database/server to import data do I change the sConn= part? If so which piece of info exactly? Thanks in advnace

    1. Hi Andre, I don’t have MySQL installed, but yes the sConn parameter should be changed. To create a connection string you can for instance create an ordinary text file, rename the file extension from .txt to .udl and double click the file. Then create a working connection to your MySQL database. When done open the file in notepad to see the connection string. All this assumes you have the necessary drivers installed to connect to MySQL.

      Regards,
      Tomas

  14. Hi Tom,
    Thanks for the code .I had a small doubt ,is it possible to export the entire table or atleast one column at a time as sending data cell by cell may be time consuming

    Regards,
    Ananya

    1. Hi Ananya,

      Actually it is not cell by cell, it is row by row. But regarding how to send the entire table, the traditional solution would be to use TVP’s (Table-Valued Parameters) for this but I don’t think it can be done in VBA.

      An alternative would be to use XML, but I’m not sure that would improve performance really…

      Another solution that would probably be the best performance wise, would be to turn it around and instead pull the data from SQL Server. Either by using openrowset, or by using SSIS. If you have the possibility.

      1. I tried the above example at http://www.sql-server-helper.com/tips/read-import-excel-file-p03.aspx and received the error message:

        Msg 7308, Level 16, State 1, Line 1
        OLE DB provider ‘Microsoft.Jet.OLEDB.4.0’ cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

        I am using:
        SQL Server 2008 R2
        Office 2010 SP-1 32-bit
        Windows 7 Ultimate SP1 64-bit

        My query looks like this:
        SELECT *
        FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
        ‘Excel 8.0;Database=D:\WORK_STUFF\EXCEL_SPREADSHEETS\testopenrowset.xls;IMEX=1’,
        ‘SELECT * FROM [Sheet1$]’)

        I also tried it as Excel 2010 version: testopenrowset.xlsx and received the exact same error.

  15. Hello,

    it works for me.

    I have a doubt over here. i want to set some permissions for SQL table. if i did, then in that case this is not working for me.

    And if i remove the permissions this works.

    but i want to set permissions to SQL tables. please give me some solution.

    thanks

  16. I am Getting Error in Connection String. The Error Message is “SQL Server Does Not Exist or Access Denied”. I am using SQL SERVER 2014. and Excel is 2013.

    1. Hi, are you sure the server reference in the connection string is correct? That is, following the format SERVERNAME or SERVERNAME\INSTANCENAME? If that seems to be correct, I’d say there is some permission issue.

      1. Hi

        conn.Open “Provider=SQLOLEDB;Server=local;Database=excelSheetUsingMacros;Integrated Security=SSPI;

        This is the Conection string which i gave.

  17. Hi Tomas,

    Above code is really helpful but I am experiencing an issue. If I have duplicated customer id ,it’s not exporting.
    Is there any option to allow that.

    Thanks in advance for your knowledgeable tips.

    Warmest Regards,
    Mou

  18. Hi Tomas,

    Above codes are really helpful but if I wanted to export duplicate customer id , then it’s not working.
    It would be highly appreciated, if you could provide any suggestion.

    Warm Regards,
    Mou

    1. Hi, yes that is because CustomerId is the primary key on the table. But the table is just for demonstration purposes. To be able to insert duplicates, use the following table instead:

      CREATE TABLE dbo.Customers
    (
        CustomerId VARCHAR(10) NOT NULL
        ,FirstName VARCHAR(100) NOT NULL
        ,LastName VARCHAR(100) NOT NULL
    )
GO

      But I stongly suggest that you use primary keys on tables however…

      Regards
      Tomas

      1. Thanks a lot! Now I am experiencing another issue,if I am importing a sql data to excel and then
        after modifying something in excel ,export the same data in sql,then instead of replacing, it”s appending.
        Is that possible to replace.Any help wiuld be highly appreciable.

        Kind Regards,
        Mou

  19. Hiya,

    Firstly, thank you so much, this code has saved me a massive headache! It works almost exactly as I needed to. Which leads me to…

    Secondly, I have birth and death date columns in my database, but not everyone put into the database is dead (it’d be easier if they were.), and whenever I pass a table to SQL that has a blank death date, it sets the date to 01/01/1900… Is there any way to make it pass null?

    Thanks!

    1. Hi Molly, you can use the T-SQL function NULLIF to set it to NULL if the value is 01/01/1900. So if I wanted to insert NULL if the “FirstName” column was “abc” in my example above, it would look like this:

      conn.Execute "insert into dbo.Customers (CustomerId, FirstName, LastName) values ('" & sCustomerId & "', NULLIF('" & sFirstName & "', 'abc'), '" & sLastName & "')"

      Regards
      Tomas

      1. Thanks very much!

        This has worked perfectly where birthdate is unknown, but for some reason it’s not working for deathdate.

        This is my line of code:

        conn.Execute “insert into dbo.Components (ArtistID, Forename, Surname, Nationality, BirthDate, DeathDate) values (‘” & sArtistID & “‘, NULLIF(‘” & sForename & “‘, ”), ‘” & sSurname & “‘, NULLIF(‘” & sNationality & “‘, ”), NULLIF(‘” & sBirthDate & “‘, ”), NULLIF(‘” & sDeathDate & “‘, ”))”

        Could it be because of the double )) at the end? I can’t see why it wouldn’t be working…

        1. It is easier to verify the syntax if you extract the SQL that will be executed. To do that, simply replace “conn.Execute” with “MsgBox”. Then you’ll get a dialog that you can copy from (Ctrl+C) and then test in management studio. Hopefully, if there are any errors they will be more visible.

          I actually tried your SQL string that way, and with some changes this:

          MsgBox "insert into dbo.Components (ArtistID, Forename, Surname, Nationality, BirthDate, DeathDate) values ('" & sArtistID & "', NULLIF('" & sForename & "', ''), '" & sSurname & "', NULLIF('" & sNationality & "', ''), NULLIF('" & sBirthDate & "', ''), NULLIF('" & sDeathDate & "', ''))"

          …generated this SQL:

          insert into dbo.Components (ArtistID, Forename, Surname, Nationality, BirthDate, DeathDate) values ('A', NULLIF('B', ''), 'C', NULLIF('D', ''), NULLIF('E', ''), NULLIF('F', ''))

          … which seems correct to me (forget the A,B etc, I hardcoded the variables).

          You can go mad composing strings i VBA 🙂

          There might be a character problem also, check so that you use ” and ‘. Or is that a browser thing… ***edit*** yes, it is… use the characters from the boxes above.

          1. So I finally got around to fixing the problem… I used the MsgBox like you suggested, and saw that for some reason the macro was reading the cells in the death date column as 0, but blank in the others. I got around this by changing the code to read:

            NULLIF(‘” & sDeathDate & “‘, ‘0’), which worked a treat!

            Thanks again!

            I know this is a little off topic, but I don’t suppose you know how to get around date formats between Excel and SQL?

            Another upload macro I will be using requires the date of the upload to be recorded, and I used sUploaded = Now()
            This worked fine on 11th of March, but wouldn’t work on 14th… I was confused for ages until I realised it’s a difference in date conventions… so SQL had actually stored the date as the 3rd November.

            I’ve tried many ways to get around this, but they don’t seem to work, so I’m sure I must be missing something?

            Again, thank you so much!!

          2. Hi, dates in SQL Server are stored as integers internally. When displayed the format depends on local settings on the computer. For the date of the upload you can use a default value in the SQL Server table column like this: 

            UploadDate DATETIME2 DEFAULT(GETDATE()) NOT NULL

            Then you wont have to specify a value for that column in the insert statement.

            Dates are represented differently in SQL Server and Excel. So perhaps it is sometimes better to convert the date to a string before inserting in to the table.

  20. Hi Tomas Lind,

    Thank you so much for the code! It works flawlessly and has saved me a lot of time and efforts in getting my job done.

    Quick question – After importing the excel data to the SQL server table, if there are any data/ field changes made to a particular set of rows – I might need an option to refresh the existing data in the SQL table. Say, i have 5 customer ID’s alone in my excel table and i imported them to the SQL using your code. Now, i added 2 more customer ID’s to the excel. How will I be able to append these extra customer ID’s to the SQL table without having to import all 7 again creating 5 duplicates in the table?

    It would be of much needed help if you could provide us a coding sample for the requested data.
    Thanks much

    1. Hi, glad to hear the code is useful 🙂

      You can check that the records doesn’t exists like this:

      conn.Execute "if not exists (select * from dbo.Customers where CustomerId = " & sCustomerId & ")" & " begin insert into dbo.Customers (CustomerId, FirstName, LastName) values ('" & sCustomerId & "', '" & sFirstName & "', '" & sLastName & "') end"

      Regards
      Tomas

      1. Hi Thomas,

        Thanks for your prompt response. Quick question- I am trying to push a set of Record ID’s (250+) into the SQL server from Excel using the below mentioned code. However, i see that the order of the record ID’s in excel doesn’t match with the order of record IDs in the database table. Any thoughts and help on cleaning up the code to send data in the exact same order would be highly appreciated :

        Sub ImportSQL()

        Dim conn As New ADODB.Connection
        Dim iRowNo As Integer
        Dim sSysID As String

        With Sheets(“BTF_Baseline”)

        ‘Open a connection to SQL Server
        conn.Open “Provider=SQLOLEDB;Data Source=****;Initial Catalog=****;Integrated Security=SSPI”

        conn.Execute “delete from ****”

        ‘Skip the header row
        iRowNo = 3

        ‘Loop until empty cell in CustomerId
        Do Until .Cells(iRowNo, 1) = “”
        sSysID = .Cells(iRowNo, 1)

        ‘Generate and execute sql statement to import the excel rows to SQL Server table
        conn.Execute “insert into ****(SysID) values (‘” & sSysID & “‘)”

        iRowNo = iRowNo + 1
        Loop

        MsgBox “SysID imported.”

        conn.Close
        Set conn = Nothing

        End With
        End Sub

        1. Hi, the rows in Excel are processed in order. In SQL Server however, rows are only stored in a specific order if there is a clustered index on the table. Such as the CustomerId column in my example.

          Regards
          Tomas

  21. Thanks for your post as it has helped tremendously.
    Is there a limitation on number of items defined and imported /
    In the following VBA, which written as is, works, I have 8 more cell/rows to reference.
    At the moment I am using static number values to have the query run.

    But if I add on more item definition, i.e.

    fttonsmovedtpd = .cells(44, IColNo).Value

    even though it is Dim’ed previously
    and make no other change to the sql,
    I get an error.

    Any ideas?

    Sub InsertForecastCol()

    Dim conn As New ADODB.Connection
    Dim iColNo As Integer
    Dim iRowNo As Integer
    Dim fGeneral As String
    Dim fdays, fthrs, fcoga, fcogb, fplmt, fplmttcu, fplmtrcu, fplmtqlt, fplktpd, frcvcr, fcl_plt, fcl_plttcu As Integer
    Dim fcl_pltrcu, fcl_pltrehtpd, fstons, fstonstcu, fstonsrcu, fstonstpd, fcltonssp, fcltonssptcu, fcltonssprcu As Integer
    Dim fcltonssptpd, fwtons, fwttonsm, fwtonstpd, fwttonsmtpd, frehorep, frehoret, frehclt, fttonsmoved, fttonsmovedtpd As Integer
    Dim fplmrlbs, fcplmrlbs, fpllbs, fcllbs, fstklbs, frcv, frcvg As Integer

    On Error GoTo ErrHandle

    With Sheets(“Fcst”)

    ‘Open a connection to SQL Server
    conn.Open “Provider=SQLOLEDB.1;” & _
    “Data Source=xxxxxxxx;Initial Catalog=yyyyyy;” & _
    “User ID=111111;PWD=22222;” & _
    “Connect Timeout=50”

    conn.Execute “DELETE FROM [dbo].[Forecast]”

    ‘Start with data on Column 6

    iColNo = 6

    ‘Loop until empty cell in yearfull
    Do Until .Cells(6, iColNo) = “”

    fGeneral = .Cells(5, iColNo).Value
    fdays = .Cells(6, iColNo).Value
    fthrs = .Cells(7, iColNo).Value
    fcoga = .Cells(8, iColNo).Value
    fcogb = .Cells(9, iColNo).Value
    fplmt = .Cells(11, iColNo).Value
    fplmttcu = .Cells(12, iColNo).Value
    fplmtrcu = .Cells(13, iColNo).Value
    fplmtqlt = .Cells(14, iColNo).Value
    fplktpd = .Cells(15, iColNo).Value
    frcvcr = .Cells(16, iColNo).Value
    fcl_plt = .Cells(18, iColNo).Value
    fcl_plttcu = .Cells(19, iColNo).Value
    fcl_pltrcu = .Cells(20, iColNo).Value
    fcl_pltrehtpd = .Cells(21, iColNo).Value
    fstons = .Cells(23, iColNo).Value
    fstonstcu = .Cells(24, iColNo).Value
    fstonsrcu = .Cells(25, iColNo).Value
    fstonstpd = .Cells(26, iColNo).Value
    fcltonssp = .Cells(28, iColNo).Value
    fcltonssptcu = .Cells(29, iColNo).Value
    fcltonssprcu = .Cells(30, iColNo).Value
    fcltonssptpd = .Cells(31, iColNo).Value
    fwtons = .Cells(33, iColNo).Value
    fwtonstpd = .Cells(34, iColNo).Value
    fwttonsm = .Cells(36, iColNo).Value
    fwttonsmtpd = .Cells(37, iColNo).Value
    frehorep = .Cells(39, iColNo).Value
    frehoret = .Cells(40, iColNo).Value
    frehclt = .Cells(41, iColNo).Value
    fttonsmoved = .Cells(43, iColNo).Value

    conn.Execute “insert into [dbo].[Forecast] ([Time Period], [Days], [Total hrs], [COGa], [COGb], [PL Mined Tons], [PL Mined Tons TCU] ” & vbCrLf & _
    “, [PL Mined Tons RCU], [PL Mined Tons QLT], [PL Mined Tons PL KTPD] ” & vbCrLf & _
    “, [PL Mined Tons % Recovery to Crusher], [CL-> PL Tons], [CL-> PL Tons TCU], [CL-> PL Tons RCU], [CL-> PL Tons Rehandle CL KTPD] ” & vbCrLf & _
    “, [Stacked Tons], [Stacked Tons TCU], [Stacked Tons RCU], [Stacked Tons YPD], [CL Tons (Stockpile)], [CL Tons (Stockpile) TCU] ” & vbCrLf & _
    “, [CL Tons (Stockpile) RCU], [CL Tons (Stockpile) Mined CL KTPD], [Waste Tons], [Waste TPD],[Total Tons Mined], [Total tons TPD] ” & vbCrLf & _
    “, [Rehandel PL ore %], [Rehandel PL ore], [Rehandled CL], [Total Tons moved], [Total Tons Moved TPD], [PL Mined Rec Lbs] ” & vbCrLf & _
    “, [Cumulative PL Mined Rec Lbs], [PL Mined total Lbs], [CL MINED (crushRec) Lbs], [STACKED Rec lbs], [Recovery (recoverable lbs/TCU lbs)], [Recoverable grade]) ” & vbCrLf & _
    ” values (‘” & fGeneral & “‘,'” & fdays & “‘,'” & fthrs & “‘,'” & fcoga & “‘,'” & fcogb & “‘,'” & fplmt & “‘,'” & fplmttcu & “‘,'” & fplmtrcu & “‘ ” & vbCrLf & _
    “,'” & fplmtqlt & “‘,'” & fplktpd & “‘,'” & frcvcr & “‘,'” & fcl_plt & “‘,'” & fcl_plttcu & “‘,'” & fcl_pltrcu & “‘ ” & vbCrLf & _
    “, ‘” & fcl_pltrehtpd & “‘,'” & fstons & “‘,'” & fstonstcu & “‘,'” & fstonsrcu & “‘,'” & fstonstpd & “‘,'” & fcltonssp & “‘,'” & fcltonssptcu & “‘ ” & vbCrLf & _
    “,'” & fcltonssprcu & “‘,'” & fcltonssptpd & “‘,'” & fwtons & “‘,'” & fwtonstpd & “‘,’9999’,'” & fwttonsmtpd & “‘,'” & frehorep & “‘,'” & frehoret & “‘ ” & vbCrLf & _
    “,'” & frehclt & “‘,'” & fttonsmoved & “‘,’9999′,’7′,’8′,’9′,’1′,’2′,’3′,’4’)”

    iColNo = iColNo + 1

    Loop

    MsgBox “Forecast imported.”

    GoTo CleanUp

    ErrHandle:
    MsgBox (“An error occurred. Check your input and try again.”)

    CleanUp:
    Application.StatusBar = False
    If conn.State Then conn.Close

    End With

    End Sub

      1. Actually I discovered it was a data type mismatch in the sql table, so once I changed the data type it worked, so issue resolved.
        Thank you as it was your original post that led me to this project. Not much info on excel to sql via vba on the net.

  23. This is exactly what I’ve been searching for. I can’t wait to go to work on Monday and try it out. Thanks for a great post!

  24. When running the sql below, I only get 17 rows of data instead of the 121 rows that exist.

    Can you see anything that is incorrect?

    With Sheets(“Summary”)

    ‘Open a connection to SQL Server
    conn.Open “Provider=???;” & _
    “Data Source=??????;Initial Catalog=??????;” & _
    “User ID=????;PWD=?????;” & _
    “Connect Timeout = 3000”

    conn.Execute “delete from [dbo].[SAF_Forecast_Weekly]”

    iRowNo = 257

    ‘Loop until cell = 999
    Do Until .Cells(iRowNo, 1) = 999

    fUpdate = .Cells(10, 3)
    fRegion = .Cells(1, 1)
    fMonth = .Cells(iRowNo, 1).Value
    fDate = .Cells(iRowNo, 2).Value
    fday = .Cells(iRowNo, 3).Value
    fLeachpad = .Cells(iRowNo, 4).Value
    fFCPDown = .Cells(iRowNo, 5).Value
    fFCPRun = .Cells(iRowNo, 6).Value
    fTONS = .Cells(iRowNo, 7).Value
    fTCU = .Cells(iRowNo, 8).Value
    fSAP = .Cells(iRowNo, 9).Value
    fRCU = .Cells(iRowNo, 10).Value
    fQLT = .Cells(iRowNo, 11).Value
    fASCU = .Cells(iRowNo, 12).Value
    fCalcite = .Cells(iRowNo, 13).Value
    fTCLAY = .Cells(iRowNo, 14).Value
    fBIO = .Cells(iRowNo, 15).Value
    fQKP = .Cells(iRowNo, 16).Value
    fClayFines = .Cells(iRowNo, 17).Value
    fCumined = .Cells(iRowNo, 20).Value
    fCuPlacedSAP = .Cells(iRowNo, 21).Value
    fCuPlacedRCU = .Cells(iRowNo, 22).Value
    fCuproduced = .Cells(iRowNo, 23).Value
    fMined = .Cells(iRowNo, 25).Value
    fMoved = .Cells(iRowNo, 26).Value
    fRehandle = .Cells(iRowNo, 27).Value
    fRehandle2 = .Cells(iRowNo, 28).Value
    fAcidtoaggl = .Cells(iRowNo, 30).Value
    fActual = .Cells(iRowNo, 31).Value
    fAcidtoaggl2 = .Cells(iRowNo, 32).Value
    fRaffSXEW = .Cells(iRowNo, 33).Value
    fTotalAcid = .Cells(iRowNo, 34).Value
    fTonsXTcu = .Cells(iRowNo, 38).Value
    fTonsXRcu = .Cells(iRowNo, 39).Value
    fCure = .Cells(iRowNo, 40).Value

    conn.Execute “insert into [dbo].[SAF_Forecast_Weekly] ([Updated], [Region], [Month], [Date], [day], [Leachpad Location]) ” & vbCrLf & _
    ” values (‘” & fUpdate & “‘,'” & fRegion & “‘,'” & fMonth & “‘,'” & fDate & “‘,'” & fday & “‘,'” & fLeachpad & “‘)”

    iRowNo = iRowNo + 1

    Loop

    MsgBox “Summary Forecast imported.”
    GoTo CleanUp
    ErrHandle:
    MsgBox (“An error occurred. Check your input and try again.”)
    CleanUp:
    Application.StatusBar = False
    If conn.State Then conn.Close
    End With

    1. Hi, I can’t see any errors really… but try to show the insert statement for each loop iteration in an MsgBox. Perhaps then you’ll be able to see what the problem is.

  25. Hi Tomas,
    Great Solution, there isn´t much excel vba to sql on the net and this is great.

    one question, does this also handle updates? for example if i change a value in one of the columns and export it to SQL will it create another row or update the one already there?

    If not could you provide some code for and update please..

    1. Hi, you can just skip the looping of rows and hard code the cell reference. So instead of:

      sCustomerId = .Cells(iRowNo, 1)

      you can use

      sCustomerId = .Cells(1, 1)

      …for instance.

      And remove the rows that handle looping.

  27. Hi Tomas,
    Very knowledgeable article. I’m facing one issue which I’m unable to resolve. There are many tables in the database I’m using and a lot of constraints are applied on them. I’m getting the error that insert query having conflict with the constrainst on table.column name and constraint name…. I want to bypass this thing. Can you help me what changes should be in my query… Will really appreciate the help

    1. Hi, I’d strongly suggest not bypassing constraints since they are probably there for a reason. That said, I’m guessing you run into foreign key constraint violations, since you do inserts in many tables? If so, and if your data is “healthy”, you probably just need to change the order of the inserts. E.g. you need to insert “products” before “orders” if there is a foreign key constraint that states that an order must have a product.

  28. I need Help.

    I need use WHERE NOT EXIST FUCNTION , I need COMPARE of PRIMARY KEYS IN THE EXCEL WITH OF SQL for Insert Rows IF NOT EXIST in the Database.

    And Update the ROWS with EXISTS in EXCEL WITH THE SQL

    “CODE”

    conn.Execute “INSERT INTO dbo.DM (REGION, COUNTRY_KEY, CUSTOMER, CUST_NUM, KAM, COMPANY_CODE, TRIGRAM, COUNTRY, CU_UPDATE, COLLECTOR, CC_SUPPORT,AR) values (‘” & REGION & “‘, ‘” & COUNTRY_KEY & “‘, ‘” & CUSTOMER & “‘, ‘” & CUST_NUM & “‘, ‘” & KAM & “‘, ‘” & COMPANY_CODE & “‘, ‘” & TRIGRAM & “‘, ‘” & COUNTRY & “‘, ‘” & CU_UPDATE & “‘, ‘” & COLLECTOR & “‘, ‘” & CC_SUPPORT & “‘, ‘” & AR & “‘) WHERE NOT EXISTS(SELECT CUSTOMER FROM MD WHERE CUSTOMER=(‘” & CUSTOMER &”‘)) )”

    I not work.

    Please HELP

  29. I have other Question

    I run the macro in my file, after uploading 32767 row the macro crash with the next Error “Overflow(6)”, i try uploading one file excel with more 200 thousand rows in my sql.

    Your have idea for fix this.

    2. This is due to the limitation on the int datatype. Changing your field datatype from Int to Double should fix the issue.

  30. It works like a charm for me ! Just had to add the “User Id” and “Password” in the connection string and remove the Integrated Security part. Thank you very much for this article !

  31. Tomas, I Insert the data, but I am wanting to now merge with another table, and do it from within the macro. I’m thinking the best way to go about this would be to call the .sql file I have written up to accomplish this in MySQL at the end before I close my connection. I cannot find the proper syntax for this though. Would you know how to do this?

  33. Hi Thomas many thanks for this.

    I have modified the code to read from an Excel Table using

    With TempFcast.ListObjects(1).DataBodyRange
    For i = 1 To .Rows.Count
    s1 = .Rows(i).Columns(1): s2 = .Rows(i).Columns(2): s3 = .Rows(i).Columns(3): s4 = .Rows(i).Columns(4): s5 = .Rows(i).Columns(5): s6 = .Rows(i).Columns(6)
    s7 = .Rows(i).Columns(7): s8 = .Rows(i).Columns(8): s9 = .Rows(i).Columns(9): s10 = .Rows(i).Columns(10): s11 = .Rows(i).Columns(11): s12 = .Rows(i).Columns(12)
    s13 = .Rows(i).Columns(13): s14 = .Rows(i).Columns(14): s15 = .Rows(i).Columns(15): s16 = .Rows(i).Columns(16): s17 = .Rows(i).Columns(17): s18 = .Rows(i).Columns(18)
    s19 = .Rows(i).Columns(19): s20 = .Rows(i).Columns(20): s21 = .Rows(i).Columns(21): s22 = .Rows(i).Columns(22): s23 = .Rows(i).Columns(23): s24 = .Rows(i).Columns(24)
    s25 = .Rows(i).Columns(25): s26 = .Rows(i).Columns(26): s27 = .Rows(i).Columns(27): s28 = .Rows(i).Columns(28): s29 = .Rows(i).Columns(29): s30 = .Rows(i).Columns(30)
    s31 = .Rows(i).Columns(31): s32 = .Rows(i).Columns(32): s33 = .Rows(i).Columns(33): s34 = .Rows(i).Columns(34): s35 = .Rows(i).Columns(35): s36 = .Rows(i).Columns(36): s37 = .Rows(i).Columns(37)

    all the variables (s1, s2, s3 etc.) are Dim’d as Variant because some columns are text and some numeric. The variables load correctly as String or Double.

    The SQL Server table to which the data has to be appended has the columns that correspond to numeric entries set as Numeric(12, 2)

    When I run my code I get the error: “Error converting data type varchar to numeric”

    I tried changing

    “Values (‘” & s1 & “‘, ‘” & s2 & “‘, ‘” & s3 & “‘, ‘” & s4 & “‘, ……….. and so on up to s37

    to this for each of the variables that could be either numeric or empty (the sql table is set to allow Null)

    Values (‘” & s1 & “‘, ‘” & s2 & “‘, ‘” & s3 & “‘, ‘CAST(” & s4 & “AS Numeric(12,2)’, …………

    But I still get the same error, any suggestions greatly appreciated.

    Thanks.

    1. Thought I had it fixed because of apostrophe in wron place and missing end bracket,

      Fixed that with

      “Values (‘” & s1 & “‘, ‘” & s2 & “‘, ‘” & s3 & “‘, CAST(‘” & s4 & “‘ AS Numeric(12,2)), CAST(‘” & s5 & “‘ AS Numeric(12,2)) …..

      But still same error!

  34. Thought I had it fixed because of apostrophe in wron place and missing end bracket,

    Fixed that with

    “Values (‘” & s1 & “‘, ‘” & s2 & “‘, ‘” & s3 & “‘, CAST(‘” & s4 & “‘ AS Numeric(12,2)), CAST(‘” & s5 & “‘ AS Numeric(12,2)) …..

    But still same error!

  35. Hi Tomas,

    I am looking for excel VBA macro which will copy multiple file from folder and move them to another folder ? and also would like to provide like operator in file name. Can you please tell if its possible and how?

  37. Hi! first of all sorry for my english skills to write are not to good…

    I’ve tried your example and everything was fine till i tried to add more arrows under the once you created in the example.
    The pop up window says:
    Run-time error ‘-2147217873(80040e2f)
    Violetion of primary key constraint ‘Customer_PL’. Cannot insert duplicate key in object ‘dbo.Customers’. The duplicate key is 345
    and VBA tell me the error is in
    conn.Execute “insert into dbo.Customers(CustomerId, FirstName, LastName) values (‘” & sCustomerId & “‘, ‘” & sFirstName & “‘, ‘” & sLastName & “‘)”

    i have not duplicated CustomerId, i think it’s because the loop starts again reading the rows when i press the button for “add” new rows.

    i’m doing this because the company where i’m doing my interships exported a heavy .xls file into sql server so now i have to find a way to manage the data from excel(Insert, delete, update) they are 70 columns :s

    Thanks for share your knowledge

  38. By the way…i forgot to tell that the excel file converted to sql server doesn’t have primary key, so… i supposed is easier?

    1. Hi Michel,

      I think you are right. Each time you press the button the same rows are inserted to the SQL Server table and the primary key prevents that.

      I don’t know anything about your data, but a very common scenario is to check if the record is already imported. If not, then import it. If it is, then check if it is changed and if so update it. Else do nothing. All this is handled by the MERGE statement.

  40. Thank you for this. This made exporting data into our database a snap. Using this in a template makes manual creation a thing of the past.

  42. Hi There
    This is amazing. I am using this to push data in excel with a button, to a table in SQL. I do have a question as I am receiving a fault.
    Also I am showing you my database table column datatypes.

    **
    **SQL TABLE COLUMN DATATYPES**
    **

    RecordedPeriod (datetime, not null)
    EventDate (varchar(8), not null)
    ID (int, not null)
    DeptCode (varchar(2), not null)
    OpCode (varchar(2), not null)
    StartTime (time(0), not null)
    FinishTime (time(0), not null)
    Units (int, not null)

    **
    **CODE**
    **

    Sub Button1_Click()

    Dim conn As New ADODB.Connection
    Dim iRowNo As Integer
    Dim sRecordedPeriod, sEventDate, sID, sDeptCode, sOpCode, sStartTime, sFinishTime, sUnits As String

    With Sheets(“Sheet1”)

    ‘Open a connection to SQL Server
    conn.Open “Provider=SQLOLEDB;Data Source=db1\db1;Initial Catalog=ProdTrack;Integrated Security=SSPI;”

    ‘Skip the header row
    iRowNo = 2

    ‘Loop until empty cell in FirstName
    Do Until .Cells(iRowNo, 1) = “”
    sRecordedPeriod = .Cells(iRowNo, 1)
    sEventDate = .Cells(iRowNo, 2)
    sID = .Cells(iRowNo, 3)
    sDeptCode = .Cells(iRowNo, 4)
    sOpCode = .Cells(iRowNo, 5)
    sStartTime = .Cells(iRowNo, 6)
    sFinishTime = .Cells(iRowNo, 7)
    sUnits = .Cells(iRowNo, 8)

    ‘Generate and execute sql statement to import the excel rows to SQL Server table
    conn.Execute “insert into dbo.TimeLog (RecordedPeriod, EventDate, ID, DeptCode, Opcode, StartTime, FinishTime, Units) values (‘” & sRecordedPeriod & “‘, ‘” & sEventDate & “‘, ‘” & sID & “‘, ‘” & sDeptCode & “‘, ‘” & sOpCode & “‘, ‘” & sStartTime & “‘, ‘” & sFinishTime & “‘, ‘” & sUnits & “‘)”

    iRowNo = iRowNo + 1
    Loop

    MsgBox “Data Successfully Exported.”

    conn.Close
    Set conn = Nothing

    **
    **FAULT**
    **

    Microsoft Visual Basic
    Run-time error ‘2147217913 (80040e07)’:
    Conversion failed when converting date and/or time from character string.

  43. Hi Tomas thank you for this very useful post but I have a question. I did everything as shown but getting this “Operation cannot be completed when object is closed ” error at the below line.
    conn.Execute “insert into dbo.Customers (CustomerId, FirstName, LastName) values (‘” & sCustomerId & “‘, ‘” & sFirstName & “‘, ‘” & sLastName & “‘)”
    I checked it online but couldn’t find a solution..

      1. Thanks for reply. yes, but I receive error in “con.Execute” line when I trace it by F8.
        The error is : ” Run-Time error 2147217873 (80040e2f) Automation error. ”
        Here is some of my codes .

        Dim sDate, sFullName as String ‘The Data type in SQL table is nvarchar .
        …
        sDate = Worksheets(“Sheet1”).TextBox1.Value
        sFullName = Worksheets(“Sheet1”).TextBox2.Value
        con.Execute “insert into dbo.DefinitionForm (Date1,FullName) values (‘” & sDate & “‘,'” & sFullName & “‘)”
        MsgBox “Successful.”

        Could you help me please?

        1. Dear Mr.Tomas
          I found it finally. I picked a command button from ActiveX Controls. It didn’t know text boxes n option btn.
          But now I pick one from Form Controls and it works.
          I am thankful for all your help, Sir.
          Br,

  45. I followed these instructions step by step it worked once but now it keeps giving me Sub Or Function not defined and I have checked everything and can’t see what I am doing wrong?

  47. hi I have a comment column which has ‘ character as a value. Since this is a key word the SQL is breaking is there a work around for the same?

  49. Sub Button1_Click()

    Dim conn As New ADODB.Connection
    Dim iRowNo As Integer
    Dim sCustomarId, sFirstName, sLastName As String

    With Sheets(“Sheet1”)

    ‘Open a connection to SQL Server
    conn.Open “Provider=SQLOLEDB;Data Source=W1042W89R2;Initial Catalog=ExcelDemo;User Id=sa;Password=Bell@12345”
    cn.ConnectionString = Connstring
    cn.Open

    ‘Skip the header row
    iRowNo = 2

    ‘Loop until empty cell in CustomarId
    Do Until .Cells(iRowNo, 1) = “”
    sCustomerId = .Cells(iRowNo, 1)
    sFirstName = .Cells(iRowNo, 2)
    sLastName = .Cells(iRowNo, 3)

    ‘Generate and execute sql statement to import the excel rows to SQL Server table
    conn.Execute “insert into dbo.Customers (CustomarId, FirstName, LastName) values (‘” & sCustomarId & “‘, ‘” & sFirstName & “‘, ‘” & sLastName & “‘)”

    iRowNo = iRowNo + 1
    Loop

    MsgBox “Customers imported.”

    conn.Close
    Set conn = Nothing

    End With

    End Sub

    I am trying to make a file for multiple users….however facing an error with runtime error424 object:required. Please help

  50. This Post is old but still it is guiding people. Thanks man, This helped me a lot. Wanted to appreciate your efforts.

  52. Hi Tomas, Great article!

    I understood that you made a code to insert ‘N’ number of rows with fixed number (‘3’) of columns, but Could you please help with the code to insert ‘N’ number of rows with ‘N’ Number of columns?

    Many Thanks,
    Venkat

  53. Hi..i tried the above and when i check my sql table its coming up with black rows although there is data in the excel spreadsheet and funnily its coming with the exact no of rows.

  54. Thanks nice posted, Tomas. For some reason, my program didn’t import datas columns in Excel to SQL Table. I need your expertise help, would you be so kind help to tell me what I did wrong? Pls advise

    Sub Button1_Click()

    Dim conn As New ADODB.Connection
    Dim iRowNo As Integer
    Dim sprrowid, sMaterialType, sMaterialGroup As String

    Dim SQLUser As String
    Dim SQLPassword As String
    Dim SQLServer As String
    Dim DBName As String
    Dim DbConn As String

    SQLUser = “S1Datar”
    SQLPassword = “x!la”
    SQLServer = “voa-db-001”
    DBName = “S1data”

    With Sheets(“Sheet1”)

    ‘Open a connection to SQL Server

    conn.Open “Provider=SQLOLEDB; User ID=” & SQLUser & “;Password=” & SQLPassword & “;Initial Catalog=” & DBName & “;” & _
    “Data Source=” & SQLServer & “;Use Procedure for Prepare=1;”

    ‘Skip the header row
    iRowNo = 2

    ‘Loop until empty cell in CustomerId
    Do Until .Cells(iRowNo, 1) = “”
    sprrowid = .Cells(iRowNo, 0)
    sMaterialType = .Cells(iRowNo, 1)
    sMaterialGroup = .Cells(iRowNo, 2)

    ‘Generate and execute sql statement to import the excel rows to SQL Server table

    conn.Execute “UPDATE ” & [S4_DataMigration].[dbo].[pt_mstr_Group_Type] & ” ” & _
    “SET ” & _
    “MaterialType = ” & sMaterialType & “, ” & _
    “MaterialGroup = ” & sMaterialGroup & _
    “WHERE prrowid = ” & sprrowid & ” ;”

    iRowNo = iRowNo + 1
    Loop

    MsgBox “Customers imported.”

    conn.Close
    Set conn = Nothing

    End With

    End Sub

  56. Hello,

    Below is my VBA coding, however, the data isn’t importing.

    Sub Button4_Click()

    Dim conn As New ADODB.Connection
    Dim iRowNo As Integer

    Dim sEmpId, sEmployeeName, sManagerName, sUserId, sDateofJoining, sTeamName, sEmployeeStatus, sManagerChangedDate, sTeamChangedDate, sTeamChangedTo, sComments, sLastDatewithAllscripts, sStatus, sUpdatedBy, sUpdatedDateTime As String

    With Sheets(“database”)

    ‘Open a connection with SQL Server
    conn.Open “Provider=SQLOLEDB;Data Source=K783620-W10N;Initial Catalog=Staffing_List;Integrated Security=SSPI;”

    ‘Skip the header row

    iRowNo = 1

    ‘Loop Until empty cell in Emp Id

    Do Until .Cells(iRowNo, 1) = “”

    EmpId = .Cells(iRowNo, 2)
    EmployeeName = .Cells(iRowNo, 3)
    ManagerName = .Cells(iRowNo, 4)
    UserId = .Cells(iRowNo, 5)
    DateofJoining = .Cells(iRowNo, 6)
    TeamName = .Cells(iRowNo, 7)
    EmployeeStatus = .Cells(iRowNo, 9)
    ManagerChangedDate = .Cells(iRowNo, 10)
    TeamChangedDate = .Cells(iRowNo, 11)
    TeamChangedTo = .Cells(iRowNo, 12)
    Comments = .Cells(iRowNo, 13)
    LastDatewithAllscripts = .Cells(iRowNo, 14)
    EmployeeStatuse = .Cells(iRowNo, 15)
    UpdatedBy = .Cells(iRowNo, 16)
    UpdatedDateTime = .Cells(iRowNo, 17)

    ‘Generate and execute sql statement to import the excel rows to SQL Server table

    conn.Execute “Insert into dbo.StaffingList (EmpId, EmployeeName, ManagerName, UserId, DateofJoining, TeamName, EmployeeStatus, ManagerChangedDate, TeamChangedDate, TeamChangedTo, Comments, LastDatewithAllscripts, Status, UpdatedBy, UpdatedDateTime) Values (‘” & sEmpId & “‘,'” & sEmployeeName & “‘,'” & sManagerName & “‘,'” & sUserId & “‘,'” & sDateofJoining & “‘,'” & sTeamName & “‘,'” & sEmployeeStatus & “‘,'” & sManagerChangedDate & “‘,'” & sTeamChangedDate & “‘,'” & sTeamChangedTo & “‘,'” & sComments & “‘,'” & sLastDatewithAllscripts & “‘,'” & sStatus & “‘,'” & sUpdatedBy & “‘,'” & sUpdatedDateTime & “‘)”

    iRowNo = iRowNo + 1

    Loop

    MsgBox “Data Transferred to SQL”

    conn.Close

    Set conn = Nothing

    End With

    End Sub

  57. If you try to add a string with an apostrophe to an SQL table, Excel will give you an error near the letter that follows the apostrophe. To prevent any such errors, you can replace

    sCustomerId

    with

    Replace(sCustomerId, “‘”, “””)

    (and the same for the other named strings) in your conn.Execute statement.

    (I can’t read any of the comments, for some reason, so sorry if someone has already said this.)

  58. Hi I followed all the steps, and I have the “user-defined type not defined” error. the Microsoft ActiveX data object recordset 2.8 library has been added

  60. Thanx so much. One question….I am have a lot of columns in my database and having issues trying to split a long line of code in multiple lines before where the words are separated by a comma I.e. before value ( ‘” &..&.”‘,

Leave a Reply

Discover more from Tomas Lind

Subscribe now to keep reading and get access to the full archive.

Continue reading