Importing and Exporting CSV, Excel, and JSON Data in Python

Course: Python Training

Topic: Importing and Exporting CSV, Excel, and JSON Data

Course Link: Python Training Course at JustAcademy

Demo Registration: Register for a Free Course Demo

Website: JustAcademy Official Website

1. Introduction

Python applications frequently need to read data from external files, process it, and save the results for future use. CSV, Excel, and JSON are three common data formats used in data analysis, automation, reporting, web development, and database-related applications.

Python provides built-in modules for working with CSV and JSON files. For Excel files, developers commonly use the Pandas library along with libraries such as OpenPyXL. Understanding how to import and export these formats helps developers exchange information between applications and manage structured data efficiently.

What You Will Learn

• Understand CSV, Excel, and JSON file formats.
• Read and write CSV files using Python's built-in csv module.
• Import and export CSV and Excel data using Pandas.
• Read and write Excel workbooks and worksheets.
• Load and save JSON data using Python's json module.
• Handle missing values, encoding issues, and invalid files.
• Choose the appropriate format for different applications.
• Build a small project that imports, processes, and exports data.

2. Understanding CSV, Excel, and JSON Formats

2.1 CSV (Comma-Separated Values)

CSV is a text-based format that stores tabular data in rows and columns. Each line usually represents a record, and values are separated by commas. CSV files are widely supported by spreadsheets, databases, reporting tools, and data analysis libraries.

Example of a CSV file named employees.csv:

EmployeeID,Name,Department,Salary
101,Aarav,IT,55000
102,Meera,HR,48000
103,Rohan,Finance,62000

Advantages:

• Easy to read and create.
• Supported by many programming languages and spreadsheet tools.
• Usually smaller than equivalent spreadsheet workbooks.
• Suitable for exchanging simple tabular datasets.

Limitations:

• Does not preserve formatting, formulas, charts, or multiple worksheets.
• Data types are not explicitly stored; values are commonly read as text by basic CSV tools.
• Values containing commas, quotation marks, or newlines require correct CSV quoting.

2.2 Excel Files

Excel workbooks generally use the .xlsx extension. A workbook can contain multiple worksheets, formulas, formatting, tables, charts, and other spreadsheet features.

Example workbook: company_data.xlsx

• Employees: Employee names, departments, and salaries.
• Sales: Product sales and revenue.
• Summary: Aggregated totals and reporting information.

Advantages:

• Supports multiple worksheets in a single workbook.
• Can preserve spreadsheet formatting and formulas.
• Useful for business reports and financial analysis.
• Allows structured data to be shared with non-programmers.

Limitations:

• Workbook files can be larger than CSV files.
• Reading and writing may require third-party libraries.
• Complex formulas, macros, charts, and formatting may not be fully preserved by every Python library or workflow.

2.3 JSON (JavaScript Object Notation)

JSON is a text-based format that stores structured data using objects and arrays. It is especially common in REST APIs, configuration files, web applications, and data exchange between services.

Example of a JSON file named employee.json:

{
    "employee_id": 101,
    "name": "Aarav",
    "department": "IT",
    "salary": 55000,
    "skills": ["Python", "SQL", "Excel"]
}

Advantages:

• Supports nested objects and arrays.
• Represents strings, numbers, booleans, null values, objects, and arrays.
• Widely used in APIs and web applications.
• Can represent data that does not fit neatly into a simple table.

Limitations:

• Does not natively preserve Excel-style formatting or formulas.
• Nested JSON may require transformation before tabular analysis.
• Standard JSON does not support comments or special values such as Python's NaN.

2.4 Comparison of Data Formats

3. Preparing the Python Environment

Python includes the csv and json modules, so they do not need separate installation. Pandas is useful for data analysis and for reading and writing CSV, Excel, and JSON files.

3.1 Installing Required Libraries

pip install pandas openpyxl

openpyxl supports reading and writing modern .xlsx workbooks. Pandas can use other optional engines for different Excel formats, depending on the file type and installed packages.

3.2 Importing the Libraries

import csv
import json
import pandas as pd

For the examples in these notes, create a project folder containing your Python script and the data files. Use relative paths when appropriate, and make sure the program runs from the expected working directory.

4. Importing CSV Data Using Python's Built-in CSV Module

The csv module provides tools for reading and writing CSV files. It is a useful choice when you want to process records individually without loading the entire file into a Pandas DataFrame.

4.1 Reading a CSV File with csv.reader()

import csv

with open("employees.csv", "r", newline="", encoding="utf-8") as file:
    reader = csv.reader(file)

    for row in reader:
        print(row)

Explanation:

• open() opens the file in read mode.
• newline="" helps the CSV module handle line endings correctly.
• encoding="utf-8" supports common text characters.
• csv.reader() returns each row as a list of strings.
• The with statement closes the file automatically.

For the sample employee file, the output resembles:

['EmployeeID', 'Name', 'Department', 'Salary']
['101', 'Aarav', 'IT', '55000']
['102', 'Meera', 'HR', '48000']
['103', 'Rohan', 'Finance', '62000']

Notice that the values are strings. Convert numeric values explicitly when calculations are required.

4.2 Reading a CSV File with csv.DictReader()

DictReader uses the header row as dictionary keys. This makes the data easier to access by column name.

import csv

with open("employees.csv", "r", newline="", encoding="utf-8") as file:
    reader = csv.DictReader(file)

    for employee in reader:
        print(employee["Name"], employee["Department"])
        print(float(employee["Salary"]))

This approach is useful when the file contains named columns and the program needs only selected fields.

4.3 Handling Missing or Unexpected Columns

Before accessing a column, confirm that it exists in the header. A missing field may produce a KeyError or a missing value, depending on the method used.

import csv

with open("employees.csv", "r", newline="", encoding="utf-8") as file:
    reader = csv.DictReader(file)

    if "Name" not in (reader.fieldnames or []):
        raise ValueError("The CSV file must contain a Name column")

    for row in reader:
        name = row.get("Name", "").strip()
        if name:
            print(name)

5. Exporting Data to CSV Using Python's CSV Module

5.1 Writing Rows with csv.writer()

import csv

employees = [
    ["EmployeeID", "Name", "Department", "Salary"],
    [101, "Aarav", "IT", 55000],
    [102, "Meera", "HR", 48000],
    [103, "Rohan", "Finance", 62000]
]

with open("employees_export.csv", "w", newline="", encoding="utf-8") as file:
    writer = csv.writer(file)
    writer.writerows(employees)

print("CSV file exported successfully.")

The writerows() method writes multiple rows. Use writerow() when writing one row at a time.

5.2 Writing Dictionaries with csv.DictWriter()

import csv

employees = [
    {"EmployeeID": 101, "Name": "Aarav", "Department": "IT", "Salary": 55000},
    {"EmployeeID": 102, "Name": "Meera", "Department": "HR", "Salary": 48000}
]

fieldnames = ["EmployeeID", "Name", "Department", "Salary"]

with open("employees_dict.csv", "w", newline="", encoding="utf-8") as file:
    writer = csv.DictWriter(file, fieldnames=fieldnames)
    writer.writeheader()
    writer.writerows(employees)

writeheader() writes the column names. The order of fields is controlled by fieldnames.

5.3 Appending Rows to an Existing CSV File

import csv

new_employee = [104, "Diya", "Marketing", 51000]

with open("employees_export.csv", "a", newline="", encoding="utf-8") as file:
    writer = csv.writer(file)
    writer.writerow(new_employee)

Append mode ("a") adds data to the end of a file. It does not automatically verify that the file exists or that its header matches the new row, so validate the file structure before appending.

6. Importing CSV Data Using Pandas

Pandas provides read_csv(), which loads tabular data into a DataFrame. DataFrames support filtering, sorting, calculations, grouping, and data cleaning.

6.1 Reading a CSV File

import pandas as pd

df = pd.read_csv("employees.csv")

print(df)

Pandas typically infers column data types, so numeric columns may be loaded as integers or floating-point numbers when the values are consistent.

6.2 Inspecting Imported Data

print(df.head())
print(df.tail())
print(df.shape)
print(df.columns)
print(df.dtypes)
print(df.info())
print(df.describe())

6.3 Reading CSV Files with Common Options

df = pd.read_csv(
    "employees.csv",
    encoding="utf-8",
    sep=",",
    na_values=["N/A", "unknown", "-"]
)

Important parameters include:

• filepath_or_buffer: File path or supported data source.
• sep: Delimiter, such as a comma, semicolon, or tab.
• encoding: Character encoding used by the file.
• header: Row used as column names.
• usecols: Columns to load.
• nrows: Maximum number of rows to read.
• dtype: Explicit data types for selected columns.
• parse_dates: Columns to interpret as dates in supported cases.
• na_values: Additional values to interpret as missing.

6.4 Loading Selected Columns

df = pd.read_csv(
    "employees.csv",
    usecols=["Name", "Department", "Salary"]
)

print(df)

Loading only required columns can reduce memory usage when working with large datasets.

6.5 Handling Delimiters and Encoding

Not every CSV-like file uses commas. Some use semicolons or tabs. If all data appears in one column, check the delimiter before changing the file itself.

# Semicolon-separated file
df = pd.read_csv("employees_semicolon.csv", sep=";")

# Tab-separated file
df = pd.read_csv("employees_tab.txt", sep="\t")

# A file saved with a common Windows encoding
df = pd.read_csv("employees_windows.csv", encoding="cp1252")

Choose an encoding based on the actual source file. Do not assume that every file requires cp1252 or that replacing undecodable characters is harmless.

7. Exporting Data to CSV Using Pandas

7.1 Exporting a DataFrame

import pandas as pd

df = pd.DataFrame({
    "EmployeeID": [101, 102, 103],
    "Name": ["Aarav", "Meera", "Rohan"],
    "Department": ["IT", "HR", "Finance"],
    "Salary": [55000, 48000, 62000]
})

df.to_csv("employees_pandas.csv", index=False)

The index=False argument prevents Pandas from adding the DataFrame index as an extra column. If the index is meaningful, decide deliberately whether to export it.

7.2 Exporting Selected Columns

df[["Name", "Department"]].to_csv(
    "employee_departments.csv",
    index=False
)

7.3 Exporting Without a Header

df.to_csv(
    "employees_no_header.csv",
    index=False,
    header=False
)

Use header=False only when the receiving application expects a file without column names.

7.4 Exporting with a Different Delimiter

df.to_csv(
    "employees_semicolon.csv",
    sep=";",
    index=False,
    encoding="utf-8"
)

7.5 Appending Data with Pandas

new_employee = pd.DataFrame({
    "EmployeeID": [104],
    "Name": ["Diya"],
    "Department": ["Marketing"],
    "Salary": [51000]
})

new_employee.to_csv(
    "employees_pandas.csv",
    mode="a",
    header=False,
    index=False
)

This example assumes the destination file already exists and has matching columns. Validate the destination schema first; appending with mismatched columns can corrupt the logical structure of the dataset.

8. Importing Excel Files Using Pandas

Pandas provides read_excel() for reading Excel worksheets. For modern .xlsx files, installing openpyxl is a common setup.

8.1 Reading an Excel Workbook

import pandas as pd

df = pd.read_excel("company_data.xlsx")

print(df.head())

By default, Pandas reads the first worksheet. If a workbook contains multiple sheets, specify which worksheet to import.

8.2 Reading a Specific Worksheet

df = pd.read_excel(
    "company_data.xlsx",
    sheet_name="Employees"
)

print(df)

You can select a worksheet by name or zero-based position.

# Read the second worksheet
df = pd.read_excel(
    "company_data.xlsx",
    sheet_name=1
)

8.3 Reading Multiple Worksheets

all_sheets = pd.read_excel(
    "company_data.xlsx",
    sheet_name=None
)

for sheet_name, sheet_df in all_sheets.items():
    print("Worksheet:", sheet_name)
    print(sheet_df.head())

When sheet_name=None is used, Pandas returns a dictionary mapping worksheet names to DataFrames.

8.4 Reading Selected Columns and Rows

df = pd.read_excel(
    "company_data.xlsx",
    sheet_name="Employees",
    usecols=["Name", "Department", "Salary"],
    nrows=100
)

Excel import options can include:

• sheet_name: Worksheet name, index, or None for all worksheets.
• header: Row used for column names.
• usecols: Columns to import.
• nrows: Maximum number of rows to read.
• skiprows: Rows to skip at the beginning or specified positions.
• dtype: Data types to request for selected columns.
• na_values: Additional missing-value markers.

8.5 Handling Excel Dates and Missing Values

df = pd.read_excel(
    "sales.xlsx",
    sheet_name="Sales",
    na_values=["N/A", "Not Available", "-"]
)

df["OrderDate"] = pd.to_datetime(
    df["OrderDate"],
    errors="coerce"
)

print(df[["OrderDate"]].head())

The errors="coerce" option converts unparseable dates into NaT (a missing datetime value). Inspect these rows before deciding whether to remove them or correct the source data.

9. Exporting Data to Excel Using Pandas

9.1 Writing a DataFrame to Excel

import pandas as pd

df = pd.DataFrame({
    "Product": ["Laptop", "Monitor", "Keyboard"],
    "Quantity": [5, 8, 20],
    "UnitPrice": [50000, 12000, 1500]
})

df.to_excel(
    "sales_report.xlsx",
    sheet_name="Sales",
    index=False
)

