Converting Spreadsheets Between Formats with Python
Tabular data exists in many formats: Excel (.xlsx, .xls), CSV, ODS (LibreOffice), JSON, Parquet, HTML, Feather, and more. Python with pandas and openpyxl makes these conversions trivial, including batch processing, multiple sheets, and transformations along the way.
Installation
pip install pandas openpyxl odfpy xlrd pyarrow fastparquet
# xlrd for .xls (Excel 97-2003)
# odfpy for ODS (OpenDocument)
# pyarrow for Parquet
Universal Reading with pandas
import pandas as pd
def read_table(path, sheet=0, **kwargs):
"""Read any tabular format based on file extension."""
ext = path.lower().rsplit('.', 1)[-1]
readers = {
'xlsx': lambda p: pd.read_excel(p, sheet_name=sheet, **kwargs),
'xls': lambda p: pd.read_excel(p, sheet_name=sheet, engine='xlrd', **kwargs),
'ods': lambda p: pd.read_excel(p, sheet_name=sheet, engine='odf', **kwargs),
'csv': lambda p: pd.read_csv(p, **kwargs),
'tsv': lambda p: pd.read_csv(p, sep='\t', **kwargs),
'json': lambda p: pd.read_json(p, **kwargs),
'parquet': lambda p: pd.read_parquet(p, **kwargs),
'html': lambda p: pd.read_html(p, **kwargs)[0],
}
if ext not in readers:
raise ValueError(f"Unsupported format: .{ext}")
df = readers[ext](path)
print(f"Read: {path} → {df.shape[0]} rows × {df.shape[1]} columns")
return df
df = read_table("sales.xlsx")
print(df.head())
Excel → CSV
import pandas as pd
from pathlib import Path
def excel_to_csv(xlsx_path, output_dir=None, sep=',', encoding='utf-8-sig'):
"""
Convert each Excel sheet to a separate CSV file.
utf-8-sig includes BOM for Excel Windows compatibility.
"""
output_dir = Path(output_dir or Path(xlsx_path).parent)
output_dir.mkdir(exist_ok=True)
stem = Path(xlsx_path).stem
xl = pd.ExcelFile(xlsx_path)
generated = []
for sheet_name in xl.sheet_names:
df = xl.parse(sheet_name)
if df.empty:
print(f" (skipping empty sheet: {sheet_name})")
continue
safe_name = sheet_name.replace(' ', '_').replace('/', '-')
csv_path = output_dir / f"{stem}_{safe_name}.csv"
df.to_csv(csv_path, index=False, sep=sep, encoding=encoding)
generated.append(csv_path)
print(f" ✓ {csv_path.name}: {len(df)} rows")
return generated
files = excel_to_csv("annual_report.xlsx", "csvs/")
Multiple CSVs → Excel (Multi-sheet Workbook)
import pandas as pd
from pathlib import Path
def csvs_to_excel(csv_list, output_path, sheet_names=None):
"""Combine multiple CSV files into one Excel workbook, one sheet each."""
with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
for i, csv_path in enumerate(csv_list):
df = pd.read_csv(csv_path, encoding='utf-8-sig')
name = (sheet_names[i] if sheet_names and i < len(sheet_names)
else Path(csv_path).stem[:31]) # Excel: max 31 chars
df.to_excel(writer, sheet_name=name, index=False)
print(f" ✓ Sheet '{name}': {len(df)} rows")
print(f"Excel created: {output_path}")
csvs_to_excel(
["january.csv", "february.csv", "march.csv"],
"q1_report.xlsx",
sheet_names=["January", "February", "March"]
)
Excel → ODS (LibreOffice)
import pandas as pd
def excel_to_ods(xlsx_path, ods_path):
xl = pd.ExcelFile(xlsx_path)
with pd.ExcelWriter(ods_path, engine='odf') as writer:
for sheet_name in xl.sheet_names:
df = xl.parse(sheet_name)
if not df.empty:
df.to_excel(writer, sheet_name=sheet_name[:31], index=False)
print(f" Sheet '{sheet_name}': {len(df)} rows")
print(f"ODS saved: {ods_path}")
excel_to_ods("data.xlsx", "data.ods")
Excel → JSON
import pandas as pd
import json
def excel_to_json(xlsx_path, json_path, orient='records', sheet=0, indent=2):
"""
orient options:
- 'records': list of dicts [{"col": val}, ...]
- 'split': {"columns": [...], "data": [[...]]}
- 'index': {0: {"col": val}, 1: ...}
- 'values': raw 2D array
"""
df = pd.read_excel(xlsx_path, sheet_name=sheet)
df = df.where(df.notna(), None) # NaN → None
with open(json_path, 'w', encoding='utf-8') as f:
json.dump(
json.loads(df.to_json(orient=orient, force_ascii=False, date_format='iso')),
f, ensure_ascii=False, indent=indent
)
print(f"JSON saved: {json_path} ({len(df)} records, orient='{orient}')")
excel_to_json("customers.xlsx", "customers.json", orient='records')
Output (orient='records'):
[
{
"name": "Alice Johnson",
"email": "alice@example.com",
"city": "New York",
"sales": 15000
},
...
]
Excel → Parquet (Columnar, ideal for Big Data)
import pandas as pd
from pathlib import Path
def excel_to_parquet(xlsx_path, parquet_path, sheet=0,
compression='snappy', optimize_types=True):
"""
Parquet is a highly efficient columnar format.
compression: 'snappy' (fast), 'gzip' (smaller), 'brotli'
"""
df = pd.read_excel(xlsx_path, sheet_name=sheet)
if optimize_types:
for col in df.select_dtypes(include='object'):
try:
df[col] = pd.to_numeric(df[col])
except (ValueError, TypeError):
pass
for col in df.select_dtypes(include='float64'):
if df[col].dropna().apply(float.is_integer).all():
df[col] = df[col].astype('Int64')
df.to_parquet(parquet_path, engine='pyarrow',
compression=compression, index=False)
orig = Path(xlsx_path).stat().st_size
new = Path(parquet_path).stat().st_size
print(f"Parquet: {orig/1024:.0f}KB → {new/1024:.0f}KB (saved {(1-new/orig)*100:.1f}%)")
excel_to_parquet("big_data.xlsx", "big_data.parquet")
Universal Batch Converter
import pandas as pd
from pathlib import Path
WRITERS = {
'csv': lambda df, p: df.to_csv(p, index=False, encoding='utf-8-sig'),
'xlsx': lambda df, p: df.to_excel(p, index=False, engine='openpyxl'),
'ods': lambda df, p: df.to_excel(p, index=False, engine='odf'),
'json': lambda df, p: df.to_json(p, orient='records', force_ascii=False, indent=2),
'parquet': lambda df, p: df.to_parquet(p, engine='pyarrow', index=False),
'html': lambda df, p: df.to_html(p, index=False, classes='table table-striped'),
'tsv': lambda df, p: df.to_csv(p, sep='\t', index=False),
}
READERS = {
'xlsx': lambda p: pd.read_excel(p, engine='openpyxl'),
'xls': lambda p: pd.read_excel(p, engine='xlrd'),
'ods': lambda p: pd.read_excel(p, engine='odf'),
'csv': lambda p: pd.read_csv(p),
'tsv': lambda p: pd.read_csv(p, sep='\t'),
'json': lambda p: pd.read_json(p),
'parquet': lambda p: pd.read_parquet(p, engine='pyarrow'),
}
def convert_table(source, target_fmt, output_dir=None):
source = Path(source)
src_ext = source.suffix.lstrip('.').lower()
if src_ext not in READERS:
raise ValueError(f"Unsupported input format: .{src_ext}")
if target_fmt not in WRITERS:
raise ValueError(f"Unsupported output format: .{target_fmt}")
df = READERS[src_ext](str(source))
out_dir = Path(output_dir) if output_dir else source.parent
out_dir.mkdir(exist_ok=True)
dest = out_dir / f"{source.stem}.{target_fmt}"
WRITERS[target_fmt](df, str(dest))
print(f"Converted: {source.name} → {dest.name} ({len(df)} rows)")
return str(dest)
def batch_convert(folder, src_fmt, dst_fmt, output_dir=None):
folder = Path(folder)
files = list(folder.glob(f"*.{src_fmt}"))
print(f"Converting {len(files)} .{src_fmt} files → .{dst_fmt}")
for f in files:
try:
convert_table(f, dst_fmt, output_dir)
except Exception as e:
print(f" ✗ {f.name}: {e}")
# Convert all CSVs in a folder to Excel
batch_convert("data/csvs/", "csv", "xlsx", "data/excel/")
# Convert all Excel to Parquet
batch_convert("data/excel/", "xlsx", "parquet", "data/parquet/")
Merging and Transforming During Conversion
import pandas as pd
from pathlib import Path
def merge_csvs_to_excel(csv_list, output_path, add_source_column=True):
"""Combine multiple CSVs into one Excel file, adding a source column."""
frames = []
for csv_path in csv_list:
df = pd.read_csv(csv_path)
if add_source_column:
df['source'] = Path(csv_path).stem
frames.append(df)
combined = pd.concat(frames, ignore_index=True)
with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
combined.to_excel(writer, sheet_name='Data', index=False)
ws = writer.sheets['Data']
for col in combined.select_dtypes(include='number').columns:
col_idx = combined.columns.get_loc(col) + 1
for row in range(2, len(combined) + 2):
ws.cell(row=row, column=col_idx).number_format = '#,##0.00'
print(f"Merged {len(csv_list)} CSVs → {len(combined)} rows → {output_path}")
merge_csvs_to_excel(
["sales_north.csv", "sales_south.csv", "sales_east.csv"],
"total_sales.xlsx"
)
Additional Resource
For converting Excel, CSV, ODS and other spreadsheet formats without any coding, use KaijuConverter — free and no registration required.
Related conversions
Frequent conversions across the catalogue: