Last active
June 1, 2022 18:11
-
-
Save aschleg/62bcdc6cef2831c0c035 to your computer and use it in GitHub Desktop.
Alternative method to split Excel worksheet into multiple worksheets based on column name
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
from xlwings import Workbook, Range, Sheet | |
import pandas as pd | |
import os | |
# Alternative method to split an Excel worksheet into multiple sheets based on a column name. | |
# The script will prompt four questions to enter in the required information. The workbook will then open and | |
# split the prompted worksheet into separate worksheets based on the desired column name. | |
# To run, open the command prompt and enter the command python Split_Excel_Worksheet_v2.py | |
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" | |
Script | |
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" | |
path = raw_input('Enter folder path of workbook containing worksheet to split: ') | |
worksheet = raw_input('Enter workbook name with extension, e.g. example.xlsx: ') | |
sheet = raw_input('Enter worksheet name to split: ') | |
column = raw_input('Enter column name to split worksheet data on: ') | |
workbook = os.path.join(path, worksheet) | |
wb = Workbook(workbook) | |
data = pd.DataFrame(pd.read_excel(workbook, sheet, index_col=None, na_values=[0])) | |
data.sort(column, axis = 0, inplace = True) | |
split = data.groupby(column) | |
for i in split.groups: | |
Sheet.add() | |
Range('A1', index = False).value = split.get_group(i) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Can I get the same for Linux