Automate Excel Pivot Table With Python

Подписаться 62 тыс.

Просмотров 25 тыс.

50% 1

Excel being the most popular spreadsheet application + Python being one of the most popular programming language, by integrating these two applications, we can build some really useful and sophisticate applications.
In this tutorial, we will learn how to automate Excel pivot table with Python using pywin32 (win32com) library.
📑 Source Code: learndataanalysis.org/automat...
▶ References
🔖 Dataset Used: data.sfgov.org/Economy-and-Co...
📑 Excel VBA Pivot Table Reference: docs.microsoft.com/en-us/offi...
📑 Excel VBA Pivot Field Reference: docs.microsoft.com/en-us/offi...
► Buy Me a Coffee? Your support is much appreciated!
-------------------------------------------------------------------------------------------
☕ Paypal: www.paypal.me/jiejenn/5
☕ Venmo: @Jie-Jenn
💸 Join Robinhood with my link and we'll both get a free stock: bit.ly/3iWr7LC
► Support my channel so I can continue making free contents
---------------------------------------------------------------------------------------------------------------
🌳 Becoming a Patreon supporter: / jiejenn
🛒 By shopping on Amazon → amzn.to/2JkGeMD
📘 Facebook Page → / madeinpython
📘 More tutorial videos on my website → LearnDataAnalysis.org
👩‍💻 Follow me on Linked: / jiejenn
✉️ Business Inquiring: RU-vid@LearnDataAnalysis.org
#excel #python #ExcelAutomation

Опубликовано:

20 мар 2022

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 54

@intoperspective2023 2 года назад

This video deserves a lot more views than this and I am sure it will. Very detailed, and simple to understand. Thank you for such a great video.

@intoperspective2023 2 года назад

+ 1 new subscriber

@amithdebnath9161 7 месяцев назад

Great video!! Thanks for the help!

@RicardoQuispeM Год назад

Wow, thank you!!

@gaben_aTan 10 месяцев назад

Спасибо за видео. Долго искал , как создать сводную. именно таким образом 😊👍👍👍👍👍👍👍👍👍

2 года назад

Hi Jie! Thanks for this video! It helped me a lot. Regards from Colombia.

@jiejenn 2 года назад

Glad the video helped.

2 года назад

@@jiejenn Hi. I'm making an automation for Excel which is generated from SAP. This will help me a lot. Thanks again.

@ABC9923XYZ Год назад

Simply Amazing

@jiejenn Год назад

👍

@manteshwarhajare868 Год назад

Really impressed, im looking how to create pivot table by using python, instead of pandas, Because when we use pandas we cant use pivot table functionality. By using win32 we can achieve this.

@hamletrex Год назад

great video, thanks for the effort! Can you tell, or show, how would you filter out some value in 'Agency name' filter? I've been trying with CurrentPage and Add2, but I get the error every time.

@ghadirsaadati9087 Год назад

Many thanks. First of all, I really appreciate your video. Secondly, may I ask how we can add all fields_rows in one column ? Because with Excel Pivot table, when you put all data in rows, it will automatically put them in one column, one under another.

@zaozaoliu8269 11 месяцев назад

That is really helpful! Thanks! May I also ask if it is possible to add an calculated field into the pivot table, supposedly "PivotTable.CalculatedFields().Add()"?

@projectworld8632 2 года назад

great sir thank you ❤❤❤❤❤❤❤💋💋💋💋💋💋

@jiejenn 2 года назад

Glad the video helped.

@coffeeman978 9 месяцев назад

Amazing tutorial, i used this on one of my project. @Jie Jenn is it possible to add a calculated field here? I find it hard to mimic the manual calculated field creation.

@shinywilliam2440 Месяц назад

Can we able to add the field settings to this table ? like layout & print options ?

@ylnswzxlzdxy 11 месяцев назад

Hi Jie, if i want to not show subtotals and repeat all labels, how shud i write? trying out these for repeating all labels: pt.RowAxisLayout(win32.constants.xlRepeatLabels) pt.ColumnAxisLayout(win32.constants.xlRepeatLabels), still getting error, do you have any ways to not show subtotals and repeating all labels ?

