How To Automate Excel Using Python | Combine Files & Create Charts 🤓 

Very happy to hear that you found it useful! Thanks for watching!

*I am glad you liked the video, thanks for watching and for the comment!*

Thank you for watching the video & taking the time to leave a comment. Perhaps the following video might also be interesting for you: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-ZI9T2O7XYxY.html In the video, I am going to solve a real-world data science/analysis project with Python. Happy Coding! 👍

Fantastic! Glad it helped! 🦾

Thank you! Glad you enjoyed the video. Welcome aboard! 😃

Thank you for watching the video. I am happy to hear that you like video :)

My pleasure! Thanks for watching the video and your comment!

Thank you very much! 🙏 I greatly appreciate your support! 👍

*Hello Bassel Khaled, thanks for watching the video and your comment.* Me too, I also love using Python ❤

@@CodingIsFun You are welcome my fried. I started learning Python for more than 1 year through different platforms. It is very fun and powerful language specially if you are using Python's libraries. I already subscribed to you channel and looking forward to more coding . Thank you again .

@@bak1970 I fully agree - the available libraries are making Python very powerful. Thanks for subscribing & Welcome aboard 😃

Thank you for your feedback. 🙏

*Thank you.* I am glad you enjoyed the video 😃

@@CodingIsFun Can you help with one question? How combine excel files which have more one sheet? Using first script in this tutorial combines only first sheet.

@@vitus2537 You could also iterate over each worksheet for the given Workbook. I have adjusted the Excel Files from the tutorial, split the data into different worksheets and adjusted my code accordingly. Please find the example I have created for you on my Google Drive: drive.google.com/file/d/1sBGjN_rg8ieg9Cfh1MQl1vZg5nAmhq-Q/view?usp=sharing ____________________________________________________ files = ["January.xlsx", "February.xlsx", "March.xlsx"] # Create Empty DataFrame combined = pd.DataFrame() for file in files: excel_file = pd.ExcelFile(file) # Get all sheet names for each Excel File sheet_names = excel_file.sheet_names # Iterate over each sheet name & create append DataFrame for sheet_name in sheet_names: df = pd.read_excel(file, sheet_name=sheet_name) df["Date"] = df["Date"].dt.date df["Day"] = pd.DatetimeIndex(df["Date"]).day df["Month"] = pd.DatetimeIndex(df["Date"]).month df["Year"] = pd.DatetimeIndex(df["Date"]).year combined = combined.append(df, ignore_index=True) # Clean Up NaN Values combined.dropna(inplace=True) ____________________________________________________ Hope it helps 🙏 Cheers, Sven ✌

@@CodingIsFun Thank you for help! it's nice code :) You combine all sheets in one but me need combine all sheets with save names of sheets. For example: we have two workbook ("first.xlsx" and "second.xlsx") and in every workbook we have two sheet ("Sheet1" and "Sheet2"). After combine i will have workbook "Combine.xlsx" with two sheet "sheet1" and "sheet2" and it is my big problem because i don't know pandas)) Sorry for my english because i'm Not a native language.

@@vitus2537 Ok understood. Please find your new example here 😃 👉 drive.google.com/file/d/1zeKW6pfd5KojB99XR-t8S1cZaSg-z7VT/view?usp=sharing Please install xlwings before running the code: pip install xlwings. Below is the code: import pandas as pd import xlwings as xw # Specify Files Names [ff the files are NOT in the same directory as the script, please use the absolute path to the files] files = ["first.xlsx", "second.xlsx"] wb = xw.Book("combined.xlsx") for file in files: # Create Empty DataFrame combined = pd.DataFrame() # Use Excel File to get access to Worksheets excel_file = pd.ExcelFile(file) # Get all sheet names for each Excel File sheet_names = excel_file.sheet_names # Iterate over each sheet name & create append DataFrame for sheet_name in sheet_names: df = pd.read_excel(file, sheet_name=sheet_name) df["Date"] = df["Date"].dt.date df["Day"] = pd.DatetimeIndex(df["Date"]).day df["Month"] = pd.DatetimeIndex(df["Date"]).month df["Year"] = pd.DatetimeIndex(df["Date"]).year combined = combined.append(df, ignore_index=True) # Take DataFrame (which includes data for all sheets) and append to new Workbook wb.sheets.add().range("A1").value = combined

*I am glad you liked the video, thanks for watching and for the comment!*

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

Thanks for watching and your suggestion. Yet, I have never used R 😬

Thank you for watching the video & welcome aboard :) Creating PowerPoint Presentation would be a video on its own. Nonetheless, I have created the following example for you: To export static images using plotly, make sure to install Kaleido (pip install -U kaleido) from pptx import Presentation # pip install python-pptx import pandas as pd import plotly.express as px df = pd.read_csv("raw.githubusercontent.com/mwaskom/seaborn-data/master/tips.csv") # CREATE BAR CHART fig = px.bar(df, x="day", y="tip") fig.write_image("bar_chart.png") # pip install -U kaleido # CREATE PPT prs = Presentation() slide = prs.slides.add_slide(prs.slide_layouts[8]) placeholder = slide.placeholders[1] picture = placeholder.insert_picture("bar_chart.png") prs.save("Example.pptx") Hope it helps 😃

Thank you!

Thanks for watching and your comment! :)

Thank you very much. 🙏 Glad the videos are helpful to you. In regards to your questions, it depends on some factors. For instance, if you know any other programming languages or your preferred media to learn a programming language. If you do not have any programming experience, I would start by learning the ‘basics’ such as variables, lists, functions, etc. However, I would not spend too much time learning the basics. I rather would suggest picking a small project. 📚 My book recommendation to learn Python: “Automate the boring stuff” from Al Sweigart Why do I think the book is a great resource for beginners in Python? 1. PRACTICAL examples to automate Excel, Word, PDF, E-Mail, ... 2. Concepts are clearly explained 3. Each section is broken down into consumable bits The content of the book can be found for FREE here: 👉 automatetheboringstuff.com/ 🎞 If you prefer a video-based course, I can recommend the one from Angela Yu on Udemy: 👉 www.udemy.com/course/100-days-of-code/ Usually, Udemy offers regularly promotions, where you can get each course for around 10$ USD. 📢Last but not least, please feel free to also reach out to me via the channel discussion board: 👉 ru-vid.com/show-UCZjRcM1ukeciMZ7_fvzsezQdiscussion I will be very happy to help you out in case of any questions😊 Happy Coding! Cheers Sven

Hi, To iterate over all folders, check out this video: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-w6-28jcr09Q.html Then store each excel file in a separate dataframe and use pd.append. The additional columns will be added to the dataframe (as mentioned in the docs: pandas.pydata.org/docs/reference/api/pandas.DataFrame.append.html After merging the dataframes, you can still drop the unwanted columns. I hope this helps! Happy Coding!

@@CodingIsFun gonna try it out. THANKS 😊

Thanks! For creating worksheets, I would recommend using the library 'openpyxl'. In fact, pandas uses 'openpyxl' under the hood when dealing with Excel files. That said, you could also use Pandas to create workbooks with 3 sheets; see the example below: import pandas as pd df = pd.DataFrame() with pd.ExcelWriter('output.xlsx') as writer: df.to_excel(writer, sheet_name='Sheet_name_1') df.to_excel(writer, sheet_name='Sheet_name_2') df.to_excel(writer, sheet_name='Sheet_name_3') I hope this helps!

*Thank you.* I am glad you enjoyed the video 😃

If you can post more videos in that direction. Using Python to automate Excel routines. Thank you very much!

@@user-qp8ei8lc3d Thank you for the video suggestion. In fact, I am planning to create more videos around Excel Automation 👍👩‍💻

I am using the free text editor 'Atom': atom.io/

I am using Atom (atom.io/), which is a lightweight text editor. My other recommendation would be VS Code: code.visualstudio.com/

Thank you, glad it is useful! ❤😃👩‍💻

It's a good question but difficult to answer because it depends on many factors. I am unsure what your input form looks like (GUI, Excel Sheet, Web application, ..) and how your data is structured. Sorry that I cannot help here!

@@CodingIsFun it's a quite basic question (shame on me!). I have an excel sheet with different client's names. I'd like to select one of them and retrieve the data from it. Can i use an input form (whichever?) that will then filter through that excel sheet? (perhaps you have a video about it ?)

@@anasofia4048 Ok got it! I do not have a full tutorial on this. Yet, I have once created a similar demo application. You can find the demo video and code here: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-RnTqlKzQhRY.html I hope it helps as a starting point. Happy Coding!

Thanks for watching. To change the file extension on Windows: - Open the folder containing your text file. - Click on the "View" tab at the top of the File Explorer window and make sure the "File name extensions" checkbox is checked. This will allow you to see and change the extensions of files in File Explorer. - Right-click on your text file and select "Rename". - Change the extension from .txt to .py. - A warning will pop up, explaining that changing a file extension might make the file unusable. Since you're intentionally changing the extension to .py so that it can be run as a Python script, you can safely click "Yes" to proceed. I hope it helps! Happy Coding!

Thanks for watching. Sorry, but I don't have time for custom solutions. I hope you can understand.

Under the hood, pandas is using openpyxl when dealing with Excel files. Pandas makes it easier to manipulate the data, e.g. combine rows or use a Data frame to plot the data

@@CodingIsFun you are kind, thank you very much

Inside the loop, you could adjust the date format to your liking, e.g. MM /DD/YYY would translate to "%m/%d/%Y" Please see below the adjusted code snippet. I also uploaded it on GistHub for you: gist.github.com/Sven-Bo/1388dda5edc4a16ee8b5f6f351a66e24 The output would look like this: prnt.sc/12f53n0 Hope you like this solution 🙏 _________________________________________ for file in files: df = pd.read_excel(file) df['Date'] = df['Date'].dt.strftime("%m/%d/%Y") # CHANGE HERE df['Day'] = pd.DatetimeIndex(df['Date']).day df['Month'] = pd.DatetimeIndex(df['Date']).month df['Year'] = pd.DatetimeIndex(df['Date']).year df.dropna(inplace=True) df['Month_Name'] = df['Month'].apply(lambda x: calendar.month_abbr[int(x)]) combined = combined.append(df,ignore_index = True)

Yes, check out "xlwings" as shwon in this video: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-JoonRjMsSdY.html

Thanks for watching. Please use the latest code from GitHub: github.com/Sven-Bo/combine-excel-files-with-python/blob/master/combine_files.py

@@CodingIsFun Thanks Sven, this is indeed very helpful.

Thanks for watching the video & your suggestions. I cannot make any promises, but l will see what I can do.

@@CodingIsFun it would be of great help. Take your time Master.

Thanks for watching the video & your video suggestion 👍😀

Hi Amit Singh. Thanks for watching the video and your question. You could execute a Python Script (by clicking a button) from Excel using VBA. Hopefully, the following video will help you: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-MgL9IOCFZ0U.html Thank you!

Thank you for your response. I will check.. keep showing python to automate ms office application. VBA is not in demand

Thank you for watching the video. I have created the following example for you: drive.google.com/file/d/1MdrTimYTbdf1rGgcV3vjk-sWy_RGSncd/view?usp=sharing Before running the script, please make sure that you have xlwings installed -> pip install xlwings. I have inserted also comments for each line in the code. Hope it is clear what each line of the does 🙏 _____________________________________________ import xlwings as xw # pip install xlwings # -- Connect to Workbook/Worksheet wb = xw.Book('Data_with_pivot.xlsx') sht = wb.sheets('PIVOT') # -- Store values from Pivot Table in variable data = sht.range('A3:B21').value # -- Create new Workbook, Insert Values & Save/Close Workbook wb_new = xw.Book() sht = wb_new.sheets[0] #First Worksheet sht.range('A1').value = data #Insert data from Pivot Table wb_new.save('My_New_Workbook.xlsx')

@@CodingIsFun Thanks you. I will test it. I have a excel report with 2 pivot table without source db. I have to explode (show details) some pivot table and get 2 dbs, then I have to rename the sheet of that 2 bds and save changes the report. Sorry for my english.

Thanks for watching. To avoid conflicts when multiple users try to access and edit the same Excel file, you can consider using a database, a cloud-based solution, or a shared drive like Dropbox, but you may still encounter conflicts if multiple users try to edit the same cell simultaneously.

What does the error message say?

@@CodingIsFun Thanks, It's as follows. pivot_4 = join.pivot_table(index=['M/C'], values=['Dip Qty / (Prs)'], columns=['Month'], margins=True, aggfunc='sum') pivot_4['%'] = pivot_4['Reject / (Pcs)'] / pivot_4['Dip Qty / (Prs)'] ------------------------------------------------------------------------------ KeyError Traceback (most recent call last) ~\Anaconda3\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance) 3079 try: -> 3080 return self._engine.get_loc(casted_key) 3081 except KeyError as err: pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc() pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc() pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item() pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item() KeyError: 'Reject / (Pcs)' The above exception was the direct cause of the following exception: KeyError Traceback (most recent call last) in ----> 1 pivot_4['%'] = pivot_4['Reject / (Pcs)'] / pivot_4['Dip Qty / (Prs)'] ~\Anaconda3\lib\site-packages\pandas\core\frame.py in __getitem__(self, key) 3021 if is_single_key: 3022 if self.columns.nlevels > 1: -> 3023 return self._getitem_multilevel(key) 3024 indexer = self.columns.get_loc(key) 3025 if is_integer(indexer): ~\Anaconda3\lib\site-packages\pandas\core\frame.py in _getitem_multilevel(self, key) 3072 def _getitem_multilevel(self, key): 3073 # self.columns is a MultiIndex -> 3074 loc = self.columns.get_loc(key) 3075 if isinstance(loc, (slice, np.ndarray)): 3076 new_columns = self.columns[loc] ~\Anaconda3\lib\site-packages\pandas\core\indexes\multi.py in get_loc(self, key, method) 2874 2875 if not isinstance(key, tuple): -> 2876 loc = self._get_level_indexer(key, level=0) 2877 return _maybe_to_slice(loc) 2878 ~\Anaconda3\lib\site-packages\pandas\core\indexes\multi.py in _get_level_indexer(self, key, level, indexer) 3156 else: 3157 -> 3158 idx = self._get_loc_single_level_index(level_index, key) 3159 3160 if level > 0 or self.lexsort_depth == 0: ~\Anaconda3\lib\site-packages\pandas\core\indexes\multi.py in _get_loc_single_level_index(self, level_index, key) 2807 return -1 2808 else: -> 2809 return level_index.get_loc(key) 2810 2811 def get_loc(self, key, method=None): ~\Anaconda3\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance) 3080 return self._engine.get_loc(casted_key) 3081 except KeyError as err: -> 3082 raise KeyError(key) from err 3083 3084 if tolerance is not None: KeyError: 'Reject / (Pcs)'

@@asankacool1 According to the error traceback, it seems that column 'Reject / (Pcs)' in your 'pivot4' does not exist. You might wanna check/print out your column names: print(pivot4.columns)

@@CodingIsFun Thanks bro, corrected it & now rejects exists. and I now get this. I cannot figure out what I have done wrong here fundamentally. ------------------------------------- KeyError Traceback (most recent call last) ~\Anaconda3\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance) 3079 try: -> 3080 return self._engine.get_loc(casted_key) 3081 except KeyError as err: pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc() pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc() pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item() pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item() KeyError: '%' The above exception was the direct cause of the following exception: KeyError Traceback (most recent call last) ~\Anaconda3\lib\site-packages\pandas\core\generic.py in _set_item(self, key, value) 3825 try: -> 3826 loc = self._info_axis.get_loc(key) 3827 except KeyError: ~\Anaconda3\lib\site-packages\pandas\core\indexes\multi.py in get_loc(self, key, method) 2875 if not isinstance(key, tuple): -> 2876 loc = self._get_level_indexer(key, level=0) 2877 return _maybe_to_slice(loc) ~\Anaconda3\lib\site-packages\pandas\core\indexes\multi.py in _get_level_indexer(self, key, level, indexer) 3157 -> 3158 idx = self._get_loc_single_level_index(level_index, key) 3159 ~\Anaconda3\lib\site-packages\pandas\core\indexes\multi.py in _get_loc_single_level_index(self, level_index, key) 2808 else: -> 2809 return level_index.get_loc(key) 2810 ~\Anaconda3\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance) 3081 except KeyError as err: -> 3082 raise KeyError(key) from err 3083 KeyError: '%' During handling of the above exception, another exception occurred: ValueError Traceback (most recent call last) in ----> 1 pivot_4['%'] = pivot_4['Reject / (Pcs)'] / pivot_4['Dip Qty / (Prs)'] ~\Anaconda3\lib\site-packages\pandas\core\frame.py in __setitem__(self, key, value) 3161 else: 3162 # set column -> 3163 self._set_item(key, value) 3164 3165 def _setitem_slice(self, key: slice, value): ~\Anaconda3\lib\site-packages\pandas\core\frame.py in _set_item(self, key, value) 3241 self._ensure_valid_index(value) 3242 value = self._sanitize_column(key, value) -> 3243 NDFrame._set_item(self, key, value) 3244 3245 # check if we are modifying a copy ~\Anaconda3\lib\site-packages\pandas\core\generic.py in _set_item(self, key, value) 3827 except KeyError: 3828 # This item wasn't present, just insert at end -> 3829 self._mgr.insert(len(self._info_axis), key, value) 3830 return 3831 ~\Anaconda3\lib\site-packages\pandas\core\internals\managers.py in insert(self, loc, item, value, allow_duplicates) 1201 value = safe_reshape(value, (1,) + value.shape) 1202 -> 1203 block = make_block(values=value, ndim=self.ndim, placement=slice(loc, loc + 1)) 1204 1205 for blkno, count in _fast_count_smallints(self.blknos[loc:]): ~\Anaconda3\lib\site-packages\pandas\core\internals\blocks.py in make_block(values, placement, klass, ndim, dtype) 2740 values = DatetimeArray._simple_new(values, dtype=dtype) 2741 -> 2742 return klass(values, ndim=ndim, placement=placement) 2743 2744 ~\Anaconda3\lib\site-packages\pandas\core\internals\blocks.py in __init__(self, values, placement, ndim) 140 141 if self._validate_ndim and self.ndim and len(self.mgr_locs) != len(self.values): --> 142 raise ValueError( 143 f"Wrong number of items passed {len(self.values)}, " 144 f"placement implies {len(self.mgr_locs)}" ValueError: Wrong number of items passed 14, placement implies 1

@@asankacool1 It is a little hard for me to debug your code via the RU-vid comments. Regarding your error message, check out the following Stackoverflow post: stackoverflow.com/questions/43196907/valueerror-wrong-number-of-items-passed-meaning-and-suggestions

Thank you for watching the video and trying out the solution. It is hard to tell what the issue could be without seeing the full error trace log. I assume that you have stored the Excel files in the same directory as the Python script?! If so, you could try to change the current working directory by including the following lines on top of the script: import os, sys os.chdir(sys.path[0]) Hope this helps!

@@CodingIsFun Thanks! I have tried it and it worked. I also have sent you a mail regarding this error message in detail. This video helped me a lot!!

@@jefrin Thank you, I have just seen your email and replied :)

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! Cheers, Sven ✌️

Thanks for the hint. I have updated the Github repo. *OLD:* combined = combined.append(df, ignore_index=True) *NEW:* combined = pd.concat([combined, df], ignore_index=True)

@@Joe-ps7em Thanks for your feedback. Due to my current workload, I do not offer 1:1 lessons. That said, you might want to check out my favourite resources to learn Python: www.pythonandvba.com/resources/#Python Happy Learning & Coding!

Pandas can read data from Excel files, including those created using LibreOffice Calc, using the pd.read_excel() function. The function can read both .xls and .xlsx file formats.

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!

Thanks for watching the video. Did you try to use the code from my example (👉 bit.ly/2JnRhZ7), or have you modified the code base? If so, code you share the full code here? Thank you 😃

@@CodingIsFun Hi I have used the same base code as yours..., I have not modified anything..

@@cool89ful Sorry for my late reply. I am very sorry, but I am not sure why this error occurs on your side 😕

Thanks for watching and for your comment! Power Query is a great tool. If Power Query meets your needs, there's no need to switch. :)

I am not really familiar with Mac, but I found the following video: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-yBdZZGPpYxg.html&ab_channel=AutomatingOSINT Hope it helps you 🙏

Hey there, thanks for watching the video! I'm sorry I can't help you with your problem based on the information you provided. To give me a better idea of what's going on, it would be super helpful if you could write down which line of code is causing the error, let me know if you modified the code from the tutorial, and explain in more detail what you did to troubleshoot the problem. Don't forget to also give me some context about your setup and environment. If you're having trouble figuring things out, another option is to join our Discord server at pyhtonandvba.com/discord. You can ask your question there and maybe someone in the community can help out. Thanks for understanding.

# imports import pandas as pd import datetime as dt files = ['January','February','March'] combined = pd.DataFrame() for file in files: df=pd.read_excel(file) combined = combined.append(df,ignore_index=True) combined.to_excel('Admits_Q1_2022',index=False,sheet_name='Q1 2022')

i only modified the name of the file with its sheet name, the rest remain same. thank you anw for your reply

@@kristinapaseru2518 Use the latest code from GitHub: github.com/Sven-Bo/combine-excel-files-with-python/blob/master/combine_files.py

@@CodingIsFun File "", line 7 BASE_DIR = Path(C:\Users\KRISTINA\Desktop\Combine_Excel_Files).parent ^ SyntaxError: invalid syntax

Glad you like it!

Thanks for watching the tutorial. Currently, I do not have a tutorial on how to send files via WhatsApp. Unfortunately, WhatsApp does not offer a free API. To send files via WhatsApp without using any paid service, you might want to look at browser automation tools/libraries like 'Selenium' to automate the WebWhatsApp interface. Cheers, Sven ✌

Thanks a lot Sven, really appreciate it. Does telegram have any API that allows similar automation?

@@rprasathr I went ahead and checked it - Yes, Telegram offers a free API: core.telegram.org/api/terms However, I have never used it before. But I found the following video, which shows how to send a file using Telegram using Python (via post request): ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-NYT1KFE1X2o.html Hope it helps :)

@@CodingIsFun thanks a lot