Python: Split Each Excel Sheet Into Separate Files (fast & easy)

𝗗𝗘𝗦𝗖𝗥𝗜𝗣𝗧𝗜𝗢𝗡
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
This video will show you how to split each worksheet in Excel into a separate Excel file/workbook using Python instead of VBA. In particular, we will be using the xlwings library.

Remark: In the video, I have used the os dependent '.api' method to copy sheets.
Since xlwings version 0.22 the '.copy()' has been implemented.
Additionally, xlwings.App() can now be used as context manager in xlwings v0.24.3:
This will make sure that there are no zombie processes left over on Windows, even if you use a hidden instance and your code fails.
It is therefore recommended to use it whenever you can. Therefore I have adjusted the code as follows:

👩‍💻 𝗣𝘆𝘁𝗵𝗼𝗻 𝗖𝗼𝗱𝗲:
from pathlib import Path
import xlwings as xw # pip install xlwings

EXCEL_FILE = Path(__file__).parent / 'YOUR_EXCEL_FILE.xlsx'
OUTPUT_DIR = Path(__file__).parent / 'Output'

# Create Output directory
OUTPUT_DIR.mkdir(parents=True, exist_ok=True)

with xw.App(visible=False) as app:

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

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

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

[moretinga]

Great video, short and directly to the point. An example of how these tutorials should be. Thanks.

[qpeilcd]

Excellent tutorial!! For more tutorials like that, add more videos in this sense, as this would help us a lot! Hugs

[zeciconicaciojr]

Great video, but the folders weren't exactly the way they were before. But it saved me a lot of work.
THANK YOU!

[CodingIsFun]

*What other Excel tasks would you like to automate?*

[Raven-uguw]

What are you talking about? Where do you type that in?

[MrMallesh]

I have a workbook which contains 200 sheets in a single workbook, I want to combine it into 20 workbooks. ... I am doing via excel macro it takes 45 minutes. Can we do it via python!

[chatishwaranTSsathiyamoorthy]

Excellent Explanation .. Thanks a lot for the video.

I have one doubt/question
1. We are splitting country basis excel, if we want to add one more sheet with product name?
how can we do

[kevinoverhead]

Thank you! Question: Is it possible to go the other way, take several excel files and combine into one excel file with multiple tabs?

[bongadywili]

How can I save One worksheet as a seperate workbook

[AhmadArafatAhmedArafat]

I have multiple excel files, I want each file to be an excel sheet, how do we do that?

[rajeevmenon]

Can you please include a video in splitting a long text file into multiple files with proper naming using some pattern matching?

[ceciliepedersen]

if I have a folder with multiple excel files with the same amount of sheets how to I split all the files in the folder? and save them as the file name + sheet name?

[krfmkms]

hello, can you split the excel file by the specific rows, and just cut the file to the half and mak anither excel file. Because I have a lot of rows in one file and I couldnt merge 6 files saving all the information. So, can you help me ? how Can I divide excel file like 500k rows in one file and other rows will be in other, like specyfying the index of row it in Python . Thanks.

[nevilledoke]

sir, i want to divide/ split the excel file by its index. not 'unique value' plzz help

[ianpropst-campbell]

Thank you for this tutorial! I attempted both the code from the video as well as the update code you included in the description, but neither of them works. I run the code and nothing happens. No errors but also no new files either. Any suggestions? Thank you once again for your tutorial.

[omsangwan]

Hi, first of all a great video, thanks a lot, just wanted to ask if I want to only paste the values not the formula to the new file while using xlwings, how to do that

[larry]

Hey when I run this im getting a error (Exception has occurred: KeyError
'pop from an empty set') However when I then run it again the script works. Whats going on?

[alanmackintosh]

Love your videos. I'd like to do the exact opposite to this video. I have several excel spreadsheets which I would like to copy each sheet from the workbook, into a new workbook. I do not want to combine the data, or use pandas. Just simply copy the sheets into a new workbook. Are you able to do a tutorial like this?

[samytounsi]

Hi, Thank you very good tutorial,

But I have this error in pycharm when I try it : (I installed the project interpreter xlwings) :

"Traceback (most recent call last):
File "/Users/Sam/PycharmProjects/splitingfile/venv/lib/python3.9/site-packages/aeosa/appscript/reference.py", line 599, in __getattr__

selectortype, code =
KeyError: 'Copy'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/Users/Sam/Desktop/OCUS/Ocus Python/splitfile.py", line 11, in
sheet.api.Copy()
File "/Users/Sam/PycharmProjects/splitingfile/venv/lib/python3.9/site-packages/aeosa/appscript/reference.py", line 601, in __getattr__
raise AttributeError("Unknown property, element or command: {!r}".format(name)) from e
AttributeError: Unknown property, element or command: 'Copy' "

Do you know why ? Thanks in advance

[arunar]

Excellent but I need to store the Separated excel sheet into Allotted Sheet Ex:
Example: Three different Collage data excel sheets contains in Folder (Sheet 1: Name, Sheet 2: Department on so on)

But using the above trick I easily separated the Sheet but I need to store Separated the sheet a particular sheet which name has "Name" on so on

So kindly make the video for that.