📁 Day 9 – File Handling and Data I/O (CSV, TXT, Excel)

1. Learning Objectives

By the end of Day 9, you will be able to:

Open, read, and write plain text files (.txt) using Python’s built‑in open().
Read and write CSV files (comma‑separated values) using the csv module.
Use pandas to read and write Excel (.xlsx) and CSV files.
Process AEC data from external files: structural member schedules, material take‑offs, coordinate lists.
Write formatted reports and export results for use in spreadsheets or BIM tools.

2. Concept Explanation

2.1 Why File I/O Matters in AEC

In practice, you rarely type data directly into a Python script. Instead you:

Import a beam schedule from Excel to perform design checks.
Read a CSV of column coordinates exported from CAD.
Write a material quantity report to share with a quantity surveyor.
Parse a text‑based model export from a structural analysis tool.

File I/O bridges Python and your existing project data.

2.2 Reading/Writing Text Files (`.txt`)

Opening a file: open(filename, mode)

Mode	Description
`"r"`	Read (default)
`"w"`	Write (overwrites)
`"a"`	Append
`"r+"`	Read and write

Always use a context manager (with) – it automatically closes the file.

# Reading
with open("project_notes.txt", "r") as file:
    content = file.read()
    print(content)

# Writing
with open("output.txt", "w") as file:
    file.write("Material: Concrete\nVolume: 45.2 m³\n")

2.3 Working with CSV Files

CSV = Comma‑Separated Values.
Each line is a row, columns separated by commas.

import csv

# Reading
with open("beams.csv", "r") as f:
    reader = csv.reader(f)
    header = next(reader)   # skip header row
    for row in reader:
        mark, span, load = row
        print(f"{mark}: span={span}m, load={load}kN/m")

# Writing
with open("output.csv", "w", newline="") as f:
    writer = csv.writer(f)
    writer.writerow(["Mark", "Span", "Load"])
    writer.writerow(["B1", 6.0, 25])
    writer.writerow(["B2", 4.5, 18])

2.4 Introduction to pandas for Excel

pandas is the de‑facto Python library for data analysis. It can read/write Excel, CSV, and many other formats.

Installation: pip install pandas openpyxl

Key functions:

pd.read_csv("file.csv") → DataFrame
pd.read_excel("file.xlsx", sheet_name="Sheet1") → DataFrame
df.to_csv("output.csv", index=False)
df.to_excel("output.xlsx", sheet_name="Results", index=False)

A DataFrame is like a spreadsheet in memory – rows and columns with labels.

import pandas as pd

# Read an Excel file
df = pd.read_excel("beam_schedule.xlsx", sheet_name="Beams")
print(df.head())                 # first 5 rows
print(df.columns)                # column names
print(df["Span (m)"].mean())     # average span

# Filter: beams with span > 6m
long_beams = df[df["Span (m)"] > 6.0]
print(long_beams)

# Write to CSV
df.to_csv("beams_export.csv", index=False)

Why pandas for AEC?

Handles large datasets efficiently (thousands of members).
Built‑in filtering, grouping, aggregation (like Excel pivot tables).
Seamless integration with Excel workflows.

3. Code Examples

Example 1: Reading a material take‑off from a text file

Suppose materials_takeoff.txt contains:

Concrete C30, 45.2, m³
Rebar 20mm, 12.5, tonnes
Steel UB 406, 8.3, tonnes

total_cost = 0.0
rates = {"Concrete C30": 95.0, "Rebar 20mm": 1100.0, "Steel UB 406": 1200.0}

with open("materials_takeoff.txt", "r") as f:
    for line in f:
        line = line.strip()
        if not line:
            continue
        parts = line.split(",")
        name = parts[0].strip()
        qty = float(parts[1])
        unit = parts[2].strip()
        cost = qty * rates.get(name, 0)
        total_cost += cost
        print(f"{name}: {qty:.1f} {unit} → ${cost:.2f}")

print(f"\nTotal material cost: ${total_cost:.2f}")

Example 2: CSV – column coordinate import and transformation

Imagine columns.csv:

ID,X,Y
C1,0.0,0.0
C2,6.0,0.0
C3,0.0,8.0
C4,6.0,8.0

import csv

columns = []
with open("columns.csv", "r") as f:
    reader = csv.DictReader(f)   # reads header automatically
    for row in reader:
        columns.append({
            "id": row["ID"],
            "x": float(row["X"]),
            "y": float(row["Y"])
        })

# Shift all columns by (2.0, 3.0) and write new CSV
with open("columns_shifted.csv", "w", newline="") as f:
    fieldnames = ["ID", "X", "Y"]
    writer = csv.DictWriter(f, fieldnames=fieldnames)
    writer.writeheader()
    for col in columns:
        writer.writerow({
            "ID": col["id"],
            "X": col["x"] + 2.0,
            "Y": col["y"] + 3.0
        })

print("Shifted columns written to columns_shifted.csv")

Example 3: pandas – reading an Excel beam schedule and writing a summary

Assume beam_schedule.xlsx has columns: Mark, Span_m, UDL_kNm, Depth_mm, Weight_kgpm

import pandas as pd

# Read
df = pd.read_excel("beam_schedule.xlsx", sheet_name="Beams")
print("Input data:")
print(df.head())

# Add computed columns
df["M_max_kNm"] = df["UDL_kNm"] * df["Span_m"]**2 / 8
df["Deflection_mm"] = 5 * df["UDL_kNm"] * 1000 * (df["Span_m"] * 1000)**4 / (384 * 200000 * (df["Depth_mm"]**3 / 12))
df["Defl_Limit_mm"] = df["Span_m"] * 1000 / 250
df["Deflection_OK"] = df["Deflection_mm"] <= df["Defl_Limit_mm"]

# Filter to problematic beams
critical = df[~df["Deflection_OK"]]
print(f"\n{len(critical)} beams fail deflection:")

# Write summary to Excel
with pd.ExcelWriter("beam_analysis_results.xlsx") as writer:
    df.to_excel(writer, sheet_name="Full Analysis", index=False)
    critical.to_excel(writer, sheet_name="Critical Beams", index=False)

# Also write a CSV for CAD import
df.to_csv("beam_analysis.csv", index=False)
print("Results saved to beam_analysis_results.xlsx and beam_analysis.csv")

Example 4: Writing a formatted report to a text file

beams = [
    {"mark": "B1", "span": 6.0, "M": 112.5, "S_req": 450000},
    {"mark": "B2", "span": 4.5, "M": 45.6, "S_req": 182400},
    {"mark": "B3", "span": 7.2, "M": 194.4, "S_req": 777600},
]

with open("beam_report.txt", "w") as f:
    f.write("BEAM ANALYSIS REPORT\n")
    f.write("=" * 40 + "\n")
    f.write(f"{'Mark':<8} {'Span':<8} {'M_max':<10} {'S_req':<10}\n")
    f.write("-" * 36 + "\n")
    for b in beams:
        f.write(f"{b['mark']:<8} {b['span']:<8.1f} {b['M']:<10.1f} {b['S_req']:<10.0f}\n")
    f.write("=" * 40 + "\n")

print("Report written to beam_report.txt")

4. Hands‑on Exercises (3–5 Problems)

Problem 1 – Reading a simple text file
Create a text file storeys.txt with the following content (each line: floor name, height):

Ground,4.5
Level 2,3.5
Level 3,3.5
Level 4,3.0
Roof,3.0

Write a script that reads the file, computes the total height, and prints each floor with its height.

Problem 2 – CSV column load check
Given a CSV file column_loads.csv:

ID,Load_kN
C1,850
C2,920
C3,1100
C4,780
C5,650

Read the file using csv.DictReader. For each column, check if the load exceeds a capacity of 1000 kN. Print warnings for overstressed columns.

Problem 3 – pandas material cost estimator
Using pandas, read an Excel file materials.xlsx with columns: Material, Volume_m3, Unit_Cost_per_m3.
Compute total cost per material and overall total. Write the result to a new Excel file with an added column Total_Cost.

Problem 4 – Export a beam schedule to CSV
You have a list of beam dictionaries:

beams = [
    {"mark": "B1", "span": 6.0, "load": 25, "depth": 400},
    {"mark": "B2", "span": 4.5, "load": 18, "depth": 350},
    {"mark": "B3", "span": 7.2, "load": 30, "depth": 500},
]

Write code that exports this list to a CSV file named beam_schedule_output.csv.

Solutions (attempt first):

# P1
with open("storeys.txt", "r") as f:
    total = 0
    for line in f:
        line = line.strip()
        if not line:
            continue
        name, height_str = line.split(",")
        height = float(height_str)
        total += height
        print(f"{name}: {height} m")
    print(f"Total height: {total} m")

# P2
import csv
capacity = 1000
with open("column_loads.csv", "r") as f:
    reader = csv.DictReader(f)
    for row in reader:
        load = float(row["Load_kN"])
        if load > capacity:
            print(f"WARNING: Column {row['ID']} load {load} kN exceeds capacity!")

# P3
import pandas as pd
df = pd.read_excel("materials.xlsx")
df["Total_Cost"] = df["Volume_m3"] * df["Unit_Cost_per_m3"]
print(df)
total = df["Total_Cost"].sum()
print(f"Overall cost: ${total:.2f}")
df.to_excel("materials_with_cost.xlsx", index=False)

# P4
import csv
beams = [
    {"mark": "B1", "span": 6.0, "load": 25, "depth": 400},
    {"mark": "B2", "span": 4.5, "load": 18, "depth": 350},
    {"mark": "B3", "span": 7.2, "load": 30, "depth": 500},
]
with open("beam_schedule_output.csv", "w", newline="") as f:
    writer = csv.DictWriter(f, fieldnames=["mark", "span", "load", "depth"])
    writer.writeheader()
    writer.writerows(beams)
print("Written to beam_schedule_output.csv")

5. Applied Challenge Task

Task: Interactive Structural Member Data Processor

Your task is to build a script that:

Asks the user for an input filename (Excel or CSV) containing a member schedule.
Reads the file using pandas (assume columns: Member, Span_m, Load_kNm, Section).
Asks the user for a material yield stress (MPa) and a deflection limit ratio (default L/250).
Computes for each member:
- Max moment: M = load * span² / 8
- Required section modulus: S_req = M * 1e6 / (0.6 * fy) (units: Nmm)
- Deflection limit: span * 1000 / ratio
- Approximate deflection (simplified): deflection = 5 * load * 1000 * (span * 1000)**4 / (384 * 200000 * (200**3/12))
  (assume a rectangular section 200mm wide, depth = depth from section name if possible, otherwise use 400mm)
Flags members that fail either strength (if actual S < S_req) or deflection.
Exports the results to a new Excel file with an added sheet for “Failed Members”.
Also writes a one‑page text summary report (member count, pass/fail counts, total steel weight assuming 80 kg/m per member).

Why this matters:
This mirrors a real engineering workflow: import a schedule → run checks → flag issues → export results for the design team. It combines all the skills from Days 1–9: variables, conditionals, loops, functions, strings, lists/dicts, file I/O, and pandas.

6. Brief Review Summary

Text files: open() with context manager (with), read/write lines.
CSV files: csv.reader, csv.writer, csv.DictReader/DictWriter.
pandas: read_excel(), read_csv(), to_excel(), to_csv() – powerful DataFrame operations.
Always handle file paths and missing files gracefully (we’ll cover exceptions properly tomorrow).
File I/O connects your Python scripts to real project data – essential for automation.

Key takeaway:
You can now import data from spreadsheets and CAD exports, process it, and export results – making your Python scripts practical tools in a real AEC workflow.

7. Preview of Next Topic (Day 10)

Tomorrow we’ll cover Exception Handling and Debugging.
You’ll learn:

Using try / except / else / finally to handle errors gracefully (e.g., file not found, bad data).
Common AEC pitfalls: division by zero in calculations, missing values, type errors.
Debugging techniques: print() debugging, using pdb, and interpreting tracebacks.
Writing robust scripts that don’t crash on messy input data.

Handling errors professionally is what separates a script from a reliable tool.

Search This Blog

Python For Engineers