Run SQL queries in EXCEL (just like a normal Excel formula 🤯)

👉 Explore All My Excel Solutions: pythonandvba.com/solutions
𝗗𝗘𝗦𝗖𝗥𝗜𝗣𝗧𝗜𝗢𝗡
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
Did you know that you can use an SQL formula in Excel? Learn in this video how to run SQL queries in Excel. You don't have to be a programmer to be able to get the most out of SQL. In this video, you'll learn how to add the SQL formula to your Excel application. In particular, we will use the Python package xlwings and the xlwings Excel add-in. After setting everything up, I will walk you through some practical examples and share my tips and tricks for using SQL in Excel.
🌍 𝗟𝗜𝗡𝗞𝗦:
▶ Download the xlsx workbook: github.com/Sven-Bo/run-sql-qu...
▶ Xlwings documentation: docs.xlwings.org/en/stable/ex...
⭐ 𝗧𝗜𝗠𝗘𝗦𝗧𝗔𝗠𝗣𝗦:
00:00 - Intro
00:31 - Installing the dependencies
00:57 - First SQL Example
03:07 - More SQL sample queries
06:47 - Outro
𝗧𝗢𝗢𝗟𝗦 𝗔𝗡𝗗 𝗥𝗘𝗦𝗢𝗨𝗥𝗖𝗘𝗦
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
🆓【𝗙𝗥𝗘𝗘】Excel Add-in (𝗠𝘆𝗧𝗼𝗼𝗹𝗕𝗲𝗹𝘁): pythonandvba.com/mytoolbelt
📊 Dashboard Excel Add-In (𝗚𝗿𝗮𝗳𝗹𝘆): pythonandvba.com/grafly
🎨 Cartoon Charts Excel Add-In (𝗖𝘂𝘁𝗲𝗣𝗹𝗼𝘁𝘀): pythonandvba.com/cuteplots
🤪 Fun Emoji Excel Add-In (𝗘𝗺𝗼𝗷𝗶𝗳𝘆): pythonandvba.com/emojify
📑 Excel Templates: pythonandvba.com/go/excel-tem...
🎓 My Courses: pythonandvba.com/go/courses
📚 Books, Tools, and More: pythonandvba.com/resources
𝗖𝗢𝗡𝗡𝗘𝗖𝗧 𝗪𝗜𝗧𝗛 𝗠𝗘
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
🔗 LinkedIn: / sven-bosau
📸 Instagram: / codingisfun_official
💻 GitHub: github.com/Sven-Bo
💬 Discord: pythonandvba.com/discord
📬 Contact: pythonandvba.com/contact
☕ 𝗕𝘂𝘆 𝗺𝗲 𝗮 𝗰𝗼𝗳𝗳𝗲𝗲?
If you want to support this channel, you can buy me a coffee here:
▶ pythonandvba.com/coffee-donation

[@CodingIsFun]

*Do you use SQL at your job?* 🤔

[@michaelomosebi6374]

I get the error: "Could not activate Python COM server, hr=-2147221164" 😣😣

[@SaulEduardoo2]

Hi Sven, I don't used, I don't even know how to program in SQL, but, use SQL will allow me to create database more ease to manipulate instead of having all the data in excel.

[@channelbtv2903]

Thank you sir, do you have any tutorial using dynamic SQL instead of statik syntac SQL in excell?

[@CodingIsFun]

@@channelbtv2903 nope

[@Russels_Viper]

Can this work with Oracle ? If yes, how do I go about doing it .

[@plavali_znaem]

The content of this channel is sooo underrated!! It’s not the first time I search for something very specific and end up here finding exactly this and more over my expectations!! 🎉🎉 Weiter so, Sven! Danke sehr!

[@CodingIsFun]

Ah, you're making me blush! Thanks for the kind comment and happy to hear that you're enjoying the videos! ♥💪

[@TonySitterly]

