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
No pythons here, hopefully!
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.
Just some input.
And this is how the output in CSV should look like:
|
1 2 3 4 5 6 7 8 9 10 11 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
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! 🙂