This creates an Excel workbook with one worksheet named Sales.

9.2 Exporting Multiple Worksheets

import pandas as pd

employees = pd.DataFrame({
    "Name": ["Aarav", "Meera"],
    "Department": ["IT", "HR"]
})

sales = pd.DataFrame({
    "Product": ["Laptop", "Monitor"],
    "Revenue": [250000, 96000]
})

with pd.ExcelWriter("company_report.xlsx", engine="openpyxl") as writer:
    employees.to_excel(
        writer,
        sheet_name="Employees",
        index=False
    )
    sales.to_excel(
        writer,
        sheet_name="Sales",
        index=False
    )

print("Workbook created successfully.")

The ExcelWriter context manager allows multiple DataFrames to be written to the same workbook. The file is saved when the context closes successfully.

9.3 Exporting a Summary Report

summary = df.groupby("Product", as_index=False).agg(
    TotalQuantity=("Quantity", "sum"),
    AverageUnitPrice=("UnitPrice", "mean")
)

summary.to_excel(
    "product_summary.xlsx",
    sheet_name="Summary",
    index=False
)

This example assumes the current DataFrame contains Product, Quantity, and UnitPrice columns. Grouping and aggregating data before export is useful for business reporting.

9.4 Appending or Replacing a Worksheet

import pandas as pd

updated_data = pd.DataFrame({
    "Name": ["Diya", "Kabir"],
    "Department": ["Marketing", "Operations"]
})

with pd.ExcelWriter(
    "company_report.xlsx",
    engine="openpyxl",
    mode="a",
    if_sheet_exists="replace"
) as writer:
    updated_data.to_excel(
        writer,
        sheet_name="Employees",
        index=False
    )

Append mode requires an existing workbook. The if_sheet_exists="replace" option replaces the specified worksheet's contents. Use it carefully because the existing sheet's data and formatting may be lost or changed. Behavior can depend on the Pandas version and workbook engine.

10. Working with Excel Workbooks Using OpenPyXL

OpenPyXL gives direct access to cells and worksheets in modern Excel workbooks. It is useful when a task requires cell-level operations rather than DataFrame-based analysis.

10.1 Creating a Workbook

from openpyxl import Workbook

workbook = Workbook()
sheet = workbook.active
sheet.title = "Employees"

sheet.append(["EmployeeID", "Name", "Department"])
sheet.append([101, "Aarav", "IT"])
sheet.append([102, "Meera", "HR"])

workbook.save("employees_openpyxl.xlsx")

10.2 Reading Cell Values

from openpyxl import load_workbook

workbook = load_workbook("employees_openpyxl.xlsx", data_only=False)
sheet = workbook["Employees"]

for row in sheet.iter_rows(values_only=True):
    print(row)

values_only=True returns cell values rather than cell objects. When reading formulas, data_only=False exposes formula expressions; data_only=True reads cached results when available. OpenPyXL does not calculate Excel formulas itself.

10.3 Updating Cells

from openpyxl import load_workbook

workbook = load_workbook("employees_openpyxl.xlsx")
sheet = workbook["Employees"]

sheet["B2"] = "Aarav Sharma"
sheet["C2"] = "Engineering"

workbook.save("employees_updated.xlsx")

Saving to a new filename helps preserve the original workbook. For macro-enabled workbooks, additional care is needed to preserve VBA content, and some workbook features may not be fully supported.

11. Importing JSON Data in Python

The built-in json module converts JSON text into Python objects. JSON objects normally become dictionaries, arrays become lists, and JSON values such as numbers, strings, booleans, and null become their corresponding Python values.

11.1 Reading a JSON File with json.load()

Create a file named employee.json containing:

{
    "employee_id": 101,
    "name": "Aarav",
    "department": "IT",
    "salary": 55000,
    "skills": ["Python", "SQL", "Excel"]
}

Read the file as follows:

import json

with open("employee.json", "r", encoding="utf-8") as file:
    employee = json.load(file)

print(employee)
print(employee["name"])
print(employee["skills"])

json.load() reads JSON data from a file object and converts it into Python data structures.

11.2 Parsing a JSON String with json.loads()

import json

json_text = '{"name": "Meera", "department": "HR", "salary": 48000}'

employee = json.loads(json_text)

print(employee["name"])
print(employee["salary"])

Use json.loads() when the JSON content is already available as a string, such as a response body received from an API.

11.3 Accessing Nested JSON Values

import json

json_text = '''
{
    "employee": {
        "id": 101,
        "name": "Aarav",
        "address": {
            "city": "Mumbai",
            "country": "India"
        }
    }
}
'''

data = json.loads(json_text)

print(data["employee"]["name"])
print(data["employee"]["address"]["city"])

Use the appropriate sequence of dictionary keys and list indexes to access nested information. For real-world data, check whether expected keys exist before accessing them.

11.4 Importing JSON Arrays

import json

json_text = '''
[
    {"id": 1, "name": "Aarav", "salary": 55000},
    {"id": 2, "name": "Meera", "salary": 48000},
    {"id": 3, "name": "Rohan", "salary": 62000}
]
'''

employees = json.loads(json_text)

for employee in employees:
    print(employee["name"], employee["salary"])

JSON arrays containing objects are often convenient for representing collections of records.

12. Exporting JSON Data in Python

12.1 Writing JSON to a File with json.dump()

import json

employee = {
    "employee_id": 101,
    "name": "Aarav",
    "department": "IT",
    "salary": 55000,
    "skills": ["Python", "SQL", "Excel"]
}

with open("employee_export.json", "w", encoding="utf-8") as file:
    json.dump(employee, file, indent=4, ensure_ascii=False)

json.dump() writes a Python object directly to a file. The indent=4 option makes the JSON easier to read, while ensure_ascii=False allows non-ASCII characters to be written directly.

12.2 Converting Python Objects to JSON Strings

import json

employee = {
    "name": "Meera",
    "department": "HR",
    "active": True
}

json_text = json.dumps(employee, indent=2)

print(json_text)

json.dumps() returns a JSON string instead of writing directly to a file. It is useful for preparing data for an API or another application.

12.3 Controlling JSON Output

import json

data = {
    "name": "Aarav",
    "skills": ["Python", "Pandas"],
    "active": True
}

with open("employee_pretty.json", "w", encoding="utf-8") as file:
    json.dump(
        data,
        file,
        indent=2,
        sort_keys=True,
        ensure_ascii=False
    )

• indent: Controls indentation for readable output.
• sort_keys=True: Sorts dictionary keys in the output.
• ensure_ascii=False: Preserves Unicode characters in the output text.

12.4 Understanding JSON and Python Data Types

Not every Python object can be serialized automatically. For example, sets, custom class instances, and some date or datetime objects require conversion or a custom JSON encoder.

13. Importing and Exporting JSON with Pandas

Pandas can work with JSON data, especially when JSON contains records suitable for tabular analysis.

13.1 Reading JSON Records

import pandas as pd

df = pd.read_json("employees.json")

print(df.head())

This works when the JSON structure matches a layout supported by read_json(). JSON files can use several orientations, so inspect the structure when importing data fails or columns appear unexpectedly.

13.2 Reading JSON Lines

JSON Lines, commonly using the .jsonl extension, stores one JSON value per line. It is frequently used for logs and large datasets.

import pandas as pd

df = pd.read_json(
    "employees.jsonl",
    lines=True
)

print(df)

Use lines=True when each line contains a separate JSON object. Do not use it indiscriminately for a standard JSON array formatted across multiple lines.

13.3 Exporting a DataFrame to JSON

import pandas as pd

df = pd.DataFrame({
    "EmployeeID": [101, 102],
    "Name": ["Aarav", "Meera"],
    "Department": ["IT", "HR"]
})

df.to_json(
    "employees_records.json",
    orient="records",
    indent=2,
    force_ascii=False
)

The orient="records" option writes each DataFrame row as a JSON object inside an array. The force_ascii=False option preserves Unicode characters in the output text.

13.4 Common JSON Orientations

Choose the orientation based on the receiving application's expected JSON structure.

14. Converting Data Between CSV, Excel, and JSON

Data often arrives in one format and must be delivered in another. Pandas makes many conversions straightforward because the imported data can be represented as a DataFrame.

14.1 CSV to Excel

import pandas as pd

df = pd.read_csv("employees.csv")
df.to_excel("employees_converted.xlsx", index=False)

print("CSV converted to Excel.")

14.2 Excel to CSV

import pandas as pd

df = pd.read_excel(
    "company_data.xlsx",
    sheet_name="Employees"
)

df.to_csv(
    "employees_converted.csv",
    index=False
)

CSV cannot retain multiple worksheets, formatting, or formulas. Export only the worksheet or data that should be represented in the CSV file.

14.3 CSV to JSON

import pandas as pd

df = pd.read_csv("employees.csv")

df.to_json(
    "employees_converted.json",
    orient="records",
    indent=2,
    force_ascii=False
)

14.4 JSON to CSV

import pandas as pd

df = pd.read_json("employees.json")

df.to_csv(
    "employees_from_json.csv",
    index=False
)

This works most directly when JSON contains flat records. Nested objects and arrays may need normalization with pd.json_normalize() or another transformation before exporting to a flat CSV file.

14.5 Excel to JSON

import pandas as pd

df = pd.read_excel("company_data.xlsx", sheet_name="Employees")

df.to_json(
    "employees_from_excel.json",
    orient="records",
    indent=2,
    force_ascii=False
)

14.6 JSON to Excel

import pandas as pd

df = pd.read_json("employees.json")
df.to_excel("employees_from_json.xlsx", index=False)

For nested JSON, decide how nested fields should be represented in columns before writing the Excel file.

15. Cleaning and Transforming Imported Data

Data imported from external files may contain missing values, duplicate records, inconsistent capitalization, extra spaces, or incorrect data types. Cleaning data before exporting it helps ensure that the output is reliable.

15.1 Identifying Missing Values

import pandas as pd

df = pd.read_csv("employees.csv")

print(df.isna().sum())

15.2 Removing Duplicate Rows

df = df.drop_duplicates()

Use drop_duplicates() only when duplicate rows are genuinely unwanted. In some datasets, repeated records may represent valid transactions.

15.3 Filling Missing Values

df["Department"] = df["Department"].fillna("Unknown")

df["Salary"] = pd.to_numeric(
    df["Salary"],
    errors="coerce"
)

df["Salary"] = df["Salary"].fillna(df["Salary"].median())

Choose a missing-value strategy based on the meaning of each column. Filling a missing salary with the median is a modeling or analysis decision, not a universal rule.

15.4 Standardizing Text

df["Name"] = df["Name"].astype("string").str.strip()
df["Department"] = (
    df["Department"]
    .astype("string")
    .str.strip()
    .str.title()
)

Standardizing text can reduce inconsistencies such as leading spaces or different capitalization. Check whether changing capitalization is appropriate for identifiers or other case-sensitive values.

15.5 Converting Numeric Columns

df["Salary"] = pd.to_numeric(
    df["Salary"],
    errors="coerce"
)

print(df["Salary"].dtype)

The errors="coerce" option turns invalid numeric strings into missing values. Inspect those values to decide whether they should be corrected or excluded.

15.6 Exporting the Cleaned Dataset

df.to_csv("employees_cleaned.csv", index=False)

df.to_excel("employees_cleaned.xlsx", index=False)

df.to_json(
    "employees_cleaned.json",
    orient="records",
    indent=2,
    force_ascii=False
)

16. Error Handling While Importing and Exporting Files

File operations can fail because of missing paths, invalid formats, encoding problems, permission restrictions, or malformed data. Add appropriate error handling for important workflows.

16.1 Handling Missing Files

from pathlib import Path
import pandas as pd

file_path = Path("employees.csv")

if not file_path.exists():
    print("The input file was not found.")
else:
    df = pd.read_csv(file_path)
    print(df.head())

16.2 Handling Invalid CSV or Excel Files

import pandas as pd

try:
    df = pd.read_csv("employees.csv")
except FileNotFoundError:
    print("Input file does not exist.")
except pd.errors.EmptyDataError:
    print("The CSV file is empty.")
except pd.errors.ParserError:
    print("The CSV file has an invalid structure.")
except UnicodeDecodeError:
    print("The file encoding could not be decoded.")

For Excel files, invalid or unsupported workbook content can also cause parsing or engine-related errors. Handle the exceptions relevant to the libraries and formats used in your application.

16.3 Handling Invalid JSON

import json

try:
    with open("employee.json", "r", encoding="utf-8") as file:
        data = json.load(file)
except FileNotFoundError:
    print("JSON file not found.")
except json.JSONDecodeError as error:
    print("Invalid JSON:", error)

16.4 Safe Export Practices

• Check that the output directory exists and that the application has write permission.
• Validate column names and data types before exporting.
• Write important results to a temporary file and replace the destination only after a successful write when appropriate.
• Keep a backup of important source files.
• Avoid overwriting original files unintentionally.
• Do not expose confidential data in exported files or logs.

17. Common Problems and Solutions

18. Complete Mini Project: Employee Data Import, Cleaning, and Export

Project Objective

Create a Python program that imports employee data from CSV, cleans the data, calculates a salary summary, and exports the results into CSV, Excel, and JSON formats.

Step 1: Create the Input CSV

Create a file named employees.csv with the following content:

EmployeeID,Name,Department,Salary
101,Aarav,IT,55000
102,Meera,HR,48000
103,Rohan,Finance,62000
104,Diya,IT,58000
105,Kabir,HR,50000

Step 2: Import and Clean the Data

import pandas as pd

df = pd.read_csv("employees.csv")

# Standardize text fields
df["Name"] = df["Name"].astype("string").str.strip()
df["Department"] = (
    df["Department"]
    .astype("string")
    .str.strip()
    .str.title()
)

# Convert numeric columns
df["EmployeeID"] = pd.to_numeric(
    df["EmployeeID"],
    errors="coerce"
)

df["Salary"] = pd.to_numeric(
    df["Salary"],
    errors="coerce"
)

# Remove rows missing required fields
df = df.dropna(
    subset=["EmployeeID", "Name", "Department", "Salary"]
)

# Remove duplicate employee records
df = df.drop_duplicates(subset=["EmployeeID"])

print(df)

Step 3: Calculate Department Salary Summaries

summary = df.groupby("Department", as_index=False).agg(
    EmployeeCount=("EmployeeID", "count"),
    AverageSalary=("Salary", "mean"),
    TotalSalary=("Salary", "sum")
)

print(summary)

The summary contains the number of employees, average salary, and total salary for each department.

Step 4: Export to CSV

df.to_csv("employees_cleaned.csv", index=False)
summary.to_csv("department_summary.csv", index=False)

Step 5: Export to Excel

with pd.ExcelWriter("employee_report.xlsx", engine="openpyxl") as writer:
    df.to_excel(
        writer,
        sheet_name="Employees",
        index=False
    )
    summary.to_excel(
        writer,
        sheet_name="Department Summary",
        index=False
    )

Step 6: Export to JSON

df.to_json(
    "employees_cleaned.json",
    orient="records",
    indent=2,
    force_ascii=False
)

summary.to_json(
    "department_summary.json",
    orient="records",
    indent=2,
    force_ascii=False
)

Step 7: Verify the Output Files

from pathlib import Path

for filename in [
    "employees_cleaned.csv",
    "department_summary.csv",
    "employee_report.xlsx",
    "employees_cleaned.json",
    "department_summary.json"
]:
    path = Path(filename)
    print(filename, "exists:", path.exists())

Expected result: The program creates cleaned employee data in CSV, Excel, and JSON formats. It also creates department-level summary files and a multi-sheet Excel report.

Project learning outcomes:

• Read CSV data and inspect its structure.
• Clean text and numeric columns.
• Remove duplicate employee IDs.
• Aggregate data using Pandas.
• Export tables into multiple file formats.
• Verify that output files were created.

19. Best Practices for File Import and Export

• Use the right format: CSV for simple tables, Excel for spreadsheet-based reporting, and JSON for structured application data.
• Validate input data: Check expected columns, required values, and data types before processing.
• Specify encoding: Use a suitable encoding such as UTF-8 when reading or writing text files.
• Use context managers: Open files with with when using Python's built-in file handling.
• Choose explicit export options: Decide whether to include indexes, headers, worksheet names, and JSON orientation.
• Preserve source data: Save cleaned or transformed data to a new file when the original must remain unchanged.
• Handle missing values deliberately: Do not automatically replace or delete missing data without considering its meaning.
• Use efficient operations: Load only necessary columns or rows when dealing with large datasets.
• Validate conversions: Compare row counts, column names, and important totals after exporting and re-importing.
• Protect sensitive information: Do not include confidential fields in files that will be shared externally.
• Account for spreadsheet formula risks: If exporting untrusted text to CSV for spreadsheet users, consider how values beginning with characters such as =, +, -, or @ may be interpreted by spreadsheet software.
• Document file assumptions: Record delimiters, worksheet names, encoding, expected columns, and output format.

20. Practice Exercises

1. Create a CSV file containing 10 student records and import it using csv.reader().
2. Read the same file with csv.DictReader() and print each student's name and marks.
3. Load a CSV file into Pandas and display its dimensions, column names, and data types.
4. Export selected DataFrame columns to a new CSV file without the index.
5. Create an Excel workbook with two worksheets named Students and Results.
6. Read a specific worksheet from an Excel workbook and calculate the average marks.
7. Read a JSON file containing a list of products and print the name and price of each product.
8. Write a Python dictionary to a formatted JSON file using json.dump().
9. Convert a CSV file into JSON using Pandas with orient="records".
10. Convert an Excel worksheet into CSV and explain which workbook features are not retained.
11. Clean a dataset by removing duplicate IDs and converting invalid numeric values to missing values.
12. Build a program that reads a sales CSV, calculates totals by product, and exports a multi-sheet Excel report.

21. Interview Questions and Answers

Q1. What is CSV?

Answer: CSV stands for Comma-Separated Values. It is a text format that represents tabular data as records separated into fields, commonly using commas.

Q2. Which Python module is used to read CSV files without external dependencies?

Answer: Python's built-in csv module provides functions such as reader(), DictReader(), writer(), and DictWriter().

Q3. What is the difference between csv.reader() and csv.DictReader()?

Answer: csv.reader() returns each row as a list, while csv.DictReader() maps fields to dictionary keys, typically using the first row as the header.

Q4. Why is Pandas useful for importing CSV files?

Answer: Pandas loads tabular data into a DataFrame, making it easier to filter, clean, aggregate, analyze, and export data.

Q5. What does index=False do in to_csv() and to_excel()?

Answer: It prevents the DataFrame index from being written as an extra column in the output file.

Q6. How do you read an Excel worksheet by name?

Answer: Use pd.read_excel("file.xlsx", sheet_name="SheetName").

Q7. How can multiple worksheets be written to one Excel workbook?

Answer: Use pd.ExcelWriter() as a context manager and call to_excel() for each DataFrame with a different worksheet name.

Q8. What is the difference between json.load() and json.loads()?

Answer: json.load() reads JSON from a file-like object, while json.loads() parses a JSON string.

Q9. What is the difference between json.dump() and json.dumps()?

Answer: json.dump() writes JSON to a file-like object, whereas json.dumps() returns the JSON representation as a string.

Q10. What is the purpose of orient="records" in Pandas JSON export?

Answer: It represents each DataFrame row as an object in a JSON array, making it useful for exchanging record-based data.

Q11. How do you handle missing values in an imported DataFrame?

Answer: Inspect them with isna(), then choose an appropriate strategy such as filling values, correcting the source, or removing affected records.

Q12. How can you read only selected CSV columns?

Answer: Use the usecols parameter in pd.read_csv().

Q13. Why might a CSV file display all values in one column?

Answer: The delimiter used by the file may differ from the assumed comma. Inspect the file and set the correct sep value.

Q14. What happens to Excel formatting when converting a worksheet to CSV?

Answer: CSV preserves plain text values in a tabular layout, not Excel formatting, formulas as spreadsheet formulas, charts, or multiple worksheets.

Q15. How do you handle malformed JSON?

Answer: Catch json.JSONDecodeError, inspect the reported location, and validate the source JSON syntax and structure.

Q16. Can JSON represent nested information?

Answer: Yes. JSON supports objects inside objects and arrays, which makes it useful for hierarchical data.

Q17. How do you convert a CSV file to Excel with Pandas?

Answer: Load the CSV using pd.read_csv(), then save the DataFrame with to_excel() and index=False when the index is not required.

Q18. Why should exported data be validated?

Answer: Validation helps identify missing rows, changed data types, unexpected columns, incorrect totals, and formatting or conversion problems.

22. Quick Revision Checklist

• csv.reader() reads CSV rows as lists.
• csv.DictReader() reads CSV rows as dictionaries.
• csv.writer() and csv.DictWriter() write CSV data.
• pd.read_csv() imports CSV data into a DataFrame.
• DataFrame.to_csv() exports DataFrame data to CSV.
• pd.read_excel() imports Excel worksheets.
• DataFrame.to_excel() exports DataFrame data to Excel.
• pd.ExcelWriter() supports writing multiple worksheets.
• json.load() reads JSON from a file.
• json.loads() parses a JSON string.
• json.dump() writes JSON to a file.
• json.dumps() creates a JSON string.
• pd.read_json() imports supported JSON structures.
• DataFrame.to_json() exports a DataFrame to JSON.
• Validate column names, types, missing values, and output files.
• Choose CSV, Excel, or JSON according to the receiving application's requirements.

23. Conclusion

Importing and exporting CSV, Excel, and JSON data is an essential Python skill for data analysis, reporting, automation, and application development. Python's built-in csv and json modules provide direct file handling, while Pandas simplifies structured data transformations and file conversions. OpenPyXL provides additional control over modern Excel workbooks.

By practicing these techniques, you can build reliable workflows that import external data, clean and validate it, perform useful calculations, and export results in the format required by another application or business user.

Continue learning with the Python Training Course at JustAcademy. You can also register for a course demo or visit the JustAcademy website for more learning resources.