Avatar of Jim Youmans
Jim YoumansFlag for United States of America

asked on 

Read/Update data in SQL Server from Excel

I have a table in SQL Server 2019 that I need to be able to use an excel spreadsheet to read and update the data.  Here is what I am looking to do.

1. Create an excel spread sheet that is connected to tableA
2. When opened it needs to read the data from tableA and display it
3. If I change the data in the spread sheet then it needs to update tableA with that new value.

Open in new window

I have found a couple of pricy add-ons that will do this but I have no budget.  I should be able to do this with VBA but can't seem to find any good examples.  Any help would be greatly appreciated.  Thank you!!


SQLMicrosoft SQL ServerMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Jim Horn
Avatar of Fabrice Lambert
Fabrice Lambert
Flag of France image

Ms Excel is a great tool to pull data from a database, but it does not update it (Excel's purpose is not to be a front-end), and there is no easy solution to do it.

MS Access might be a better tool for your project.
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

SQL Server and Excel do not support bi-directional movement of data back and forth, mostly because they are polar opposites.

SQL Server is a normalized database with rigid controls over the values that are stored in it:  column data types, primary and foreign keys, default constraints, triggers, etc.

Excel is a spreadsheet with zero controls over the values that are stored in it.  If somebody wants to put in date values '2021-01-01', '2020-05-07', '2021-01-33', '2021-02-13-14', 'banana', '' empty string, etc. they are free to do so, but all but the first value would be accepted in a normalized database.  And about that second value, is it May 7th or July 5th? 

Not to mention this opens the door for people of bad intent entering values that would lead to somebody having a resume-generating event.

You can go from SQL Server to Excel,  see my article Microsoft Excel & SQL Server:  Self service BI to give users the data they want for a correct but pretty outdated way to do it, but there is no button-click-friendly automated way to go from Excel to SQL Server.

Avatar of Jim Youmans
Jim Youmans
Flag of United States of America image


Thank you all!  I am working on using a power app to do this now.
Avatar of Jim Youmans
Jim Youmans
Flag of United States of America image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

In that case I'll take credit for the correct answer as 'You can't do that'.  Thanks in advance. 
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo