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

[@mohammadjavadderakhshan6655]

Thanks to your tutorial and codes 15 hour work done Automatically without human interference.

[@CodingIsFun]

Fantastic! Glad it helped! 🦾

[@niuean22kid]

Thanks quite straightforward . I am a business analyst looking to expand on my excel knowledge

[@CodingIsFun]

Thank you for watching the video & taking the time to leave a comment. Perhaps the following video might also be interesting for you: kzbin.info/www/bejne/kHqchWWFbL2Mrrs In the video, I am going to solve a real-world data science/analysis project with Python. Happy Coding! 👍

[@calmlife5471]

I was using string function to seperate the month,year data into a column . This is very useful sir !! Looking forward more informative videos🔥

[@CodingIsFun]

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

[@zinebalami-s5r]

Excellent video ,thank you for sharing

[@CodingIsFun]

Glad you enjoyed it! Cheers, Sven ✌️

[@anasalhariri5474]

Awesome, thank you, I subscribed. this is my first time visiting your page. Please, keep it up!

[@CodingIsFun]

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

[@SaulEduardoo2]

Great example!, this is exactly the kind of content I was looking for 😊☺

[@CodingIsFun]

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

[@dominospizzach]

This is amazing! I love your channel. You're super helpful!!

[@CodingIsFun]

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

[@corentindurand6283]

Great ! Keep up the good work !

[@CodingIsFun]

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

[@usamajunaid2989]

Great! If you are also the user of R Language then please make videos like this in RStudio to automate excel like data manipulation and conditional formulation for excel in RStudio

[@CodingIsFun]

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

[@user-ig4mg7go1s]

Great job! Thank you!

[@CodingIsFun]

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

[@AjinkyaBayani]

this was most unique video and helpfull plz guide us more

[@CodingIsFun]

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

[@bak1970]

Beautiful coding. I love Python.

[@CodingIsFun]

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

[@bak1970]

@@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 .

[@CodingIsFun]

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

[@lorenzoladejobi8701]

Very informative !!. Thank you for this.

[@CodingIsFun]

Thank you for your feedback. 🙏

[@RajneeshKumar-vr1qj]

Nice tutorial btw same work could be done by power query or vba much faster

[@CodingIsFun]

Thanks for watching and your comment! :)

[@user-do3nl9fq6n]

thank you sir, Question please: your method will take too long executing because the code will go to each cell in the rows? why you didn't use "OpenPyXL" which one is better?

[@CodingIsFun]

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

[@user-do3nl9fq6n]

@@CodingIsFun you are kind, thank you very much

[@joiepatsy4991]

Thankyou sir, very good explanation. Sir you told that we can import to power point? how can we make it? can we import the table as well to ppt? thankyou so much, i subscribed

[@CodingIsFun]

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 😃

[@sachin.tandon]

Do you know Django? With your skills, you could definitely create some amazing softwares!

[@sagarmhta]

Your videos are really helpful; How we can learn python from scratch?

[@CodingIsFun]

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: 👉 kzbin.info/door/ZjRcM1ukeciMZ7_fvzsezQdiscussion I will be very happy to help you out in case of any questions😊 Happy Coding! Cheers Sven

[@asankacool1]

The date,month segregation shown in the video is very useful for analysis. 💥💥💥

[@CodingIsFun]

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

[@maramsrinureddy]

Great content in the video, how to create multiple sheets in the excel doc using pandas, is there a way ?(example: - create 3 sheets for 3 months data), is it possible ?

[@CodingIsFun]

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!

[@anasofia4048]

Hello there, I have a problem ! I want to get information from excel per client. Meaning, I'd like to input the client's name and then extract some of the data from THAT client (I have an excel report with different data from different clients) - not sure what library / module / functions to use. Any ideas? Many thanks. Excellent videos !

[@CodingIsFun]

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!

[@anasofia4048]

@@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 ?)

[@CodingIsFun]

@@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: kzbin.info/www/bejne/iJ-3op-Br7abiLs I hope it helps as a starting point. Happy Coding!

[@vitus2537]

Good tutorial. Thank you for this, just awesome

[@CodingIsFun]

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

[@vitus2537]

@@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.

[@CodingIsFun]

@@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 ✌

[@vitus2537]

@@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.

[@CodingIsFun]

@@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

[@sofuhuseyin1952]

I have a specific question: I want to automate our material inquiries. We always send an Excel list to, for example, building material suppliers or provide a link from which they can download the list. Here's how I'd like it solved: The supplier offers me materials. For instance, they might offer some materials but not all. For each material, a cell should be assigned with the price (B10….Bx), and in column A, there's the description of the material, etc. The supplier sends or uploads the file. I have X suppliers who provide me with offers. How can I automate this? I'd like the offers from different providers to be displayed in separate columns in an Excel sheet and evaluated based on criteria such as the cheapest price (ideally marked in green) and the most expensive price (marked in red). At the end, I'd like to have an Excel file where all suppliers are listed in one column, with the evaluation of the cheapest and most expensive offers in the last columns, and the ideal prices (cheapest) in another column. The challenge lies in automatically entering the offers into a new column, in the order they're provided by the suppliers. I look forward to your support and feedback. Regards from Augsburg

[@CodingIsFun]

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 ✌️

[@rohithd]

Which is the editor tool that you have used to write python code?

[@CodingIsFun]

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

[@user-rr7yi3ru2p]

Hello! Tell me how to automatically select a function for data in Excel, for example, for the R^2 criterion: polynomial, linear or cubic? Or automatically compose a step function. Are there any built-in functions for this in Excel? Or maybe in Python? Sincerely!

[@CodingIsFun]

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

[@ahmadmojeed5507]

Thanks for the amazing work. Haven't gotten a reply yet though, what if there's a different folder directory, the both have same column name but different content, how do we merge? For example, there's folder A that contains January to March sales and folder B has January to March sales also but different sales input. How do we merge per column, merge folder then have the January in one xlsx file, February in one and the whole of march in one xlsx also

[@CodingIsFun]

Hi, To iterate over all folders, check out this video: kzbin.info/www/bejne/rWeQY2ugmNdjb7M 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!

[@ahmadmojeed5507]

@@CodingIsFun gonna try it out. THANKS 😊

[@akshathbharathi7376]

Can you make video explaining all three activities like sending excel through whatsapp, saving it on cloud and sending through web app?

[@CodingIsFun]

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

[@akshathbharathi7376]

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

[@kariza87]

Nicely elaborate

[@CodingIsFun]

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

[@asensiodias1593]

Which editor you use for Python or what ide is good for it

[@CodingIsFun]

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

[@ciccychen5911]

thanks for the video, how does py change a text file to py file? mine is not changing

[@CodingIsFun]

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!

[@thewheeldeal8439]

is there a way to make excel charts in python? not plotly in another file, but directly in excel

[@CodingIsFun]

Yes, you can also create native Excel charts with Python. Check out openpyxl and XlsxWriter. Alternatively, you can insert Python charts as images into Excel using xlwings: kzbin.info/www/bejne/anTVi4iAqc-NocUsi=rbOJBZVeqN9fjQlF I hope it helps! Happy charting! Cheers, Sven ✌️

[@abhisheksonawane2997]

Hello Sven, I had a small problem in the code mentioned in the video, After writing the line of code (combined=combined.append(df), I am getting error code saying - 'AttributeError: 'DataFrame' object has no attribute 'append'' Can you please tell me what I can do for this?

[@CodingIsFun]

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

[@abhisheksonawane2997]

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

[@asankacool1]

Hi, i get an error when tried to get aggregate % of 2 different variable-equation for Reject %. # df['%'] = df['Reject / (Pcs)'].sum()/df['Dip Qty / (Prs)'].sum() ; this gives error. Do you have any clue for this? Thanks :)

[@CodingIsFun]

What does the error message say?

[@asankacool1]

@@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)'

[@CodingIsFun]

@@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)

[@asankacool1]

@@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

[@CodingIsFun]

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

[@onurelmas8427]

Hi ! Can anyone help me ? I want to automate excel with python. But Excel is common used by my collugues. If we want to do it same time. I get a error message like this-> discard the changes or save as. How can I automate common used excel ?

[@CodingIsFun]

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.

[@sudharshan574]

Please make vdeo on progress bar, to show much file is uploaded after pd_read.excel

[@CodingIsFun]

Thanks for watching the video & your video suggestion 👍😀

[@Joe-ps7em]

Excellent

[@CodingIsFun]

Thank you!

[@anonymousm4328]

is there anyway to preserve formatting after splitting excel files ? VBA can but i cant find a way with python.

[@CodingIsFun]

Yes, check out "xlwings" as shwon in this video: kzbin.info/www/bejne/gKDSn4WggtiGmrs

[@AmitSingh-pe9ss]

I want to know how we will run the automation task? Like we go to excel,, click on macro and run macro build by VBA but for python how and from where to run the automation.

[@CodingIsFun]

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: kzbin.info/www/bejne/g5ivanyFeKuNZrc Thank you!

[@AmitSingh-pe9ss]

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

[@MacGyver90]

Pls need help. How can I explote data detail from a excel pivot table then save in a new file with Python? I need automate a excel report.

[@CodingIsFun]

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')

[@MacGyver90]

@@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.

[@anasalhariri5474]

I have a question if you don't mind. I would like to reformat the date to MM /DD/YYY how can I do that, please?

[@CodingIsFun]

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)

[@jefrin]

Hey, that is a superb video. I am a newbie to python. When I try to execute, it is showing this error. ValueError: Invalid file path or buffer object type:

[@CodingIsFun]

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!

[@jefrin]

@@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!!

[@CodingIsFun]

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

[@Joe-ps7em]

Hi. The .append is deprecated, how would we use .concat in this example?

[@CodingIsFun]

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)

[@CodingIsFun]

@@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!

[@DarkoVlajkovic]

You could do it with Python, but i feel its so much easier to do it in Power Query, you dont even need programming skills to do it...Maybe im missing something

[@CodingIsFun]

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. :)

[@user-qp8ei8lc3d]

it was amazing

[@CodingIsFun]

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

[@user-qp8ei8lc3d]

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

[@CodingIsFun]

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

[@Jurginho10]

How can I do for sending the file via mail or whatsapp?

[@CodingIsFun]

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!

[@cool89ful]

combined = combined.append(df, ignore_index = True) this line giving me error "append() takes at least 2 arguments (2 given)". Can anybody help me solving this.

[@CodingIsFun]

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 😃

[@cool89ful]

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

[@CodingIsFun]

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

[@sulalithasannasgala]

Using libre calc same as Excel

[@CodingIsFun]

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.

[@kristinapaseru2518]

i tried your code but didnt succeed. it said no such file or directory. any idea?

[@CodingIsFun]

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.

[@kristinapaseru2518]

# 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')

[@kristinapaseru2518]

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

[@CodingIsFun]

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

[@kristinapaseru2518]

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

[@jaaviis46]

How do you install libraries in Mac computers?

[@CodingIsFun]

I am not really familiar with Mac, but I found the following video: kzbin.info/www/bejne/r3PHi419hdWMrsk&ab_channel=AutomatingOSINT Hope it helps you 🙏

[@igormaricic7093]

noice

[@CodingIsFun]

Glad you like it!

[@rprasathr]

Any tutorial sending the file via WhatsApp?