What Is CSV and Why Convert It?
CSV is plain text with tabular data separated by delimiters. Limitations: no data types, no formatting, no multiple sheets, no formulas.
CSV to Excel (pandas)
import pandas as pd
# Basic
df = pd.read_csv("data.csv")
df.to_excel("data.xlsx", index=False)
# Multiple sheets
with pd.ExcelWriter("report.xlsx", engine="openpyxl") as writer:
df_sales.to_excel(writer, sheet_name="Sales", index=False)
df_clients.to_excel(writer, sheet_name="Clients", index=False)
# Semicolon-delimited (European)
df = pd.read_csv("data.csv", sep=";", encoding="latin-1")
CSV to Excel with Formatting (openpyxl)
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment
import csv
wb = Workbook()
ws = wb.active
header_font = Font(bold=True, color="FFFFFF")
header_fill = PatternFill(start_color="1F4E79", end_color="1F4E79", fill_type="solid")
with open("data.csv", "r", encoding="utf-8") as f:
for row_idx, row in enumerate(csv.reader(f), 1):
for col_idx, value in enumerate(row, 1):
cell = ws.cell(row=row_idx, column=col_idx, value=value)
if row_idx == 1:
cell.font = header_font
cell.fill = header_fill
for col in ws.columns:
max_len = max(len(str(cell.value or "")) for cell in col)
ws.column_dimensions[col[0].column_letter].width = min(max_len + 2, 50)
wb.save("data_formatted.xlsx")
CSV to JSON
import csv, json
def csv_to_json(csv_path, json_path, delimiter=","):
with open(csv_path, "r", encoding="utf-8") as f:
data = []
for row in csv.DictReader(f, delimiter=delimiter):
clean = {}
for key, value in row.items():
try:
clean[key] = int(value)
except ValueError:
try:
clean[key] = float(value)
except ValueError:
clean[key] = value
data.append(clean)
with open(json_path, "w", encoding="utf-8") as f:
json.dump(data, f, ensure_ascii=False, indent=2)
print(f"Converted {len(data)} records")
CSV to XML
import csv, xml.etree.ElementTree as ET
from xml.dom import minidom
def csv_to_xml(csv_path, xml_path, root="data", row="record"):
root_el = ET.Element(root)
with open(csv_path, "r", encoding="utf-8") as f:
for rec in csv.DictReader(f):
item = ET.SubElement(root_el, row)
for key, value in rec.items():
if key:
ET.SubElement(item, key.replace(" ", "_")).text = str(value)
xml_str = minidom.parseString(ET.tostring(root_el)).toprettyxml(indent=" ")
with open(xml_path, "w", encoding="utf-8") as f:
f.write(xml_str)
Data Cleaning
import pandas as pd
df = pd.read_csv("dirty.csv")
df = df.dropna(how="all")
df["price"] = df["price"].fillna(0)
df["name"] = df["name"].str.strip()
df["date"] = pd.to_datetime(df["date"])
df = df.drop_duplicates(subset=["id"])
df.to_excel("clean.xlsx", index=False)
df.to_json("clean.json", orient="records", force_ascii=False, indent=2)
Batch Conversion
import pathlib, pandas as pd
output_dir = pathlib.Path("excels/")
output_dir.mkdir(exist_ok=True)
for csv_file in pathlib.Path("csvs/").glob("*.csv"):
try:
df = pd.read_csv(csv_file)
output = output_dir / csv_file.with_suffix(".xlsx").name
df.to_excel(output, index=False)
print(f"OK: {csv_file.name} ({len(df)} rows)")
except Exception as e:
print(f"ERROR: {csv_file.name} - {e}")
Conclusion
Python with pandas is the most complete solution for converting CSV to Excel, JSON, or XML. Two lines for simple conversions. Full openpyxl control for formatting, multiple sheets, and data cleaning.
Related conversions
Document conversions that follow this topic naturally: