Split Excel into Multiple Files using Python

 In day to day work we get task to assign the work to multiple team members with different data to each one. Many times we get a report  from data base or some other source on which team need to work and report is in single file and you want to split the data into multiple files.

Here we are providing code snippet which can solve your task. Here we are using very popular libraries pandas and openpyxl libraries.

Split single file into multiple

Steps for Python Code to Split Data:

1. Import libraries

import pandas as pd
import numpy as np
import glob,os
import openpyxl

2. Specify the Excel file which you want to split in path variable, In run time it will ask for how many rows you need in each excel file.

path ='SingleFile.xlsx'
chunksize = int (input('Enter number of rows need in each excel\n') )

3.With above given information  we are dividing report into multiple files using pandas and numpy library.Here by changing the extension in below code you can save in required format (Ex. csv,xlsx,xls)
"chunk.to_csv('file_{:02d}.csv'.format(i), index=False, header=True)"

The out put files are saved with the prefix "File_" and suffix as file number.

i = 0
df = pd.read_excel(path,engine='openpyxl')
print('Total Number of files geneated ',len(np.array_split(df, len(df) // chunksize)))
for chunk in np.array_split(df, len(df) // chunksize):

    chunk.to_csv('file_{:02d}.csv'.format(i), index=False, header=True)
    i += 1 

Python Code to Split Data:

import pandas as pd
import numpy as np
import glob,os
import openpyxl

path ='BulkUploadTemplate.xlsx'
chunksize = int (input('Enter number of rows need in each excel\n') )

i = 0
df = pd.read_excel(path,engine='openpyxl')
print('Total Number of files geneated ',len(np.array_split(df, len(df) // chunksize)))
for chunk in np.array_split(df, len(df) // chunksize):   
    chunk.to_csv('file_{:02d}.csv'.format(i), index=False, header=True)
    i += 1 

We will get the output in the same folder where code is placed as shown below.

When everyone complete the work and updating the status in multiple files, we can combine all the files into single file using python.

Code snippet for Combining multiple files into one