Use Excel to Connect to SQL Server Data
Вставка
- Опубліковано 5 вер 2024
- 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/antho...
Instagram / anthonysmoakdata
LinkedIN / anthonysmoak
Facebook / anthonybsmoak
☕☕ If my channel has helped you, consider buying me a coffee! Thanks & Cheers! ☕☕
www.buymeacoff...
★☆★ Support this Channel: ★☆★
Merch ► shop.spreadshirt.com/AnthonySmoak
#Excel #SQLServer #analytics #data #dataanalyst #education #onlinelearning
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
Still new to my BI job, this helped!
Glad to hear it, thanks for sharing!
Hey! Just wanted to say a quick thank you for the Excel to SQL Server tutorial on UA-cam. 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
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
Great video and I am now a subscriber. Your instructions are concise and easy to follow.
Thank you so much for the comment!
super helpful and hassle free explanation.
Thank you!
Gov't fiscal analyst here, recently discovered our bloated financial database from the 80s has an SQL endpoint in the form of a DNS address accessible only inside the government network (i.e, within the VPN). I'm diving into the deep end on this. I've connected to APIs using PowerQuery, but I don't think this would be considered an API?
Based on this video, I'm guessing "server" would be the DNS address, and "database" would be the database to be queried at that DNS address?
Also, I'm totally going to be using this for production workbooks. But you'll be happy to know we move glacially, so a daily refresh automated via VBA keeps everything current. ;)
Apparently they've been begging for people to use the SQL endpoint more but it's too complex for most folks so I'm forging new paths in trying to get this working.
Thanks for the video, very informative!
Thank you Brother Smoak.
@@USA_to_Asia_in_2024 Thank you!
First time on your channel! Great work Anthony!
Thank you, I appreciate it!
great videos !!!!
Simple and Nice man !! tnx
Simple and nice is what I was aiming for, thank you!
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!
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.
Good job dude, unexpected perfect. Do some more , bless
Thanks for the comment. I have a new video out today!
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.
2:35 - Hahaha, you got me.
I'm a recovering FP&A analyst from years ago!
How to create a connection when other user opens the Excel file , the data will refresh automatically, without prompting to authenticate to SQL
THANK YOU!!!!
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!
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!
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. ua-cam.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 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 ua-cam.com/play/PLRNrM8SIqgQZ4u5yGSlVNeGgej1lcwLwr.html
tks bro
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
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 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
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.
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.
Is it possible to distribute such an excel file without giving the database credentials to anyone?
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 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/
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 :)
Can we change connection to SQL authentication? Thanks
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
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.
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
Shift + Ctrl +V to paste only values
Try ALT H, V, V
Do SQL Developer connect Excel? I am struggling with the first step of connecting excel to the SQL server
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 ?
Hi, can we change authentucation to SQL authentucation? Thanks
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.
Can I update SQL db using excel form ?
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
How do i Create a connection, not a query,?
I'm a new user ,I'm getting unable to refresh query and data source not found error , how can we resolve that.
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
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
ua-cam.com/video/38xFjh3KLM8/v-deo.html
SQL Server Dynamic Pivot
ua-cam.com/video/xpOq1_Ll9zQ/v-deo.html
Power Query UnPivot
ua-cam.com/video/EKZYt7qsKRo/v-deo.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.
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. ua-cam.com/play/PLRNrM8SIqgQZ4u5yGSlVNeGgej1lcwLwr.html
Did the other videos help?
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.
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 ?