Automate Excel using Python | Excel Hacks with Pandas 

You can do it!

Thank you 🙏

@@SATSifaction ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Jg6g6foHzCs.html #TenaliRK #FreeHinduTemples

Thank you

You’re welcome

JL you’re welcome

@@SATSifaction Complete newb question here but I cant get past the part where I enter the file path. The error text below keeps popping up: IndexError Traceback (most recent call last) in 4 pth=os.path.dirname(file) 5 newfile=os.path.join(pth,filename+'_2'+extension) ----> 6 df=pd.read_excel(file) 7 colpick=input('Select Column: ') 8 cols=list(set(df[colpick].values)) C:\Anaconda3\lib\site-packages\pandas\util\_decorators.py in wrapper(*args, **kwargs) 206 else: 207 kwargs[new_arg_name] = new_arg_value --> 208 return func(*args, **kwargs) 209 210 return wrapper C:\Anaconda3\lib\site-packages\pandas\io\excel\_base.py in read_excel(io, sheet_name, header, names, index_col, usecols, squeeze, dtype, engine, converters, true_values, false_values, skiprows, nrows, na_values, keep_default_na, verbose, parse_dates, date_parser, thousands, comment, skip_footer, skipfooter, convert_float, mangle_dupe_cols, **kwds) 338 convert_float=convert_float, 339 mangle_dupe_cols=mangle_dupe_cols, --> 340 **kwds 341 ) 342 C:\Anaconda3\lib\site-packages\pandas\io\excel\_base.py in parse(self, sheet_name, header, names, index_col, usecols, squeeze, converters, true_values, false_values, skiprows, nrows, na_values, parse_dates, date_parser, thousands, comment, skipfooter, convert_float, mangle_dupe_cols, **kwds) 881 convert_float=convert_float, 882 mangle_dupe_cols=mangle_dupe_cols, --> 883 **kwds 884 ) 885 C:\Anaconda3\lib\site-packages\pandas\io\excel\_base.py in parse(self, sheet_name, header, names, index_col, usecols, squeeze, dtype, true_values, false_values, skiprows, nrows, na_values, verbose, parse_dates, date_parser, thousands, comment, skipfooter, convert_float, mangle_dupe_cols, **kwds) 440 sheet = self.get_sheet_by_name(asheetname) 441 else: # assume an integer if not a string --> 442 sheet = self.get_sheet_by_index(asheetname) 443 444 data = self.get_sheet_data(sheet, convert_float) C:\Anaconda3\lib\site-packages\pandas\io\excel\_xlrd.py in get_sheet_by_index(self, index) 44 45 def get_sheet_by_index(self, index): ---> 46 return self.book.sheet_by_index(index) 47 48 def get_sheet_data(self, sheet, convert_float): C:\Anaconda3\lib\site-packages\xlrd\book.py in sheet_by_index(self, sheetx) 464 :returns: A :class:`~xlrd.sheet.Sheet`. 465 """ --> 466 return self._sheet_list[sheetx] or self.get_sheet(sheetx) 467 468 def sheet_by_name(self, sheet_name): IndexError: list index out of range

awesome

what product did u do for dropshipping?

Thank you

How would you modify it to add for filter and split instead? Curious, because I work with 300-660k row files and that could take a while.

how so? wouldnt mind seeing it in vba too! thanks!

@@sniper6219 You can refer to my comment. Cheers!

Glad you liked it

@@SATSifaction sorry, I didn't type all info. So just updated the comment with a question. Appreciate the link on basic packages I need to run the script described in this video. Sorry for such noob request, I would say I almost zero in coding, but advanced in excel.

No problem. That generally means pandas is not installed properly. You can try to uninstall it and reinstall it. Alternately you can set up a virtual environment and run it all in there. It will work without issue if you run it in a virtual environment. Try this video 👉🏻 How to use a Python Virtual Environment the RIGHT way with Jupyter Notebook

@@SATSifaction thanks! Will dig into that!

Good question. When you think of python think about scaling. VBA in this context is very limited to the workbook you are in. Python enables you to scale by integrating into other platforms with your excel notebook like pure machine learning frameworks, web dev etc. VBA is not scalable like python is.

Excel has a limit in number of rows, it cannot be more than 1 million rows, If u are handling less data, u can stick back to Excel If u are handling data like more than 1 million of rows, let's consider using Python

I have used VBA for long time. Now I'm using Python and I love it. However, for this kind of "Office Task" it is less time consuming and more convenient using VBA. This is not about ML or AI at all, it's Application oriented script and VBA's been done for it. Reinventing the wheel, making up the process longer or just making it as a "nice-to-have" tool is not ideal

After a little playing around I just realized that I cannot use \ / * [ ] : ? in sheet names or file names... ...which does make sense... out of our database we have : identifying parts... so i need a way to change this in the sheet before splitting....

okay so been fighting with this for a little while... ...does anyone know how to remove the special characters before the split?

You can always use a replace command to remove special characters. You can either replace special characters with an underscore which I believe is acceptable in excel or just remove it all together. There are ways to do this is code with python by using a str.replace (‘old’, ‘new’) command or you can do it a replace within excel.....so you can easily pull the data from your DB into python and use the replace method. There are more sophisticated ways of doing this with NLP as well....a quick google search on the above should set you in the right direction.

this one i believe was pycharm tho I've since moved to sublime

Means that is found a number (int) and was expecting a string (text)

You can potentially solve this by finding the "sequence item 2" the error references and try to cast the float as a string using "(str)sequence_item_2" for example. This will force the float value to become a string in the scope of wherever you cast it. Hope this helps!

@@phantasm3207 Be aware that float and int are two very different data types. This comment is still valid in explanation albeit slightly flawed in accuracy. Great tips tho!

Hi Darwin, if you watch towards to the end of the video I explain the .py file

You should probably understand the basics before jumping in. Check out my beginner python tutorial

Yes of course check it out 👉 Master Python: Complete Beginners Course - ru-vid.com/group/PLM30lSIwxWOijsnA2Fr1PPGkOiCaDaRod

Thanks and appreciate the sub. As for the file if I’m quite honest that was posted a while back and I dont have it on my computer anymore. I thought i had uploaded it with the other files but i didnt unfortunately. My tutorials are now triple checked for the original files :) sorry about that

Yes it should be. Python is agnostic of OS

You would need to use something like df.loc[-1] to find the last row in your excel file and start the writing process from there. My advise is to import the data as a df and append it within python and write back to excel as a new tab. If you have too many rows then i'd consider a database like sqlite3 where appending the data is easier

Yes, you can do this with VBA

Which is the easiest

@@Vision267 The same really. But Python gives you more options

@@infared14 what kind of additional options

You might want to check the value of cols and len(cols), confirm the .format syntax. Haven't run the code yet.

@@markanderson8066 I honestly can't find the problem. I am very new to programing.

can you help me? I get the error: Traceback (most recent call last): File "split.py", line 42, in print("Your data will split based on these values {} and create {} files or sheets based on next selection. If you are ready to proceed please type 'Y' and hit enter. Hit 'N' to exit".format(', '.join(cols), len(cols))) TypeError: sequence item 0: expected str instance, float found

@@joey5069 Hi, please change join(cols) into join(str(cols)) in your line 42. Thanks for letting me know. I already changed that in my github as well.

@@learnmandarinwithkaili1102 thank you! I still learning python and I could not figure out why it was not workin. How did you find the problem?

@@learnmandarinwithkaili1102 I get another error in the "split into files or sheets: Traceback (most recent call last): File "split.py", line 54, in sendTofile(cols) File "split.py", line 26, in sendTofile df[df[colpick]==i].to_excel("{}/{}.xlsx".format(pth, i), sheet_name=i, index=False) File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/pandas/core/generic.py", line 2175, in to_excel formatter.write( File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/pandas/io/formats/excel.py", line 730, in write writer.write_cells( File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/pandas/io/excel/_xlsxwriter.py", line 206, in write_cells wks = self.book.add_worksheet(sheet_name) File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/xlsxwriter/workbook.py", line 180, in add_worksheet return self._add_sheet(name, worksheet_class=worksheet_class) File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/xlsxwriter/workbook.py", line 729, in _add_sheet name = self._check_sheetname(name, isinstance(worksheet, Chartsheet)) File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/xlsxwriter/workbook.py", line 777, in _check_sheetname if len(sheetname) > 31: TypeError: object of type 'float' has no len()

@@joey5069 would you mind to leave your questions at my repository? Would help others who run into the same bug.

You surely will dude! Be strong!

Speaking from someone who has used both quite extensively in my career what I want to emphasize isnt just for this specific task but the automation integrations and trigger points that python offers with something like this...., its unmatched and much more superior than VBA.

@@SATSifaction Well, if someone already knows Python and don't want to learn VBA, and I totally get that, they should use Python. I'm just saying we should use the right tool for a given task. VBA is much faster if used right (being the native language), more accessible (built in IDE), bit more friendly for absolute beginners. Someone who develops Excel automations, it's worth learning both languages. If speed is important, and many times it is, VBA is mostly unbeatable.

@@danielszalok8540 There are several additional functionalities you can add to this code such as job scheduling, sending emails, etc. Although this too can be done via VBA, the coding would be much difficult compared to python. I would say python is pretty easy to learn for anyone with no prior programming and IT experience. Utilizing the features of python and excel power query, a lot of tasks can be automated.

@@danielszalok8540 VBA would soon be outdated the way things are going ahead..

@@twasif outdated doesn't mean inefficient, or difficult to use, or slow