Python – Split worksheet to worksheets, save Excel worksheets to csv

Same article, but for VBA is here – https://www.vitoshacademy.com/vba-split-worksheet-to-worksheets-save-excel-worksheets-to-csv/

This article does 2 things:

• Splits one worksheet to multiple worksheets
• Then goes through the worksheets and saves them as *.CSV files

So what is the idea?

Having this excel file, being able to build a few CSV files from it each with a size of 15 rows, keeping the header.

And this is how the output in CSV should look like:

Position,Average,Name
1,0.08333333333333333,Ivan
2,0.16666666666666666,Stoyan
3,0.25,Petkan
126,10.5,Gosho
249,20.75,Pesho
372,31.0,Ivan
495,41.25,Stoyan
618,51.5,Petkan
741,61.75,Gosho
864,72.0,Pesho

Ok. Fair enough. The only thing that should be noted, is that there should be input and output folder, that are pre-created in the root directory before running the code:

import pandas as pd
import datetime


def main():
    input_folder_and_file = "input/Excel-File.xlsx"
    output_folder = 'output'
    cut_step = 10

    df = pd.read_excel(input_folder_and_file)
    header = ''.join(df.columns.values)
    rows_count = df.shape[0]
    counter = 0

    for i in range(0, rows_count, cut_step):
        start_row = i
        end_row = i + cut_step
        new_df = df.iloc[start_row:end_row]

        my_date = datetime.date.today().strftime('%Y%m%d')
        file_name = f'{output_folder}/file_{my_date}-{"%06d"%i}.csv'

        print(file_name)
        counter = counter + 1
        new_df.to_csv(file_name, encoding='utf-8', index=False)

    print(f'{counter} csv files are generated...')


if __name__ == "__main__":
    main()

That’s all! 🙂