I love SQL and hate pivot tables - This is awesome! Thanks!

[@CodingIsFun]

Glad I could help! Cheers, Sven ✌️

[@mubafaw]

Exactly what I was looking for. Excellent!!!!! Thanks😊👍

[@CodingIsFun]

Happy to hear that it was useful; thank you for taking the time to leave a comment and for watching the video!

[@brunoestrella598]

Great job ! Thanks for sharing !

[@CodingIsFun]

You're very welcome! Thank you for watching and for the comment!

[@andybecker5001]

This is great! No idea this could be done

[@CodingIsFun]

Glad you liked it. Thanks for watching and taking the time to leave a comment!

[@chaitanyadeshmukh1404]

This so much fun. Will definitely use it. 💯

[@CodingIsFun]

Glad it was helpful!

[@soutasimportagencies]

Thank you very much for the excellent tutorial.

[@CodingIsFun]

Glad you liked it. Thanks for watching! 👍

[@kuuuyajim]

Wow! This is similar to the FILTER function but more advanced and easier to do if you know or prefer to use SQL for filtering. I can't think of a use case for my current job but it's a handy piece of knowledge to have. Thank you very much for sharing! 😉

[@CodingIsFun]

Thanks for watching. I totally agree. It is probably not what I need/use every day, but it is good to know and a great way to keep practising SQL.

[@michaelomosebi6374]

Thanks for this tip. I have a use case that will benefit from this FILTER function you just mentioned. Been on it for some days now. My PC keeps freezing once I input the sql function 😣😣

[@Mukdeni]

Thank you for sharing! This is very useful in my daily work

[@alanmusic3495]

This is just AMAZING! Is so much better than using python/pandas on the termina!!! I dont know why people arent talking about this.... I was searching for something like this and you were the only video I found. Keep it up!

[@CodingIsFun]

Happy to hear that it was useful; thank you for taking the time to leave a comment and for watching the video! Cheers, Sven ✌️

[@samaritanx9609]

Hi Sven, one more golden feather... shown great thing.. 💌

[@CodingIsFun]

Many thanks! 👍

[@sergeysergey421]

Very cool! I think Microsoft have to update Power Query tool for python and sql dialects. Pandas, sql have same language structure in my opinion.

[@CodingIsFun]

That would be cool!

[@iDea16369]

Thank you

[@CodingIsFun]

Glad you liked it. Thanks for watching.

[@hamzakrack]

Thanks man this is huge and it saves me a lot of time, but why excel doesn't have this included (or a similar feature) is beyond my understanding

[@CodingIsFun]

Glad I could help! Thanks for watching. Cheers, Sven ✌️

[@berbarmoumen1847]

❤❤❤😂😅 Merci beaucoup pour votre aide. From Algeria

[@CodingIsFun]

Thanks for watching! Cheers, Sven ✌️

[@alexrosen8762]

Extremely useful since I work a lot in Excel. Many thanks

[@CodingIsFun]

An absolute pleasure, very happy to hear that you found it useful!

[@gamenotfound9376]

Great video! Just a question, how did you empty the worksheet from cells and automatically format the query result? Thanks!

[@CodingIsFun]

Sorry, but I do not know what you mean

[@hasanmougharbel8030]

Hey there, God bless your efforts. I have only a general enquiry as a new sql learner. Suppose i created one table on sql management studio for data entry purpose... How can i compile this sql table into a windows application? In what compiler should i placed it? The reason i am asking that i would like to discover how an sql application is finally presented as an application... THanks for taking care of this

[@CodingIsFun]

Thank you for watching the video and for your question. Unfortunately, I do not know how to implement that right off the bat. I would also need to search the internet for a solution. Sorry that I cannot help!

[@PirieHart]

(1) How do you configure conditional formatting for output from the SQL() function? (2) Does the SQL() function support UPDATE or other SQL statements? Thank you for a very useful video!

