Use Excel to Connect to SQL Server Data
Vložit
- čas přidán 28. 03. 2022
- Connect directly to SQL Server data from within Excel. Also learn how to add and modify SQL statements from within Excel and pass them to SQL Server for data retrieval. If you need to quickly refresh data from SQL Server without hassle, then you need to watch this video!!
★☆★ Join this channel to get access to perks: ★☆★
/ @anthonysmoak
★☆★ FOLLOW ME BELOW: ★☆★
bit.ly/m/anthonysmoak
Instagram / anthonysmoakdata
LinkedIN / anthonysmoak
Facebook / anthonybsmoak
☕☕ If my channel has helped you, consider buying me a coffee! Thanks & Cheers! ☕☕
www.buymeacoffee.com/anthonys...
★☆★ Support this Channel: ★☆★
Merch ► shop.spreadshirt.com/AnthonySmoak
#Excel #SQLServer #analytics #data #dataanalyst #education #onlinelearning
![How to Automate Excel Reports Using SQL [CSV to SQL to Excel Automation]](/img/n.gif)
I'm totally new to SQL and usually dive into the deep end of things. Had to do some homework before understanding what you were doing but once I did that, you came thru clear as a bell. Thanks!
Thanks for leaving a comment! Keep on learning SQL, it's a foundational skill for data work.
What did you use to learn SQL? I'm in the same boat
Hey! Just wanted to say a quick thank you for the Excel to SQL Server tutorial on CZcams. It's been a game-changer for my office reports, making everything faster and stress-free. Your help is much appreciated!
Cheers,
You made my day! Thank you for leaving this comment.
My pleasure!@@AnthonySmoak
great video just what I was looking for.
Thanks
Very nice, exactly what I was looking for; thank you very much!
Glad to hear it, thanks for the comment!
So glad I found your channel!
Glad to hear it and thanks!
Great video and I am now a subscriber. Your instructions are concise and easy to follow.
Thank you so much for the comment!
First time on your channel! Great work Anthony!
Thank you, I appreciate it!
Thanks sir. After a long search I found this video. I was looking for how to edit SQL query in OLEDB Query and this video exactly shows how to do that.
Thanks again 😊
Glad to hear the video helped you! Thanks for the comment
THANK YOU!!!!
super helpful and hassle free explanation.
Thank you!
Good job dude, unexpected perfect. Do some more , bless
Thanks for the comment. I have a new video out today!
great videos !!!!
Simple and Nice man !! tnx
Simple and nice is what I was aiming for, thank you!
tks bro
Hi, thanks for the explanation! It was driving me crazy that I could not edit my query. I assume this is some Office 365 "feature" because I used to edit it in the properties using Excel 2016.
Very simple and straightforward explanation, thank you! I have a problem that is maybe similar to this - do you know if there's a way to pull in data from a source published in Tableau Server directly to Excel/Access (or even Python)? Where I work, we don't have access to the original database tables, but we can connect to the published Tableau server data sources. I was wondering if there was a way to eliminate the "middle" steps (data source > creating a workbook in Tableau > building the view/filters > exporting to Excel/Access db). Sorry if this question is more appropriate for another video. Thanks again!
You are doing it the right way. I am not aware of a method to connect directly to a Tableau extract on server.
@@AnthonySmoak No worries, thanks again for the wonderful videos!
2:35 - Hahaha, you got me.
I'm a recovering FP&A analyst from years ago!
As a financial analyst I feel a little targeted haha. Nice video.
🤣 As a recovering financial analyst, I know how Excel was abused to generate manual reports. Thanks for commenting, I'm sure you're one of the good ones who automate reports from trusted sources!
I'm doing this for some ad hoc reports but I find that the refresh is sometimes slow when importing about 10 or 15 sheets of data that have 1k to 15k of rows per sheet. However if I don't use Power Query and connect Excel directly to my SQL views it refreshs much faster. But the trade off is the setup of each sheet to each view and often times a re-setup if there are substantial updates to the view.
I like the flexibility of Power Query, but I'm not sure why it takes so long to refresh the data. I'm wondering now if VBA using an ole db connection to the database with SQL queries to pull the data is the way to go.
Could you please tell me how to achieve this? Every way I try to connect to SQL Server, I'm not able to avoid Power Query
@@pietjehans206 Excel- Data tab - From Other Sources - From SQL Server
Hi your videos are really good, but i am looking for excel data to be insert or update in sql table. Is there any video for that. i am waiting for this
Thank you. Off the top of my head, you will have to use VBA or a third party tool in order to enable write back to SQL Server. It will be easier to save your data as a .csv file and upload it to Excel or use an SSIS process.
Hi dude I was wondering if you can answer me the following question. I am working on a project with a large scale data set in excel as the central point. This data set will be updated on share point by multiple users i.e. completion dates of the work and what not. This data set is stored on sharepoint and multiple different stakeholders require a cut off version of the master data set just with the relevant information. what I have done is to create a new SS to let's say Antony as an example and cut down the data based on his needs via power query to meet his needs. I have deployed a direct URL connection for Anthony to to the Master file in Share point and did a setup of a data refresh every 1 min of the source data for Anthony. However, even that the solution works, the new entries for Anthony's customized report out of the master data set take a hell of a lot time and refreshes until I can see the updated data in excel . In other words if I change something on the master file , refresh the query for Anthony, the changed data needs minutes to reflect that change. Is there a way to optimize power query in excel to reflect that change of data in real time ? The problem is that I have to create multiple customized data sets which are pointing back to the master file plus I have multiple data sets of third parties which feeding back into the master file, meaning once those get into my master file the sub sets of data created for certain stakeholders need to refresh. Does this makes sense bro ?
What to do if I want to display the server name (data source) in a cell . When we have multiple boxes would like to display the source in the sheet itself.
Hi Anthony, so whenever my mssql data change, the data in excel would change after refresh right? What if I want to do an inner join, so I just change the query in the source in power query? Thank you so much.
Or do i import both tables and also change the query?
@@saikyawlin663 Your data will refresh based upon what is in SQL Server, yes. Watch the videos in this series to connect Excel to a SQL query. czcams.com/play/PLRNrM8SIqgQZ4u5yGSlVNeGgej1lcwLwr.html
Is there a way for the SQL query to pick up parameter from Excel cells? Lets say there are dates in A1 and A2 cell. And I want the query to pick up these Dates for Where condition in SQL and query database.
I believe this is possible with a parameter query. You may have inspired my next video!
Is there a way to create a dynamic query? I pull data on a monthly basis from SQL Server and I need to change the reporting date to the corresponding month. It would be great if I could change the date in a named range and have it link to the query instead of having to manually edit it.
There are multipe ways to do this. One way is to edit the connection string in VBA so the Where clause values point to cells on the worksheet. I would use this approach only for your personal use. Handing the document over to others could allow them to edit the sql for nefarious means.
Thank you for the tutorial! I have successfully connected to the Server through SQL Management Studio, but I cannot export the desired database to the excel workbook to operate. The error message says "Microsoft ACE OLEDB.12.0 Provider is not registered on the local computer". I used to conntect well on the existing workbook to work with power query for importing, cleaning and transforming data just like what you teach in this video. But after installed MS Office 2021 I failed connection and therefore installed SQL Management Studio to recover. I appreciate if you could offer advice to resolve this issue.
Try the following: learn.microsoft.com/en-us/previous-versions/troubleshoot/winautomation/support-tips/databases/ace-oledb-12-0-provider-not-registered-on-local-machine
Thank you for the tutorial. I have a question. After upgraded MS 2021 version, I cannot connect the SQL Server even though having the Server's name and database. The error message says "Principle name is incorrect. SSPI cannot be created!"
Iv'e never encountered this issue. See if this discussion helps: community.powerbi.com/t5/Power-Query/Microsoft-SQL-The-target-principal-name-is-incorrect-Cannot/td-p/69281/page/1
Hi, can we change authentucation to SQL authentucation? Thanks
Is it possible to dinamically change the SQL statement based on specific cells values? For example, you could create a cell to specify how many top registers you want to get.
Watch the videos in this playlist czcams.com/play/PLRNrM8SIqgQZ4u5yGSlVNeGgej1lcwLwr.html
Hi Anthony, this is great. However, one issue I'm having is that the next time I open the workbook the data connection and associated quesry aren't being saved. I get an error saying the query was not found. How do I resolve this issue?
How do I create macro button to update the dates to fetch data from the same query
I’m totally new to sql, I think my question is silly (not to say stupid) but I’m going to do it anyway.
Once I made the connection is there the risk that I inadvertently edit the original database?
This is a great question. You could update the original database if you send SQL statements containing "INSERT, UPDATE, DELETE or DROP" to the database. You would also need sufficient privileges from the database administrator to make those types of changes. If you are using this example on your own personal laptop you should have admin rights and can make SQL statements to alter tables and data (you should be fine if you stick to SELECT statements). At work, the database admin shouldn't allow you to have access to make inadvertent edits, but make sure to ask if this is the case.
@@AnthonySmoak thank you very much, for the video and the response
Can i change the Data in Excel?
So like i change the Order Key from "26" to "32" in Excel.
Is it possible then to automatically change this value also in the SQL-Sever table?
sqlspreads.com/blog/how-to-insert-data-in-excel-to-sql-server/
How to create a connection when other user opens the Excel file , the data will refresh automatically, without prompting to authenticate to SQL
Shift + Ctrl +V to paste only values
Try ALT H, V, V
Hi, I loaded my data and run some pivot tables on it. Now I want to change the database name, but using Excel 2013 and no PowerQuery is shown. What should I do?
mediaspace.illinois.edu/media/t/1_028q5krl/27706721
Does this reflect when you add column to a exusting table?
If you add a column to the existing database table it will be reflected when you refresh the data in Excel. Experiment with your data and test it out.
@@AnthonySmoak does this work on sharepoint? Or only local drive.
@@blanklate073 I have never tried to upload a connected file to SharePoint to try. Let me know if you are successful.
Can we change connection to SQL authentication? Thanks
Is it possible to distribute such an excel file without giving the database credentials to anyone?
I'm a new user ,I'm getting unable to refresh query and data source not found error , how can we resolve that.
Hi Anthony!
I want that SQL Statement at 8:52 to be fetched from a Cell in that Excel. So that I can change my query parameters from that Excel sheet itself. Is this possible? TIA
Hello sir. Watch the 3rd and 4th videos in this playlist. czcams.com/play/PLRNrM8SIqgQZ4u5yGSlVNeGgej1lcwLwr.html
Did the other videos help?
Do SQL Developer connect Excel? I am struggling with the first step of connecting excel to the SQL server
I’m using on oracle DB, IDE: PL/SQL can ya help me connect like this
I don't know much more than what a Google search would reveal on making an Oracle connection: www.wikihow.com/Connect-Excel-to-an-Oracle-Database
Can I update SQL db using excel form ?
So how do we make changes in excel and make it reflect in our database?😊
You'll need a 3rd party tool like sqlspreads.com/ I've never used it myself however.
How about if i want to write to the SQL from the excel table?
How to "write to the SQL" or back to a database table from Excel?
@@AnthonySmoak imagine that i write on a cell, press enter, and that data goes to the SQL DB.
Try an approach here, it may require an Excel add in sqlspreads.com/blog/how-to-insert-data-in-excel-to-sql-server/
@AnthonySmoak is it not possible without an addin ?
How do i Create a connection, not a query,?
Question/s:
so by doing these steps:
1. I need to keep the SQL server open at all time?
2. When I hit refresh in excel, how does it interact with the SQL server? Does it automatically hit execute to load the data to excel and will get the latest update?
1. The server should be available so you can access it for up-to-date data.
2. When you refresh the query in Excel, the sheet will reflect the current data in your database.
@@AnthonySmoak thank you. I really appreciate it :)
possible to connect to multiple databases so that I could query data with "JOIN"?
Use Power Query to combine multiple data sources. Here's an Example support.microsoft.com/en-us/office/learn-to-combine-multiple-data-sources-power-query-70cfe661-5a2a-4d9d-a4fe-586cc7878c7d
@@AnthonySmoak i have to combine data from 26 companies. So I plan to UNION data from 26 companies and do a left join with other tables.
Excel sounds like the wrong tool. Try to import the sources into SQL Server or another database, and then join your data with SQL. Automate it if it's a repeatable process.
@@AnthonySmoak This should be an automated report for regional Finance team. The report should be easily accessible and user friendly for finance. So SQL is not an option.
@@ongkamsiong Do the backend data aggregation work in a real database. Use Excel to display the results. I've worked in Finance and I know they love Excel, but manual Excel work makes life miserable for people putting the reports together when they should be automated.
Is there a way to move data from single row into multiple rows?
There are many ways to unpivot data if that's what you're asking.
@@AnthonySmoak yes I think so.. I am creating a report based on serial # of a part so I am only bringing in 1 row of data so I want to take columns at end and move the to next row. Is there any video on pivoting columns?
@@jimton4454 SQL Server Unpivot
czcams.com/video/38xFjh3KLM8/video.html
SQL Server Dynamic Pivot
czcams.com/video/xpOq1_Ll9zQ/video.html
Power Query UnPivot
czcams.com/video/EKZYt7qsKRo/video.html
The results that I am getting back from query is 1 row and lets say 10 columns. When I put into excel I want to take last 5 columns and them below 1st 5. So it fits on sheet better to view and print. I am calling it as a stored procedure in excel and passing the serial number so I am only getting 1 row of data.
@@jimton4454 Just create a second worksheet that references the first worksheet. For each cell in the second worksheet, make a reference to the respective cell on the 1st worksheet. Your first worksheet is for refreshing via SQL Server, the second worksheet with multiple lines, references the cells on the 1st worksheet.
how to include additional columns with formulas based on the retrieved data from the database?
Once the base columns are retrieved from the database, try to calculate your new columns in Excel or better yet, develop your calculations at the database layer to save Excel from working too hard.