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.
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!
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!
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! 😉
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 😣😣
@coding is fun - But we have column which is blank rest others columns have value then how to filter the blank value with related columns with it .....
Thank you very much for watching the video and your comment. Your requirements are well noted. Yet, I receive many requests for creating individual solutions. As much as I want to help, I simply do not find the time in my daily schedule to develop & test all the different requests. I hope you can understand. Thank you! -Sven
(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!
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
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!
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
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! ✌️
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!
I am facing the issue please help me out but already installed ( pip install xlwings ) C:\Users\sibis>xlwings addin install 'xlwings' is not recognized as an internal or external command, operable program or batch file. C:\Users\sibis>
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
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!
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?
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.
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!
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.
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?
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!
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?
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!
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
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!
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?
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!
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)
*Do you use SQL at your job?* 🤔
I get the error: "Could not activate Python COM server, hr=-2147221164" 😣😣
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.
Thank you sir, do you have any tutorial using dynamic SQL instead of statik syntac SQL in excell?
@@channelbtv2903 nope
Can this work with Oracle ?
If yes, how do I go about doing it .
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!
Ah, you're making me blush! Thanks for the kind comment and happy to hear that you're enjoying the videos! ♥💪
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!
Happy to hear that it was useful; thank you for taking the time to leave a comment and for watching the video! Cheers, Sven ✌️
I love SQL and hate pivot tables - This is awesome!
Thanks!
Glad I could help! Cheers, Sven ✌️
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! 😉
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.
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 😣😣
Thank you for sharing! This is very useful in my daily work
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
Glad I could help! Thanks for watching. Cheers, Sven ✌️
Exactly what I was looking for. Excellent!!!!! Thanks😊👍
Happy to hear that it was useful; thank you for taking the time to leave a comment and for watching the video!
Hi Sven, one more golden feather... shown great thing.. 💌
Many thanks! 👍
This is great! No idea this could be done
Glad you liked it. Thanks for watching and taking the time to leave a comment!
@coding is fun - But we have column which is blank rest others columns have value then how to filter the blank value with related columns with it .....
Thank you very much for watching the video and your comment. Your requirements are well noted. Yet, I receive many requests for creating individual solutions. As much as I want to help, I simply do not find the time in my daily schedule to develop & test all the different requests. I hope you can understand. Thank you! -Sven
Because of company policy, I have Sophos install and when I create the query, it shuts down Excel, because of 'malicious behavior'...
Would you happen to have more SQL query sample? Thanks in advance.
Thanks for watching. These are just some sample SQL queries. For your specific use case, you would need to write your own SQL queries.
(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!
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 :)
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
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!
Extremely useful since I work a lot in Excel. Many thanks
An absolute pleasure, very happy to hear that you found it useful!
you can use Filter F right ?
Thanks for watching. I have not tried that. Feel free to give it a try. Cheers, Sven ✌️
Thank you very much for the excellent tutorial.
Glad you liked it. Thanks for watching! 👍
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.
That would be cool!
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
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! ✌️
@@CodingIsFun strftime worked for me. Thanks so much!
Can we use update, insert, delete, create in this xlwings?
Thanks for watching. I have not tried it out
Great job ! Thanks for sharing !
You're very welcome! Thank you for watching and for the comment!
❤❤❤😂😅 Merci beaucoup pour votre aide. From Algeria
Thanks for watching! Cheers, Sven ✌️
'xlwings' is not recognized as an internal or external command,
operable program or batch file. what to to in this case, please help
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!
Great video! Just a question, how did you empty the worksheet from cells and automatically format the query result? Thanks!
Sorry, but I do not know what you mean
I am facing the issue please help me out
but already installed ( pip install xlwings )
C:\Users\sibis>xlwings addin install
'xlwings' is not recognized as an internal or external command,
operable program or batch file.
C:\Users\sibis>
Thanks for watching. Please try the following:
chatgpt.com/share/738b8329-f7eb-4fae-acdf-f51f3f08d924
Cheers, Sven ✌️
@@CodingIsFun i already tried in chat gpt and online blogs also still facing the issue i raised the issue in github xlwings repo i will check
@@sibisanjai741 good luck 🤞
Can i execute INSERT query the same way ?
Feel free to give it a try
I did.. but it said “sqlite3 operational error near insert”. I tried multiple times☹️
@@mehakk_02 then I guess it's not possible :(
Oh!! Thanks anyways✌🏻
Do you have sample for UPDATE Function?
Nope, I have not tested it. Feel free to test it out yourself :)
@@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;
Hey,
I have a question with CONVERT metod, it's not working on EXCEL, is there a way to make it work?
Thanks for watching. I am not sure about that. Sorry, that I cannot help!
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
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!
File not found: xlwings64-0.33.3.dll
I'm getting this error when I run the sql query in the excel sheet. Could you please help me?
Here you are: docs.xlwings.org/en/stable/troubleshooting.html
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?
Thanks for watching. Please have a look here: docs.xlwings.org/en/stable/troubleshooting.html
I hope it helps! Cheers, Sven ✌️
How you have assigned "a" to the table ? Can we assign different name to the table if yes then how?
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.
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 ?
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!
Hi please helo I m getting an error - File not found: xlwings64-0.11.8.dll
Did you install xlwings and the add-in?
Getting error for second command xlwings is not recognised as an internal or external command.
Did you install xlwings?
@@CodingIsFun yes
@@nikhilwaghalkar1271 what have you tried to troubleshoot?
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.
This so much fun. Will definitely use it. 💯
Glad it was helpful!
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?
Hard to tell from a distance. I assume this might be due to your Python Path settings: github.com/ericremoreynolds/excelpython/issues/52
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.
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!
How can i write code more than 255 characters??
Can you explain what you mean?
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 ?
@@samuely1-v3z Yes. Excel cells can have chars > 255, see example: www.screencast.com/t/fflBEuiMrW
really intriguing
Thanks!
The command 'xlwings addin install' is not working
What does "not working" mean?
It throwing up the message "'xlwings' is not recognized as an internal or external command,
operable program or batch file."
@@HeartistMurali did you install it?
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?
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!
@@CodingIsFun Thank you so much! You're a life savior.
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
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!
@@CodingIsFun I got the solution, although it costed me alot of time :P
@@jawadbari9481 feel free to share it here :)
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?
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!
@@CodingIsFun I posted thank you hope its possible
I keep getting error "pip is not recognized"
Have you installed Python and pip?
@@CodingIsFun worked fine today. Not sure what happened the first 8 times. Thankyou
not working on large data set
How large is your dataset?
@@CodingIsFun say 100 thousand record set
@@asifnaeem7872 interesting. I have only tested it with a smaller dataset. Do you get an error message?
@@CodingIsFun yes. error like
#value
Well I can again tell my students that learning SQL will get them anywhere, even using Excel 😃
haha, that's right. Excel ♥
Why does Joseph Slatter write a book untitled "Subscribe" ? 😆😆😆 just jokin, thanks a lot for your amazing work
haha 😅 Thanks for watching and your comment! :)
Thank you
Glad you liked it. Thanks for watching.
SQL is much clear and well documented than Microsoft DAX
Thank you for watching the video and taking the time to leave a comment! 👍
Only windows! 😭I’m macOS
Thanks for watching anyways! Cheers, Sven ✌️
pip isn't a recognised command
Ensure that you have Python installed, as mentioned in the video
@@CodingIsFun Thank you, sorry I jumped the gun and should have listened a little more before trying :)
Thanx
You're very welcome! Thank you for watching and for the comment!
Come on ! I need a solution so I can check my queries on any device, not only windows.
Yes, unfortunately, it is only available for Windows. Nonetheless, thank you for watching. Cheers, Sven ✌️
Du solltest an Deinen Akzent arbeiten. ;-)
Ok
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)
Thanks. Have a look here: docs.xlwings.org/en/stable/troubleshooting.html
I am getting a File not found: xlwings64-0.29.1.dll error. plz help
Have a look here: docs.xlwings.org/en/0.11.6/troubleshooting.html#:~:text=dll%20and%20xlwings64.,pip%20or%20conda%20%2C%20see%20Installation.
@@CodingIsFun I have tried all this...but worth mentioning I am trying this in my Mac...shall this work in Mac?