[@CodingIsFun]

Thanks for watching! I'm not entirely certain about how or if it will work. Please feel free to give it a shot and see how it goes :)

[@grev201]

Is it possible to use variable declaration? Example, cell A1 has value as date declare @date_s date = A1 select * from table a where a.DateDocument = @date_s

[@CodingIsFun]

Thank you for watching the video and for your excellent question. Right off the bat, I am not sure if that is possible. I would suggest trying it out yourself. There is not much setup/installation required, as shown in the video. Happy Coding!

[@dwaipayanbandyopadhyay6110]

How to use each table separately ? Like to fetch data from table 2 i.e b we have to use a , is it possible just to fetch data from b or c ?

[@CodingIsFun]

Thanks for watching. I would suggest to download the sample file from the description. Afterwards, you can play around with your SQL queries and see if you run into any issues. Thanks!

[@bongrrjr]

Would you happen to have more SQL query sample? Thanks in advance.

[@CodingIsFun]

Thanks for watching. These are just some sample SQL queries. For your specific use case, you would need to write your own SQL queries.

[@rianne465]

Because of company policy, I have Sophos install and when I create the query, it shuts down Excel, because of 'malicious behavior'...

[@adbarlev]

When filling the function arguments, I get Formula result = "Could not load xlwings64-0.30.13.dll from either of the following folders: ... any suggestions?

[@CodingIsFun]

Thanks for watching. Please have a look here: docs.xlwings.org/en/stable/troubleshooting.html I hope it helps! Cheers, Sven ✌️

[@albertoghiglia9566]

Hi. I got this error message "Python process exited before it was possible to create the interface object..... Working Dir: ." Maybe there is a place where to specify a connection string for the SQL server database?

[@CodingIsFun]

Hard to tell from a distance. I assume this might be due to your Python Path settings: github.com/ericremoreynolds/excelpython/issues/52

[@ddmittal420]

Hi, thanks for the video. How do I convert datetime to date in the sql query of xlwings. The sql server functions like CONVERT don't seem to work. Thanks

[@CodingIsFun]

Thank you for watching. XLWings uses SQLite under the hood, so you might want to try using strftime. Please note that I have not tested it. Happy coding! Cheers, Sven! ✌️

[@ddmittal420]

@@CodingIsFun strftime worked for me. Thanks so much!

[@LateNightsAllDay]

I was on one of ur previous videos about dataframes to excel in python I have files that need to be extracted that were created with pandas etc I dont code but I need the data from these files anyway u could help?

[@CodingIsFun]

Thank you for watching the video. Kindly understand that I receive many requests for individual solutions or modifications. That said, you might want to join our Discord server (pythonandvba.com/discord ) and post your question there. Thanks for your understanding. Happy Coding!

[@LateNightsAllDay]

@@CodingIsFun I posted thank you hope its possible

[@alializadeh8195]

Thanx

[@CodingIsFun]

You're very welcome! Thank you for watching and for the comment!

[@pawekurantowicz3748]

Hey, I have a question with CONVERT metod, it's not working on EXCEL, is there a way to make it work?

[@CodingIsFun]

Thanks for watching. I am not sure about that. Sorry, that I cannot help!

[@jrabaona]

Why does Joseph Slatter write a book untitled "Subscribe" ? 😆😆😆 just jokin, thanks a lot for your amazing work

[@CodingIsFun]

haha 😅 Thanks for watching and your comment! :)

[@user-dj2hc9kp8n]

'xlwings' is not recognized as an internal or external command, operable program or batch file. what to to in this case, please help

[@CodingIsFun]

Hard to tell from a distance. My closest guess is that you are getting this issue because you are trying to import a module of a library which is not installed in your virtual environment. If you have run 'pip install xlwings' already, ensure to call your Python file from the same environment in which you installed the package. For example, in your IDE/text editor, you might have set a different Python environment for executing your script. Therefore, try to run your Python file also from the command prompt. If that does not work, try searching for the text of your error message on Google/StackOverflow. Happy Debugging!