@tridibbiswas3361 2 года назад

Hi Thank you for your videos. Could you also share how is your vscode is configured for python dev.

@jiejenn 2 года назад

My Python setup is a little bit messy since there are many moving pieces, but I will probably find sometime one of these days share my Python setup + the add-ons I used.

@champak_chacha251 7 месяцев назад

How can I get first row number after applying filter in Excel sheet using xlwings?

@dicaseaulasdeingles3039 Год назад

Thank you sir

@bashashaik3642 Год назад

Thank you so much for publishing this video. Need your help to hide the subtotals, I used Worksheets("Sheet1").Activate.ActiveCell.PivotField.Subtotals(1) = False but its not working. Please provide the code if you have already.

@amiteshtripathi469 2 года назад

how can i find distinct count ?

@0909raj 6 месяцев назад

Hi, if i don't want subtotal then what to do?

@aksharanganeshan8862 Год назад

Hey hi , I cant see any pivot table generated . I tried the exact same code

@deyvisvalladares1339 2 года назад

Great!!! I have a question...How to add filter in pivote table with py?

@amiteshtripathi469 2 года назад

how can i find distinct count ?

@intoperspective2023 2 года назад

How did you get autocompletion for win32 attributes? For example, when you Type, xlApp.Visible, it should suggest .Visible but mine is not doing that. Please help..

@jiejenn 2 года назад

Hey Anh, in VS Code, I am using TabNine and IntelliCode extensions to auto populate members of the pywin32 package.

@victorbrax Год назад

How to use "Show values as (example: % of Row Total)", like Value Field Settings when right clicking on field?

@jiejenn Год назад

Will probably have to look into this.

@ABC9923XYZ Год назад

Can you tell how to add Filters just like columns and row and values in pt?

@laconispe06 Год назад

Filters must have their Orientation = 3. Then, if you want to filter based on the 'program_area': field_filters = {} field_filter[''program_area''] = pt.PivotFields("Program Area") field_filters['program_area'].Orientation = 3

@ABC9923XYZ Год назад

@@laconispe06 Thanks man

@ashleycarter9726 4 месяца назад

@jiejenn @laconispe06 thanks, this helped. How do you then add a specific filter? Say i wanted to filter on a specific word ?

@prashantdonga2575 11 месяцев назад

Can you please tell that how we can create pivot with win32 in python when data set is larger and not fit in excel (more then 1 million records) Bty this is the great video and very help full

@jiejenn 10 месяцев назад

Use pandas.

@prashantdonga2575 10 месяцев назад

So win32 is not going to work for this??

@EE_PallaviRai_ Год назад

While saving excel through win32, how to avoid getting the save as dialog box and directly save it

@jiejenn Год назад

There is a method called "application.DisplayAlerts", just set that to False will turn off notification. Makes sure you set that back on.

@intoperspective2023 2 года назад

Hi Jie, do you have any ideas on how I would be able to group Pivot Table Fields of Months and Years? I've been trying: Test = wsPivotTable.Range("B2") Test.Group(Start=True, End=True, Periods=list([False, False, False, False, True, False, True])) and it would group the Months only, but not the Years, even though I have its corresponding value to True....

@jiejenn 2 года назад

I am not in my office today, but I will take a look when I get a chance.

@jiejenn 2 года назад

Change your syntax to this Test.DataRange.Cells(1).Group((False, False, False, False, True, False, False))

@intoperspective2023 2 года назад

@@jiejenn Thanks for getting back to me. I tried that and I'm getting this: Traceback (most recent call last): File "c:\Python\PivotTable.py", line 64, in Test.DataRange.Cells(1).Group((False, False, False, False, True, False, False)) File "C:\Users\LaMisa\AppData\Local\Programs\Python\Python310\lib\site-packages\win32com\client\dynamic.py", line 639, in __getattr__ raise AttributeError("%s.%s" % (self._username_, attr)) AttributeError: Range.DataRange

@amiteshtripathi469 2 года назад

how can i find distinct count ?