How to Create an Excel Data Entry Form in 10 Minutes Using Python (No VBA) | Easy & Simple
Vložit
- čas přidán 28. 08. 2024
- 👉 Explore All My Excel Solutions: pythonandvba.c...
𝗗𝗘𝗦𝗖𝗥𝗜𝗣𝗧𝗜𝗢𝗡
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
Did you know you can use Python code to create an Excel Data Entry Form? This is a tutorial that will show you how to create one using the PySimpleGUI & Pandas library. No VBA or macros are required. At the end of the tutorial, we will also be converting the Python script to a standalone program. In doing so, we could share this data entry form with colleagues & friends, and they could use our program without having to install Python. The best part is that you could use this code as a starter template whenever you want to build a data entry form for Excel. If you want to add or remove columns, you only need to change one line of code. Additionally, you could also easily add more elements to the user form, like Multiline Input Fields, sliders, list boxes, a file dialogue box and much more.
PySimpleGUI Documentation:
▶ pysimplegui.re...
Full code and sample files are available here:
▶ github.com/Sve...
𝗧𝗢𝗢𝗟𝗦 𝗔𝗡𝗗 𝗥𝗘𝗦𝗢𝗨𝗥𝗖𝗘𝗦
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
🆓【𝗙𝗥𝗘𝗘】Excel Add-in (𝗠𝘆𝗧𝗼𝗼𝗹𝗕𝗲𝗹𝘁): pythonandvba.c...
📊 Dashboard Excel Add-In (𝗚𝗿𝗮𝗳𝗹𝘆): pythonandvba.c...
🎨 Cartoon Charts Excel Add-In (𝗖𝘂𝘁𝗲𝗣𝗹𝗼𝘁𝘀): pythonandvba.c...
🤪 Fun Emoji Excel Add-In (𝗘𝗺𝗼𝗷𝗶𝗳𝘆): pythonandvba.c...
📑 Excel Templates: pythonandvba.c...
🎓 My Courses: pythonandvba.c...
📚 Books, Tools, and More: pythonandvba.c...
𝗖𝗢𝗡𝗡𝗘𝗖𝗧 𝗪𝗜𝗧𝗛 𝗠𝗘
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
🔗 LinkedIn: / sven-bosau
📸 Instagram: / codingisfun_official
💻 GitHub: github.com/Sve...
💬 Discord: pythonandvba.c...
📬 Contact: pythonandvba.c...
☕ 𝗕𝘂𝘆 𝗺𝗲 𝗮 𝗰𝗼𝗳𝗳𝗲𝗲?
If you want to support this channel, you can buy me a coffee here: pythonandvba.c...
FANTASTIC video! In the first 3 minutes and 20 seconds you've taught the entire concept of PySimpleGUI. You're really good at this! 💖 the style, the clarity, the animations, and so much more about what you've done.
Thank you so much for your comment & this fabulous and simple to use library. I simply love it - with just a few lines of code, we can add a GUI to our program without messing around with the underlying frameworks. So clever and very well done! 😃👍
@@CodingIsFun Very nice. I have just started learning PySimpleGUI, last month. I want to build a project for my niece for garment shop. Hope I will achieve it soon with the help of experts like you. Thanks a lot. Keep it up.
This is the correct benchmark of technical teaching - I wish more technical trainers could adopt your clear framework for breaking down technical concepts for coding - thank you !
Thanks for the kind words! I am glad you enjoyed it.
Seriously I loved it. No one has this organized tutorial with documentation as implementation.... I'm in love
Thank you so much for your kind feedback! :)
🤷♂️ Do you have any questions or need further support? Let me know in the comments 😃
Can you please make a tutorial on how to store inputs received from PyQt5 inputs to mongoDB??
@@dishangmehta4397 Thanks for watching the video & your video suggestion. Sounds like an exciting video topic. I cannot make any promises, but l will see what I can do.
@GYAAN TECH Thanks for watching the video. In the PySimpleGUI Cookbook, there is an example of how to save the input:
'Recipe - Save and Load Program Settings'
pysimplegui.readthedocs.io/en/latest/cookbook/
Regarding the multiline input box:
As mentioned in the video, please have a look at the 'Demo' folder. PySimpleGui offers the following widget:
sg.Multiline(default_text='This is the default Text should you decide not to type anything', size=(35, 3))
Oh man, I'm just started to learn python, and find out that your channel has a bunch examples with 99% of all my needs and also real life examlples which I saw during past years while trying to achieve some automation via VBA. Keep up the good work ❤❤❤
Thanks for the kind comment, and happy to hear you're enjoying the videos!
By far this is the best tutorial video I have seen, clear instructions, demonstrations in theory behind each concept and easy to follow bite size steps, learnt more in this short video then in some hour long ones. Thank you for taking the time to produce this and share your expertise with the Community
So happy to hear that you found the video helpful. Thank you very much for watching the video and taking the time to leave a comment! :)
Bro, seriously, you are really the god of coding to me , it was the best tutorial i have ever seen
Thank you, mate! Happy to hear that the video helped you. Happy Coding! Cheers, Sven ✌️
WOW I am impressed. This video is friendly and right to the point. Love it. In 10 minutes I learnt more stuff than reading any book on this topic. Excellent Job!
Thanks for the positive feedback! Appreciate you taking the time to leave a comment. Cheers, Sven ✌️
This is a great video. I am a beginner at using python and coding. This will help me understand what a GUI is and allow me to create something nice for work. Thanks a lot!
Thank you for watching the video. I am glad to hear that the video was helpful. Happy coding! 😃👩💻
What more can I add than what has mentioned many times before? Simply a FANTASTIC video!! Thank you for your wonderful contribution.
Loved reading your comment! Thanks for the support! 👍❤️
Awesome sir god bless you
You explained it simply amd effectively and more important clearly
Thank you sir 🙏🏻
Thanks for leaving a comment! Happy you enjoyed the video! 🙏
Super impressed! Finally a clear demo of how to build a UI that writes to Excel via python. Thank you!
Hi Dennis G D! Happy to hear that you liked the video. Thanks for watching and taking the time to leave a comment! :)
Pythons for business in a nutshell 🤯🤯 So many ideas to work with this base...thanks
Happy to hear that it was useful; thank you for taking the time to leave a comment and for watching the video!
This video is awesome. The concision, clarity and simplicity put in the explanations are impressive! Thanks and please do keep up!!!
Thank you so much for your positive feedback! I appreciate it! 😃
Love the voice/accent. Cross between a man child and very intelligent man. Love the vids too. Will sub
Hey Chris! Thanks for watching and subscribing, welcome aboard! 👍
Hello Sven, I can just add another positive reaction to all the others you already received, WOW WOW what a great tutorial! Thanks for this amazing work and I guess the Python community should build a statue for you!
Regards, Frank
Hey Frank,
Thanks for watching and leaving such a kind comment! I really appreciate it. 🙏
I'm glad you found the tutorial helpful. Happy Coding! 🚀
Didn't know this was possible so easily. Very practical!
Thank you for watching the video, Florian. Glad it is helpful!
This is top notch content ..hope you gain more subscribers
Thank you 😃🙏
Xlent video.. ❤️simple and clear Sven..U shortened my learning with this video.. if possible.. please make video on update, search and delete options on this topic itself...which will enhance knowledge of ur followers. ❤️ My advanced wishes...
Thanks for your kind words & video suggestion! I appreciate it! :)
We have same opinion and waiting.
Very well explained!! Thank you!
Glad you liked it. Thanks for watching. Cheers, Sven ✌️
Good job on the video! Clear, concise and good presentation. I really like the code animations. Looking forward to watching more of your videos.
Thank you. Glad you like the video :)
Amazing tutorial and truly i am very much impressed for your knowledge and sharing such a beautiful content to Enthusiast Pythonist developers.
Happy to hear that you liked the video; thank you for taking the time to leave a comment and for watching the video! 👍
@@CodingIsFun sir not only liked your video, subscribed.as well
I am a novice, and I need guidance on how to install the Command Prompt. The Windows Command Prompt returns errors only. Thank you.
BTW. Great job! Straight to the point, and it is exactly what I needed. Regrettably, I am not a programmer and I have difficulties to install the tools to code and follow your instructions. Thanks again.
Thanks for watching. The actual error message would be helpful if you would like some guidance. Thanks! Cheers, Sven ✌️
Thinking about the many ways I could use this method.
Thank you so much for this. Please keep up the tremendous work.
This one's a real gem.
*I am glad you liked the video; thanks for watching and the kind words.* 😀
Best tutorial on the net
Thanks for the kind words! I am glad you enjoyed it. 👍
you just got another subscriber , this is so useful and relevant in my field of work . Just started out with python and your explanation is so clear and concise . Thank you brother.
Happy to hear that it was useful; thank you for taking the time to leave a comment and for watching the video!
Man I wish I'd found your channel earlier!!! This is super useful!!!
Happy to hear that you find the video useful! Thanks for watching and commenting! :)
i enjoy all your tutorials. this was the 4th i have followed along. i am learning so much
I am happy to hear that you have been finding the videos helpful. Thanks for taking the time to leave a comment and for watching!
Combining this concept within a streamlit app [taking user input and recording into an excel spreadsheet] would be first class.
Thank you for watching the video & your suggestion.
I'm soooooooooooooo happy to see this. Much needed video. I was searching for this only.
Glad you found it. Hope it is helpful :)
Super!!explained in a very simple way but very effective..so many things learnt in a such a small video!!great work sir!!
Happy to hear that. Thanks for watching and your kind words!
One of the best tutorials I have seen. Thank you
Thank you!
As always, your content is pure gold!
Thank you for your kind words :)
Wow you made that so easy to understand. This topic was intimidating for me being new to coding. Thank you for the vid :)
Glad to hear you liked it! Thank you for commenting and watching. 😃
This is great! Is there any way to add a field of day/date that would automatically update without having to input it each time?
Thanks so much for watching the video and leaving a comment! Your request is definitely noted. However, I get a ton of requests for custom solutions and, as much as I'd love to help everyone out, I just don't have the time in my schedule to develop and test all of them. I hope you can understand. Happy Coding!
This is so fun, thank you so much! I'm happy I found this channel!
Happy to hear that you liked the video!
Sorprendente, es un super video, breve claro, práctico y muy educativo. Muchas gracias
Thanks for the kind words! Glad to hear you found the video helpful and educational. 👍
Wow .very nice video...thanks ..
Glad to hear you liked it! Thank you for commenting and watching. Cheers, Sven ✌️
Love it I already reply this project it work 🎉
Glad to hear you liked it! Thank you for commenting and watching. Cheers, Sven ✌️
Bro you are great. You taught exactly what I needed. Love you man
Happy to hear that it was useful; thank you for taking the time to leave a comment and for watching the video! 👍
Thanks for the video. I am going to start using this. Good work! Hope to see more tutorials.
Thanks for watching the video and taking the time to leave a comment! :)
Most clear and cleaver video I've ever seen >>>>>> good job
Thank you very much! :)
@@CodingIsFun Although there is a problem i hope you can help me with , the script wont convert to .exe , whenever i tried it says (IndexError: tuple index out of range)
, dist folder is empty as well
everything is updated
i'm using Visual Studio and the script work fine with Visual Studio and from the Command prompt
I would really appreciate if you know the answer
Regards
You explain really well. I’m feeling motivated to automate a task
Thank you for watching the video and taking the time to leave a comment. I am glad you liked the video.
This is great! Thank you a lot!
My pleasure! Appreciate you taking the time to watch and leave a comment.
i am python biggner,
thanks and more helpful
Glad you liked it. Thanks for watching.
...wow..Very good video...thank...
Thank you! :)
i have impressed with your tutorials .please show us save delete and edit button and report generation based on the saved data
Thank you very much.
For retrieving/editing data, have a look at the following short example:
czcams.com/video/RnTqlKzQhRY/video.html
I hope this helps!
Awesome, thank you.
You're very welcome! Thank you for watching and for the comment!
Awesome work 😀
Thank you!
This was beautifully taught.
Thank you.
Thanks for the kind words! I am glad you enjoyed it. 🤩
This is great! I would like to know if we share the file and employees fill on their desktop if the information start updating on its own
Thanks for watching. Sorry, but I am not sure what you mean.
Great Work Sir...
Thanks for the kind words! I am glad you enjoyed it.
that was very well done! thanks for this! so glad i know about PySimpleGUI now!
Happy to hear that it was useful; thank you for taking the time to leave a comment and for watching the video! 👍
Thank you for the video. I was using the append with concat when I was trying Web Scraping, but it was very confusing when dealing with multiple page scrapes because I'm a beginner. I saw someone with pdf extraction use something called a namedtuple for combining values, and appending a list which is then put into a dataframe. That method seemed simpler to me.
Thank you for watching the video and taking the time to leave a comment!
Well done nice little video! Thanks for this!
Glad you enjoyed it! Thanks for watching and taking the time to leave a comment! 👍
I love your channel
It's very kind of you to say so, thank you! 🙏
This is very useful. Thanks Sven!
Thank you! Glad you like the solution!
Very Good!
There is a complete application with Save, Read, Update and Delete.
Thanks!
No, I do not have a 'complete' CRUD application.
@@CodingIsFun Great!
Excellent video! Thanks for the help!!
Glad I could help! Thanks for watching! 👍
Thanks for this great example.
An absolute pleasure, very happy to hear that you found it useful! 👍
congratulations, thank you for sharing your knowledge
Thank you for watching the video and taking the time to leave a comment! 👍
Thanks for making this so simple to understand
Glad it was helpful! Thanks for watching the video & taking the time to leave a comment :)
Great video, subscribed
Thanks for the sub! :)
Hello Man, The way you are explaining is excellent!!! Thanks a lot, Please tell me Do you have similar tutorial or code where Data Entry is My SQL intesad of excel? Thanks in advance
Thanks for watching. Nope, I do not have a tutorial on that topic.
Fantástic video. Thanks you very much. One question: what IDE you use? I'm using Visual studio code...
Thank you. In the video I used 'Atom'.
awesome tutorial... could you also please make a tutorial on "Copy pasting cells within an excel file using python". something like: copy only the entire (rows) cells and columns in which data is, and then paste this after the last data column.
Thanks for watching and for your suggestion. I have done something similar already in the past. You can find the video here: czcams.com/video/JoonRjMsSdY/video.html
I hope it helps! Happy Coding!
Another great video discover
Happy to hear that you enjoyed this one too! Thanks for the comments! :)
Very beautifully explained....very informative
Thanks for the kind words! I am glad you enjoyed it.
Amazing tutorial. Thanks for your work. I have a question tho. What should I do if i need some of the fields of the excel file to be filled always with the same data?
I'll explain this. I have to worl with a boring CSV File in wich i have to manually fill only 2 or 3 cells, and the remaining (a total of 10 in a line) they have always the same data. A boring work of copy/paste for hundreds of lines. This will help me a lot if I could apply your teaching on my needs.
Thank you
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!
can you please make video on how to stack screens in PySimpleGUI.
Thanks for watching the video & your video suggestion. You might want to check out the following article on how to work with multiple windows using PySimpleGUI:
www.blog.pythonlibrary.org/2021/01/20/pysimplegui-working-with-multiple-windows/
I hope this helps! Happy Coding! 😀
What is amazing video, many thanks!
Glad you liked it. Thanks for watching and taking the time to leave a comment!
amazing video, one of the most valuable 3min i spent on youtube.
would you show how we read combo information from mySQL db instead of hard code it in the program?
thanks
Thanks for watching the video. Happy to hear that you found the video valuable.
In essence, PySimpleGUI is 'just' used to create the GUI. If you want to manipulate a MySQL database, I would suggest coding out your pure Python script first (without GUI).
Once you got your CRUD (Create/Read/Update/Delete) functions in place (or whatever you want to do) when you can build the GUI using PySimpleGUI.
To get started with MySQL + PySImpleGUI, check out the following post: stackoverflow.com/a/622308
Happy Coding! 🚀
When can we have Panel dashbord tutorials? hahaha.. I cant wait!
Too many interesting topics and not enough time 😅
@@CodingIsFun I really understand. I was looking through CZcams for a series on Panel.. it's kindda some topics here and there. It's an idea of the next topic. Keep the good work man :)
At your pace 🙂
Excellent.
Thank you!
Hey ...question....(awesome tutorial btw...)...can the executable file be placed anywhere in the computer and it will add to the excel sheet (like in the desktop) or it has to be in the same folder as the excel sheet? Does data entry.py has to also be in same folder? Thank you for any reply.
Thanks! The Excel file does not need to be in the same directory.
You need to adjust the following to your needs:
current_dir = Path(__file__).parent if '__file__' in locals() else Path.cwd()
EXCEL_FILE = current_dir / 'Data_Entry.xlsx'
df = pd.read_excel(EXCEL_FILE)
Subscribed! Thank you so much for the tutorial, I'm just a beginner in python but this really helped me building a simple database. It could be useful to upgrade the form allowing to modify the excel data once they are stored. Could you point me in the right direction? Thanks again!
Thanks so much for watching the video and leaving a comment! Your request is definitely noted. However, I get a ton of requests for custom solutions and, as much as I'd love to help everyone out, I just don't have the time in my schedule to develop and test all of them. I hope you can understand. Happy Coding!
Hi Sven! Thank you so much for such an informative video! I have a question: what happened if we have multiple sheets in our excel file we want to enter the data into instead of the default sheet 1? Thank you in advance!
You could export it to an existing/specific sheet. Here is an example of how to do it in Pandas: czcams.com/video/DroafWQXqDw/video.html
Great video Thanks for this, I have one question y supossed that when I share the exe file needs to be with the excel and has to be in the same path right? To change this path I could implement other window with option to change the path or the file to append data?
Thanks for watching. You are right. Currently, the application is looking for an Excel file in the same folder. Yet, you can select the Excel file dynamically by inserting the 'FileBrowse' Element:
pysimplegui.readthedocs.io/en/latest/call%20reference/
www.screencast.com/t/64AUEVrzN
I hope it helps! Happy Coding! :)
Very helpful tutorial, thx a lot!
Thanks for the positive feedback! Appreciate you taking the time to leave a comment.
Hello! Excellent presentation!
But I got a few questions.
How I Can add a timestamp in a separate Excel column every time someone click on Submit?
Thank you!
Hi Alex. Thanks for watching. I have created the following solution for you: gist.github.com/Sven-Bo/71251c76e3e3e7183dc6135b371f8c14
Hope you like it! Happy Coding!
This is seriously impressive. 👏
Thank you very much!
Thank you❤, really appreciate your efforts
Glad you liked it. Thanks for watching.
Ultimate bro
Thanks! 💪
So I was wondering here and I would not know how to code. What if im going to enter information of a person or business, just to find out the business or person is already in the excel form? What could I do if I wanted to browse for a file and then click "enter" and then voila, all the pertinent fields would be filled automatically? Is this a hard thing to accomplish?? On and off I stop by because I simple love everything you do here, congratulations and thank you for all !!!!
Thanks for watching and for your question. That is possible. A while ago, I created a quick example that shows you how to retrieve values from the Excel file. You can find it here: czcams.com/video/RnTqlKzQhRY/video.html
While it is not exactly what you want, I hope it is a good starting point. Happy Coding! :)
I was looking for a way to get data from and Excel file to use in an online web form filling. I've done this successfully using Power Automate Desktop but now I don't have a license. Looked at AHK but without the coding skill it's beyond my scope for now. Watched your video using chatGPT to write Python automation BUT I guess this is a bit more challenging as each input field needs to be mapped in Python. How deep would this be?
Hi great video, clear and concise.
I do have one question though, having watched several videos on this kind of topic, I have noticed that Excel and Word files are used, why not Libre Office files?
Simple answer for my videos: I only have MS Office installed ;)
thanks my friend...
Thank you for watching the video and taking the time to leave a comment!
Thanks for the helpful video! is it possible also to make some calculations before insert the data into the excel? How to do it? thks
Thanks for watching. Yes, it is possible to perform calculations before inserting the data into Excel. You can do this by manipulating the data in the "values" dictionary or the "new_record" DataFrame. Happy Coding!
Instead of adding true or false in reach row (for German, Spanish, English) is it possible to let the script explicit write that down in excel with the data entry form? Also great tutorial!
Thanks so much for watching the video and leaving a comment! Your request is definitely noted. However, I get a ton of requests for custom solutions and, as much as I'd love to help everyone out, I just don't have the time in my schedule to develop and test all of them. I hope you can understand. Happy Coding!
The Thing is you have to put the file path instead of just filename while assigning it to EXCEL_FILE varaiable
That is correct. But the file is in the same folder as my Python file. Hence, it also works that way. If you want to be more explicit, you can use something like this:
from pathlib import Path
current_dir = Path(__file__).parent if "__file__" in locals() else Path.cwd()
excel_file_path = current_dir / "NAME_OF_YOUR_EXCEL_FILE.xlsx"
df = pd.read_excel(excel_file_path)
I hope it helps! Happy Coding! :)
Really loved the way you demonstrated the use of PysimpleGUI ...... cheers and thanks 🙌🏻🙌🏻🙌🏻🙏🏻🙏🏻
this is what I need. thanks man.
+1 subscriber from me
Great! Welcome aboard! :)
Very cool!!
Thanks! :)
Great video. I am a newbie to Python. Everything is working great however, I get a warning when I do a submit basically saying FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead. Can you point me to a viseo that explains how to take what you did here and use .concat?
Hi Bryan Leary.
Thanks for watching the video & your question.
I have updated the code in the GitHub repo ( github.com/Sven-Bo/data-entry-form-pysimplegui ).
Please see below the change:
OLD:
df = df.append(values, ignore_index=True)
NEW:
new_record = pd.DataFrame(values, index=[0])
df = pd.concat([df, new_record], ignore_index=True)
I hope this helps!
Thank You so much for the video, it was very helpful. Exactly what I was looking for to complete a project at work. But I have a question, after generating that "little application" that prompt our form, how do I share it with other people?
7:40 min
Please explain how I can program in Python a button inside my application to select files, that is, when I click on it, I can choose files from the device? Then we modify it without opening the file "...I mean excel files
Check out the following tutorial: czcams.com/video/_H5hsUwv8lE/video.html
I hope it helps! Happy Coding!
Super,,, you are making our life simpler
*Hello nagendra vishwamitra, thanks for watching the video.* Glad it was helpful!
Sir, could you please give the code for pyinstaller --onefile--noconsole data_entry.py in jupyter notebook
@@nagendravishwamitra3652 The code would be the same - you could run it in a Jupyter Notebook cell, or do you get any errors?
Hey, really a great tutorial. This I was looking for. Let‘s see if there is also an app to display the content of the excel file.
Thanks! Here is a small example of how to retrieve data: czcams.com/video/RnTqlKzQhRY/video.html
Perhaps it already helps. Happy Coding!
Thanks for sharing
A pleasure. Thanks for watching!
Really thank you very much , you are a great teacher , and i have question please , i need to know how can we make form size bigger than this
Thank you! You can change the layout as follows:
window = sg.Window('Simple data entry form', layout ,size=(290, 50))
Great content and the way you delivered the info. It works perfect when using the terminal, but the .exe file doesn't save the values in excel. all the other buttons are doing what are supposed to do, not sure why the save one is not doing it's job. Many thanks for your work, you got another subscriber. :)
Thanks for watching and the kind words. Regarding your issue:
Assuming you took the latest code from the GitHub repo:
Please change the following:
Remove: current_dir = Path(__file__).parent if '__file__' in locals() else Path.cwd()
Add: current_dir = Path.cwd()
..and try again. I hope it helps! Happy Coding!
@@CodingIsFun Thank you very much, it works now :) I'm new with python so it might have taken a long time for me to figure this out