Using Excel .xlsx files with Python! OpenPyXl Tutorial

Рет қаралды 62,527

2 жыл бұрын

This tutorial shows you the basics of installing the OpenPyXl module, how to access data and information out of an Excel file, and then how to read the data, modify the data, and save changes!
This is a beginner introduction to using excel with python, if you want to see more advanced operations and automation, let me know in the comments!
Check out my personal channel for more fun science content!
/ @peterlemaster8310
Thanks for watching and good luck with your code!

Пікірлер: 74

@surprisemawela525 5 ай бұрын

I struggled for more than a month with openpyxl but you helped me in less than 10 minutes! Thanks you so much

@lemastertech 5 ай бұрын

Hey you are very very welcome!! Thank you for watching and for the super nice comment 😁

@alexmckinley79 11 ай бұрын

Thanks a lot. I appreciate that you explain the things beginners wouldn't know intuitively.

@lemastertech 11 ай бұрын

Thanks for watching! I try to not assume anyone watching has already done any advanced stuff!!

@shankphadke 6 ай бұрын

Thank you that was a perfect tutorial - short precise and no nonsense - Awesome job

@lemastertech 6 ай бұрын

Thanks so much for watching and for the nice comment!! I try to be useful without wasting peoples time 😊

@rethanon 2 жыл бұрын

Good to see your subscriber numbers going up, you were under 400 not so long ago, now almost at 1,000! And thanks for taking up my suggestion of zooming in a bit 👍🏻

@lemastertech 2 жыл бұрын

Thanks Dale! That was a great suggestion so I really appreciate it! And yeah free code camp .org posted my best maker project to their channel and I think alot of folks found me through that which is exciting!

@AlejandroGarcia-xp5uv 3 ай бұрын

Great video! Tahnks for this clear and brief basics of openpyxl

@lemastertech 3 ай бұрын

You’re super welcome thank you for watching!!

@nanbolkeza8296 2 ай бұрын

Thanks for the great lecture. Pls I will like to see more data manipulations using excel and python.

@lemastertech 2 ай бұрын

Thanks for watching!! I’ll add more python and excel projects to my to do list!!

@jrpeet 11 ай бұрын

Super helpful - thanks!

@lemastertech 11 ай бұрын

You’re very welcome thank you for watching!

@saptanilchowdhury1851 11 ай бұрын

Really enjoyed this explanation. Thanks :)

@lemastertech 11 ай бұрын

You’re very welcome thank you for watching and for the nice comment!

@girikumar5536 6 ай бұрын

Thank you very much for this tutorial

@lemastertech 6 ай бұрын

You’re very welcome, thanks for watching!!

@Hakeem357 3 ай бұрын

Very helpful. Thank you and good luck. I subscribed

@lemastertech 3 ай бұрын

Thanks so much for watching and the nice comment, and welcome to the channel!

@swedishpsychopath8795 9 ай бұрын

This wasn't half bad. Thank you!

@lemastertech 9 ай бұрын

Hey that comment wasn’t half mean, you’re welcome thanks for watching! 😊

@pranavnyavanandi9710 2 жыл бұрын

Watched it, good to know. Could you make a video covering properties and the descriptor protocol in python? I never got to understand them.

@lemastertech 2 жыл бұрын

Hey that’s a good idea! Might be a little advanced for some of my viewers but I think it could be a useful concept on a future video! If you need any quick tips on it, this is a pretty good summary too! docs.python.org/3/howto/descriptor.html

@JohnEloi Ай бұрын

Thanks a lot!

@lemastertech Ай бұрын

You’re super welcome thanks for watching!

@kvelez 2 ай бұрын

from openpyxl import Workbook, load_workbook book = load_workbook("menu_items.xlsx") sheet = book.active #primary sheet in excel sheet['A2'].value = "Food" book.save("food_items.xlsx")

@lemastertech 2 ай бұрын

Thanks for sharing!

@FitnessPal99 3 ай бұрын

helped a tone man

@lemastertech 3 ай бұрын

Rad!

@SawekGraczyk 10 ай бұрын

Thanks a lot, awesome and very practical video could recommend more videos (yours or others) how to automate work in Excel and other MS office apps is Python effective to automate daily boring tasks on the computer? more recommends much appreciated Cheers 🙂

@lemastertech 10 ай бұрын

That’s a good idea for a future video!! Thanks for watching and leaving that suggestion!

@sidellelyne1378 10 ай бұрын

Hi, from your video , i can see you have a lot of experience on VBA. Will there be a way of optimizing VBA functions to go more faster? I've been implementing macros that take a LOT of time while running. What can I do to optimize the code and will computing this on Python could accelerate procedures?

@lemastertech 10 ай бұрын

Hi - speed in modern programming languages is all based on how optimized your code is. Try to stop in for loops or while loops as soon as you find what you’re looking for using things like break or return, avoid if chains and nested loops if there are other things available, and most of all try to only grab as small a sized dataset to work with as you can initially

@luisgabrielcastilloherrera8020 2 ай бұрын

Thank you so much for the amazing tutorial, is their a way to acces the information while the file is opened or being modified while the code is being executed

@lemastertech 2 ай бұрын

This method will actually work while the file is open but it’s not really meant to constantly update while a file is also actively being worked on in excel! You’d need a pretty frequently updated while loop to achieve something like that!

@KuriVideosOficial 8 ай бұрын

Hi, if I wanted to show (on a table format) the content of every row but just for specific columns, how could I do it?

@lemastertech 8 ай бұрын

Hi, do you mean in python because you can just hide columns in excel if you only want some columns displayed?

@ahmedthebest 2 жыл бұрын

Drum Machine with Python and Pygame - Full Project Course I am a new subscriber I just subscribed here right now after seeing this video

@lemastertech 2 жыл бұрын

Thanks friend! Welcome!

@ahmedthebest 2 жыл бұрын

@@lemastertech I hope to see projects video like that 😸

@nirmaljeyan893 6 ай бұрын

Thanks but how do I import the excel file to pycharm? For example the menu file needs to be first added to pycharm right? I'm using a community version of pycharm I'm not able to find an option to add any excel files to pycharm

@lemastertech 6 ай бұрын

Hi, so you can just copy/paste or drag/drop it into the same folder you’re working in in pycharm. Or you can import the os module and navigate to where in your computers folder structure it lives!

@Paul-ly1pw 2 жыл бұрын

I’m looking at a way that I can create a very interactive rota using python so maybe a tutorial on how to design a rota/scheduler in excel but coding in python rather than having to use VBA

@lemastertech 2 жыл бұрын

That would be a cool idea! I don’t have much experience in rotas myself so I’ll have to do a bit of research, what type of features and functions would you want to see??

@Paul-ly1pw 2 жыл бұрын

@@lemastertech maybe a skill based rota so people are selected depending on their skills. Add, modify and delete someone Dropdown selections depending on their skills, so they only appear in lists they have skills for. Date picker pop up to select dates for the schedule Print/export buttons to print out or export to pdf Email or WhatsApp buttons to send reminders. Also the option to have the reminder go as a calendar reminder on their mobile device.

@clarke3481 2 жыл бұрын

Can you do a full course on how to make word chain game with use of sql database for dictionary and also how can we make bot that plays this game and also a bot that conducts this game? Also banned letter etc modes

@lemastertech 2 жыл бұрын

That would be interesting! Thank you for the idea, I will have to read up a bit on word chain games since there are alot of different types! And sql as a database is a good idea that would be an awesome tutorial I will working on a project for that!

@toddpoon Жыл бұрын

why must the excel book be closed in order for the code to work? is it possible that it can be kept opened while running it?

@lemastertech Жыл бұрын

Hi, you can leave it open while it is running but you want to make sure to close the book before finishing your program! If you leave an excel book open but your python file finishes then changes will not be saved!

@hussainkhan8095 Ай бұрын

can you please tell me how to remove grid lines from xlsx in odoo or using python code

@lemastertech Ай бұрын

Why do you need the grid lines removed? That’s like a very basic features of excel I’m not sure how or why I’d want to remove them?

@NDxmix0x0 8 ай бұрын

whenever I try to run the code it tells me that openpyxl isn't installed, I installed it using cmd but I'm running the code using IDLE, could that be why it isn't working? edit: nevermind, I uninstalled/reinstalled and it started working

@lemastertech 8 ай бұрын

Good fix!!

@farihahasan354 Жыл бұрын

how did you save that excel sheet in pycharm?

@lemastertech Жыл бұрын

You can do a file.open with the write instruction and it will create a csv with that name in the same project folder! Goodluck!

@itsdarkoboy706 Жыл бұрын

For some reason the library expects the file to be a zipfile on my linux system, even though i specified the filename with xlsx at the end :(

@lemastertech Жыл бұрын

Hi! I am sorry I actually have not personally used this module on linux before, but you might also need the filestream module since linux handles all doc types differently! But I honestly am not sure what the specific fix for linux would be - they have decent online documentation for the openpyxl module though! Goodluck!

@ByronWatts 6 ай бұрын

Remember that an xlsx file IS a zip file. Just changing a file name isn't enough

@user-wr9wr8vh7t 3 ай бұрын

Can i use it in vs code or no

@lemastertech 3 ай бұрын

Yes the IDE you use shouldn’t matter

@ErdianRahmanaPutra 8 ай бұрын

Can this code be used while the excel is open?

@lemastertech 8 ай бұрын

I believe it throws an error or overwrites data if you do have it open so probably not best to have it open!

@burnz5553 10 ай бұрын

Can we work with password protected files?

@lemastertech 10 ай бұрын

Yes you can manually input the password but it’s honestly probably easier to save off a copy that is not protected, work with it that way, then copy it back into the master. Python also just added an actual excel plug in you can install via excel!

@AlessandroCacchione 6 ай бұрын

very good tutorial! could you please make a video about reading tables into .doc files?

@lemastertech 6 ай бұрын

Hi! Do you mean using python to read data from an excel file and then write it to a word file? I think word already handles excel data pretty well natively!?

@sandeepbharti5006 4 ай бұрын

Thanks I want to learn sumifs through phython please guide me

@lemastertech 4 ай бұрын

Hi I think the best thing to do if you want to use python to do the same style of calculation as the excel sum if would just be to use a for loop with an if statement in python that checks for your conditional and then add that number to your running total if it satisfies your condition and ignore it and move on otherwise!

@godisgood2482 2 жыл бұрын

Please upload more project on python I need it please. 😭😭😭

@lemastertech 2 жыл бұрын

I will my friend!! Good luck!

@godisgood2482 2 жыл бұрын

Can you do video on how to make bots on telegram using python please?

@lemastertech 2 жыл бұрын

Hi! I can look into this, I don’t make bots and always find them a little annoying so I’m not sure, but it might be a cool project! I’ll read about it