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:
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”:
Now we can create the button. From the “Developer” menu, select “Insert” and “Button”:
Draw a button anywhere in the sheet and an “Assign macro” dialog will appear. To create a macro for the button, click “New”:
This will open a code window for Visual Basic for Applications (VBA):
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:
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:
If you look in the table in SSMS, our customers are imported as expected:
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]
Thanks ,its very use full link.
Thank You so much for such a simple solution Thomas!!!
You ROCK!
Thanks 🙂
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.
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.
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
I needed a solution at work and your tutorial is spot-on. Thank you.
Nicely explained. Simple and straight.
Thanks.
Thanks Tom for making the Macros concept such simple…was looking something like this for understanding Macros. 🙂
Thank you! This worked great after trying out many other options1
Thanks, glad it’s useful 🙂
Fantastisk Guide Tom, thankyou for the help :)!
I hope you will make guide with different datatypes one day maybe ;)?
Dear Tomas,
Thank you very much for your post. This solution worked like a charm for my requirements.
Best,
Ed
Thank you so much!
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
Hi Rohan!
That is probably caused by the ” signs at the beginning and at the end of the string. The string will be truncated at those positions.
Regards,
Tomas
Good article, i was searching for that frm many days . How can we update particular roll cell .
Hi, to update a specific cell in a VBA macro is easy. The following code changes cell A1 to the value of string 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 = sSomeValueRegards,
Tomas
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
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
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
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.
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.
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
Hi, if you want to grant insert permissions to a table for a specific user, use the following syntax:
Regards
Tomas
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.
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.
Hi
conn.Open “Provider=SQLOLEDB;Server=local;Database=excelSheetUsingMacros;Integrated Security=SSPI;
This is the Conection string which i gave.
Thanks !!! . Got Output Man !!! Thanks Alot !!!!!
Nice 🙂
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
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
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 ) GOBut I stongly suggest that you use primary keys on tables however…
Regards
Tomas
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
That is because I’m using an INSERT statement in the demonstration. To get the behaviour you want, try the MERGE statement.
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!
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
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…
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.
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!!
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:
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.
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
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
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
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
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
Hi, what’s the actual error message? Might have missed it but I can’t see that in your comment.
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.
Thanks!! excellent post
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!
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
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.
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..
Hi, this solution will only add new rows.
To get the behavior you want, one solution is to use a MERGE statement instead of the INSERT statement that is used now.
I just need to update 2 specific cells from excel to SQL. How would I do that?
Thanks
Jessica
Hi, you can just skip the looping of rows and hard code the cell reference. So instead of:
you can use
…for instance.
And remove the rows that handle looping.
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
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.
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
Hi, try “WHERE CUSTOMER NOT IN (SELECT CUSTOMER FROM MD)” instead.
I AM USE SQL, THE SOLUTION IS WHERE NOT EXISTS( SELECT CUSTOMER FROM MD WHERE CUSTOMER='” & CUSTOMER & “‘))”
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.
Sounds like some sort of data type overflow, but I can’t answer without more information. With that much data, investigate if you could use SSIS instead.
This is due to the limitation on the int datatype. Changing your field datatype from Int to Double should fix the issue.
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 !
You are welcome 🙂
Thank you, it helped me a lot! How can I add to the connection the user ID and Password, please?
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?
Hi, I’m not sure I understand what you are trying to do exactly, but why don’t you store the sql file as a stored procedure in the database and call that?
Excellent. It worked! Thank you
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.
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!
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!
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?
Hi, perhaps there is some useful information at stackoverflow?
I think this is one of the most important information for me.
And i’m glad reading your article. But wanna remark on few general
things, The web site style is ideal, the articles is really excellent
: D. Good job, cheers
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
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?
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.
have anyone checked out datarails.io ? they seems to offer a seamless solution for this…
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.
This is awesome! Thanks so much! I’d been triggering SSIS packages from Excel before, which was just messy!
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.
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..
hi. would you please hint me about passing textbox value to SQL?
Have you tried: Worksheets(“Sheet1”).TextBox1.Value
BR
Tomas
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?
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,
Glad to hear it is working! 🙂
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?
Hi, check for syntax errors in the script. Else, you can try to recreate the macro.
/Tomas
Tomas
How can we delete rows with the same style?
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?
What’s up, constantly i used to check blog posts here in the early hours in the morning, as i enjoy to
find out more and more.
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
This Post is old but still it is guiding people. Thanks man, This helped me a lot. Wanted to appreciate your efforts.
Hi,
I have some data that has a ‘ character which is causing a run-time error. Is there a work around?
Thanks.
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
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.
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
Really easy and nice
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
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.)
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
thnx:)
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 ( ‘” &..&.”‘,