How to Split Each Excel Sheet Into a Separate File Using Python

𝗗𝗘𝗦𝗖𝗥𝗜𝗣𝗧𝗜𝗢𝗡
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
In this tutorial, we will learn how to split each Excel sheet into a separate file and put them on a new and separate directory.

🌍 𝗟𝗜𝗡𝗞𝗦:

𝗧𝗢𝗢𝗟𝗦 𝗔𝗡𝗗 𝗥𝗘𝗦𝗢𝗨𝗥𝗖𝗘𝗦
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀

𝗖𝗢𝗡𝗡𝗘𝗖𝗧 𝗪𝗜𝗧𝗛 𝗠𝗘
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀

☕ 𝗕𝘂𝘆 𝗺𝗲 𝗮 𝗰𝗼𝗳𝗳𝗲𝗲?

[CodingIsFun]

*I will be here in the comments section. Please provide your exact error message for any issues, and I will try to help.*

[TheChristianPlatt]

I cannot thank you enough for this. I place orders for my work and track everything on templates beforehand via Excel. Each vendor must be submitted individually to my purchasing department, however, and this makes it so much quicker. I made a small edit and was able to simply change the PO # (a new variable I added) and everything work from there, including naming the target directory after the PO itself. Very informative and well constructed lesson!

[tarekelias]

Really good and simple explanation, thanks a lot! Just used it in my work to save a lot of time

[Jriad]

and to be honest your videos are great and to the point

[ianpropst-campbell]

Thank you for this video. The code worked perfectly.

[frankmartinez]

Does this work for Google sheets to save them as CSV files as well?

[colinhuang]

Hi Sven, is that possible that we can write multiple DataFrames to the same sheet which already exists?

[FredyArg]

Thanks for this, do you have a sample where we have a user
1st be prompted for the directory,
2nd select a file.
3rd, split the excel file into separate excel files based on the name of a column within the excel file?

[EmekKov]

Hi, this is amazing. Would it be possible to use this method (of course after some modifications) to split one sheet into workbooks based on column content?

[rajwolbajracharya]

How can we convert this into an executable file asking option for input folder and output folder?

[chyang]

Appreciate your work! Your insights would be appreciated if to share how to split Excel sheets into Excel files using Python. For instance, my master Excel file has 5 sheets (Hank-1, Hank-2, Hank3, Mary -1, and Mary-2). After executing the ''work-saving" script, I will have two resulting Excel files such as Hank.xlsx (Hank-1, Hank-2, Hank3) and Mary.xlsx (Mary-1 and Mary-2). Many thanks!

[jedenzemenemsow]

great tutorial! any chance you could show us how to split similar file by rows, let's say we have 1000 rows in a file, and we would like to split it into 10 files each one with the header and 100 rows of data?

[charlesthamba]

Just AWESOME Sir.
Plz I want to know, in Vba how can I open a new excel workbook and work on it when a userform is already is display? Plz help. Thank you for all

[benjaminjohnson]

This does exactly what I want it to (in theory)! but I can't get it to work. On a fresh Python install (3.11) on Windows, with xlwings installed via pip, and only changing the INPUT_DIR, I get the following error:
Traceback (most recent call last):
File "[PATH]/Documents/Python Scripts/split_wb.py", line 13, in <module>
with xw.App(visible=False) as app:
File "C:\Users\[USER]\AppData\Local\Programs\Python\Python311\Lib\site-packages\xlwings\main.py", line 294, in __init__
self.impl = engines.active.apps.add(
AttributeError: 'NoneType' object has no attribute 'apps'

[ranyfahed]

greate video but when i try to run the script it crash and display this error : with xw.App(visible = False) as app

[Jriad]

Great work!

but i think it'd have been more practical to output to folders same as the folder name it was in

[riazahmedshaik]

Hi, This is fantastic. I have tabs like France_1, France_2, France_3, India_1, India-2, _indai-3 in a excel work book. I need to get generate a excel book for each country with tabs. Eg: France_1, France-2, France-3 one excel book and for India one excel book with tabs. How to do it? Please help

[quality]

hi...Your videos is best but its not my my scenario
solution..How i split the one excel file into multiple excel files by particular row name...