[@kushagramishra7505]

How you have assigned "a" to the table ? Can we assign different name to the table if yes then how?

[@CodingIsFun]

I have not assigned an Excel table, but in the formula the first table is referred to "a", the second one to "b" and so on. To my knowledge, you cannot change the names.

[@MackieGamePlay]

Do you have sample for UPDATE Function?

[@CodingIsFun]

Nope, I have not tested it. Feel free to test it out yourself :)

[@MackieGamePlay]

@@CodingIsFun I encountered an error, it says 'NoneType' Object is not iterable My SQL is: UPDATE a SET JOB = 'SENIOR MANAGER' WHERE E_NO = 20;

[@syazwanizulkhairi9972]

Can we use update, insert, delete, create in this xlwings?

[@CodingIsFun]

Thanks for watching. I have not tried it out

[@richievellalar3214]

you can use Filter F right ?

[@CodingIsFun]

Thanks for watching. I have not tried that. Feel free to give it a try. Cheers, Sven ✌️

[@mikaelapurugganan8006]

One question. I was trying to get the yearly sales (I've been working on something). It says OperationalError: no such function: YEAR. Can someone please help me?

[@CodingIsFun]

Thanks for watching. Under the hood, xlwings utilizes SQLite. SQLite does not have a "YEAR" function, however you could try something like this: SELECT strftime('%Y', date_column) AS year FROM your_table; Good luck!

[@mikaelaq.purugganan4367]

@@CodingIsFun Thank you so much! You're a life savior.

[@GurinderSingh-mh1ln]

Hi please helo I m getting an error - File not found: xlwings64-0.11.8.dll

[@CodingIsFun]

Did you install xlwings and the add-in?

[@gamenotfound9376]

Problem: This add-in makes every resulting cell an Array. How can I convert the result into normal numbers/letters? I can't copy the results.

[@CodingIsFun]

Thank you for watching the video and for your question. Unfortunately, I do not know how to implement that right off the bat. I would also need to search the internet for a solution. Sorry that I cannot help!

[@2385boris]

I am getting a File not found: xlwings64-0.29.1.dll error. plz help

[@CodingIsFun]

Have a look here: docs.xlwings.org/en/0.11.6/troubleshooting.html#:~:text=dll%20and%20xlwings64.,pip%20or%20conda%20%2C%20see%20Installation.

[@2385boris]

@@CodingIsFun I have tried all this...but worth mentioning I am trying this in my Mac...shall this work in Mac?

[@jawadbari9481]

Hi, thank you so much, loved this, however im facing an issue my quries are working well, but when i try to find result by giving date parameter in my query its giving me blank result, for example my coloumn A of table having date in date formate "25-Jun-20" but how to write in query the date format so i can get the result of this date im tryping this in my where clause (where "Bill Date" = "20200625" but result is blank please help me in this

[@CodingIsFun]

Thank you for watching the video and for your question. Unfortunately, I do not know how to implement that right off the bat. I would also need to search the internet for a solution. Sorry that I cannot help!

[@jawadbari9481]

@@CodingIsFun I got the solution, although it costed me alot of time :P

[@CodingIsFun]

@@jawadbari9481 feel free to share it here :)

[@user-mx2tz6vd3g]

Can i execute INSERT query the same way ?

[@CodingIsFun]

Feel free to give it a try

[@mehakk_02]

I did.. but it said “sqlite3 operational error near insert”. I tried multiple times☹️

[@CodingIsFun]

@@mehakk_02 then I guess it's not possible :(

[@mehakk_02]

Oh!! Thanks anyways✌🏻

[@andfanilo]

Well I can again tell my students that learning SQL will get them anywhere, even using Excel 😃

[@CodingIsFun]

haha, that's right. Excel ♥

[@nikhilwaghalkar1271]

Getting error for second command xlwings is not recognised as an internal or external command.

[@CodingIsFun]

Did you install xlwings?

[@nikhilwaghalkar1271]

@@CodingIsFun yes

[@CodingIsFun]

@@nikhilwaghalkar1271 what have you tried to troubleshoot?

[@nikhilwaghalkar1271]

I reinstall xlwings with "pip install xlwings" but it is already present and then tried to run xlwings addin install but unfortunately getting same error xlwings not found as internal or external command.

[@samuelyiau1]

How can i write code more than 255 characters??

[@CodingIsFun]

Can you explain what you mean?

[@samuelyiau1]

Have you tried to run a bit mote complex code ? It is easily exceed char 255 which is the limited of a cell, can you solve it ?

[@CodingIsFun]

@@samuelyiau1 Yes. Excel cells can have chars > 255, see example: www.screencast.com/t/fflBEuiMrW

[@tonycooper3410]

I keep getting error "pip is not recognized"

[@CodingIsFun]

Have you installed Python and pip?

[@tonycooper3410]

@@CodingIsFun worked fine today. Not sure what happened the first 8 times. Thankyou

[@asifnaeem7872]

not working on large data set

[@CodingIsFun]

How large is your dataset?

[@asifnaeem7872]

@@CodingIsFun say 100 thousand record set

[@CodingIsFun]

@@asifnaeem7872 interesting. I have only tested it with a smaller dataset. Do you get an error message?

[@asifnaeem7872]

@@CodingIsFun yes. error like #value

[@physicsmostafa1774]

SQL is much clear and well documented than Microsoft DAX

[@CodingIsFun]

Thank you for watching the video and taking the time to leave a comment! 👍

[@Skbeuker]

Only windows! 😭I’m macOS

[@CodingIsFun]

Thanks for watching anyways! Cheers, Sven ✌️

[@crouzilles37]

pip isn't a recognised command

[@CodingIsFun]

Ensure that you have Python installed, as mentioned in the video

[@crouzilles37]

@@CodingIsFun Thank you, sorry I jumped the gun and should have listened a little more before trying :)

