Split Large Excel Files into CSVs with a Few Lines of Python

1. Introduction

In daily work we may need to split a large Excel file into multiple CSV files, either by size or by the content of a specific column. Using a few lines of Python this can be done easily.

2. Project Goal

Split a single Excel file into multiple CSV files, optionally based on the values of a chosen column.

3. Preparation

Software: PyCharm

Required libraries: tkinter, pandas, xlrd

4. Analysis

1) Selecting the target file

Use tkinter.filedialog to let the user choose the file to process.

2) Reading the Excel file

Use xlrd to open the workbook and obtain the desired sheet.

3) Filtering column content

Use pandas to filter rows based on a column’s values.

4) Saving the files

Use pandas' to_csv() to write the filtered data to CSV files.

5. Implementation

Step 1: Import libraries

from tkinter import filedialog
import pandas as pd
import xlrd

Step 2: Choose the target file

path = filedialog.askopenfilename().replace('/', '\\')
first_file_fh = xlrd.open_workbook(path)
# select the file to split
first_file_sheet = first_file_fh.sheets()

Step 3: Read the Excel file

for sheetname in first_file_sheet:
    sheet_names.append(sheetname.name)
df = pd.read_excel(path, sheet_name=sheet_names[0])

Step 4: Filter by a column and save

for c in list_c:
    # loop over each column value
    df2 = df[df['地市'] == c]
    df2.to_csv('./excel_CSV/auto_ok/32_' + c + '.CSV', encoding='gbk', index=None)

6. Result

Before processing, the Excel data looks like:

After splitting, the CSV files are saved as shown:

7. Summary

This article demonstrates how to use Python to split Excel files and generate CSV files based on any column’s values, achieving the task with only a few lines of code.