Convert Excel to CSV-John Liu Blog

This Python converts Excel worksheets to CSV comma delimited format and combine all worksheets into a single file with worksheet name as the section name.

# pip install pandas openpyxl

import pandas as pd

# Path to your Excel file
excel_file = "your excel file.xlsx"

# Output text file
output_file = "combined_output.csv"

# Read the Excel file
xls = pd.ExcelFile(excel_file)

with open(output_file, "w", encoding="utf-8") as f:
    for sheet_name in xls.sheet_names:
        f.write(f"{sheet_name}\n")  # Sheet name as heading
        
        # Read sheet into DataFrame
        df = pd.read_excel(xls, sheet_name=sheet_name)
        
        # Replace NaN/NaT with Python None
        # and force object type so None is allowed
        df = df.astype(object).where(pd.notnull(df), None)

        # Replace NaN with ''
        # df = df.fillna("")
        
        # Write column headers
        f.write(",".join(df.columns.astype(str)) + "\n")
        
        # Write data rows
        for row in df.itertuples(index=False):
            f.write(",".join(map(str, row)) + "\n")
        
        # Add a blank line between sheets
        f.write("\n")

print(f"All worksheets have been combined into '{output_file}'.")

FEATURED TAGS

ai api automation azure azure cosmos db azure openai azure vm base64 dax docker excel execution plan hyper-v infrastructure m machine learning machine learning services network network card performance power automate power bi power bi report tricks power query powershell python qgis query performance regex replication snowflake sql sql script sql server sql server admin sql server config ssl ssms troubleshooting unicode vmware