[@FelipeCampelo0]

Come on ! I need a solution so I can check my queries on any device, not only windows.

[@CodingIsFun]

Yes, unfortunately, it is only available for Windows. Nonetheless, thank you for watching. Cheers, Sven ✌️

[@wiseguy7224]

Du solltest an Deinen Akzent arbeiten. ;-)

[@CodingIsFun]

Ok

[@dheerajjain5754]

Thank you this fantastic trick. however, after intalling xlwings and xlwings addin, I am still getting an error. "ImportError: DLL load failed while importing _sqlite3: The specified module could not be found. from _sqlite3 import * File "C:\Users\Dheeraj.Jain\Anaconda3\lib\sqlite3\dbapi2.py", line 27, in from sqlite3.dbapi2 import * File "C:\Users\Dheeraj.Jain\Anaconda3\lib\sqlite3\__init__.py", line 23, in import sqlite3 File "C:\Users\Dheeraj.Jain\Anaconda3\lib\site-packages\xlwings\ext\sql.py", line 2, in from .sql import sql, sql_dynamic File "C:\Users\Dheeraj.Jain\Anaconda3\lib\site-packages\xlwings\ext\__init__.py", line 1, in File "", line 219, in _call_with_frames_removed File "", line 783, in exec_module File "", line 671, in _load_unlocked File "", line 975, in _find_and_load_unlocked File "", line 991, in _find_and_load File "", line 1014, in _gcd_import return _bootstrap._gcd_import(name[level:], package, level) File "C:\Users\Dheeraj.Jain\Anaconda3\lib\importlib\__init__.py", line 127, in import_module module = import_module(module_name)

[@CodingIsFun]

Thanks. Have a look here: docs.xlwings.org/en/stable/troubleshooting.html