One Automation Project, Countless Failed Fixes.
By My Code Diary
I once spent three days fixing a Python script that was supposed to save me three hours a week.
Let that sink in.
The script was simple in theory: watch a folder, detect new CSV files, clean them, and push the data into a database. I had done harder things. I had built REST APIs, scraped websites, and worked with machine learning pipelines. This should have been a weekend project.
It was not.
And here is the thing nobody tells you about automation: the idea is always clean. The execution is always messy. Somewhere between your confident first commit and a working pipeline, reality shows up and starts laughing at your assumptions.
This article is specifically about what I learned from that project, no theory, no documentation. The actual hard lessons that cost me time, sleep, and at least two mugs of coffee I forgot to drink.
The Problem I Was Trying to Solve
Every Monday morning, a team would drop ten to fifteen CSV files into a shared folder. Someone would then manually open each one, fix formatting issues, remove duplicates, and paste the data into a master spreadsheet. That someone had been doing it for eight months.
I volunteered to automate it. “Give me a week,” I said. Classic mistake.
Lesson 1: Watching a Folder Is Not as Simple as It Sounds
My first instinct was to write a loop that checks the folder every few seconds. Something like this:
import os
import time
WATCH_FOLDER = "/data/incoming"
while True:
files = os.listdir(WATCH_FOLDER)
for file in files:
if file.endswith(".csv"):
process(file)
time.sleep(5)
This worked. Until it didn’t.
The problem: the script had no memory. Every five seconds, it would see the same files again and try to process them again. I added a “processed” set to track filenames, but that set lived in memory. Restart the script, lose the set, process everything twice.
The fix was to use watchdog, a library built specifically for this. It uses OS-level file system events instead of polling, which is both faster and far more reliable.
from watchdog.observers import Observer
from watchdog.events import FileSystemEventHandler
class CSVHandler(FileSystemEventHandler):
def on_created(self, event):
if event.src_path.endswith(".csv"):
process(event.src_path)
observer = Observer()
observer.schedule(CSVHandler(), path="/data/incoming", recursive=False)
observer.start()
Lesson: if a library exists for your exact problem, use it. Do not reinvent file watching.
Lesson 2: Files Arrive Before They Are Finished Writing
This one broke me.
A large CSV would appear in the folder, my script would detect it immediately and try to open it, and the file would still be half-written by the sender. Result: corrupt data, confusing errors, and an hour of debugging a problem that was not actually in my code.
The fix is unglamorous but effective: wait until the file size stops changing before processing it.
import os
import time
def wait_until_file_is_ready(filepath, wait_seconds=2):
previous_size = -1
while True:
current_size = os.path.getsize(filepath)
if current_size == previous_size:
break
previous_size = current_size
time.sleep(wait_seconds)
Two seconds of patience saved hours of confusion. Sometimes the boring solution is the right one.
Lesson 3: “Clean Data” Means Different Things to Different People
I assumed the CSVs would be consistent. They were not. Same team, same process, eight different column naming conventions. Some files had headers in row one. One file had headers in row three because someone had pasted a title and a blank line above the data.
Pro tip: never assume your input data is clean. Always validate it first, and fail loudly when it is not.
REQUIRED_COLUMNS = {"name", "email", "amount", "date"}
def validate_columns(df):
actual = set(df.columns.str.strip().str.lower())
missing = REQUIRED_COLUMNS - actual
if missing:
raise ValueError(f"Missing columns: {missing}")
This single function caught more bugs than the rest of my error handling combined.
Lesson 4: Logging Is Not Optional
During development, I used print() statements everywhere. When I deployed the script to run overnight, I came back in the morning to a blank terminal and no idea what had happened. Had it processed anything? Had it crashed? Had it run at all?
Logging is not a nice-to-have. It is the difference between knowing your automation worked and just hoping it did.
import logging
logging.basicConfig(
filename="automation.log",
level=logging.INFO,
format="%(asctime)s - %(levelname)s - %(message)s"
)
logging.info("Started processing file: report_jan.csv")
logging.error("Failed to parse row 42: unexpected null value")
Now when I wake up, I open the log file. Everything is there. Every file processed, every error, every timestamp. It changed how I build automation entirely.
Lesson 5: Automate the Error Notification Too
When something breaks at 3 AM, you want to know at 3 AM, not when someone complains the next morning. I added a simple email alert using Python’s smtplib. Nothing fancy. Just a message that says what failed and where.
The automation became trustworthy only after I stopped having to manually check whether it was working. That is the real goal: build it and forget about it, because it will tell you when it needs attention.
Lesson 6: Idempotency Will Save You
Early on, I made the mistake of designing a pipeline that could not be safely re-run. If a file got processed halfway and then crashed, running the script again would create duplicate records.
Idempotency means: running your script twice produces the same result as running it once. For database inserts, this often means using upsert logic instead of plain inserts.
# Instead of INSERT, use INSERT OR REPLACE or ON CONFLICT DO UPDATE
cursor.execute("""
INSERT INTO records (id, name, amount)
VALUES (?, ?, ?)
ON CONFLICT(id) DO UPDATE SET
name=excluded.name,
amount=excluded.amount
""", (row["id"], row["name"], row["amount"]))
This alone prevented more incidents than I can count.
Lesson 7: What Happens When the Folder Does Not Exist?
The folder path was hardcoded. One day, someone reorganized the shared drive and renamed it. The script failed silently because I had not accounted for the folder not existing.
Always validate your environment before starting. Check that folders exist, that credentials are loaded, and that database connections are alive. Do it at startup, not when you are already mid-process.
What Three Days Taught Me That Three Years Had Not
Automation is not about the happy path. Anyone can write a script that works when everything goes right. The real skill is writing one that handles the hundred things that go wrong, logs them clearly, recovers where it can, and alerts you where it cannot.
The project that was supposed to take a weekend took a week. But when it was done, it ran without touching for four months. Every Monday, the CSVs arrived, the data was cleaned, and the database was updated. No one had to touch a spreadsheet.
That is the whole point.
Start with the problem. Expect the mess. Build for reality, not the ideal version of it. And for the love of everything, set up logging on day one.
Drop your questions in the comments. What broke your automation? I would genuinely love to know.
