Python for Automation: Extracting Data from PDFs, CSVs, and Web APIs

It’s 6 pm on the last working day of the month and your manager needs a combined sales report by morning. The numbers are all over the place really: a finance summary in a PDF, a dozen regional CSVS in the shared folder and the live numbers only exist in the API of the company dashboard.

You could spend the whole evening copy and pasting. Or you could write a short Python script, make a cup of tea and have it do the job in under a minute.

That’s the real draw of automation. Data doesn’t usually arrive in a clean state or in one place and Python is very good at dealing with that. That’s why extraction and automation are at the very heart of any decent data analytics training in Noida. Let’s go through the three sources you’ll come across most frequently.

PDFs: Data Behind Glass

PDFs are for people to read, not for programs to parse. Invoices, contracts, annual reports etc all come in PDF format and the numbers in them are very difficult to extract. Python has three main tools for that job:

PyPDF2 extracts plain text from PDFs which contain text that has been generated digitally.
pdfplumber understands layout, spacing and tables so it’s ideal for financial statements.
Camelot focuses on tables and returns them as pandas DataFrames ready to clean.

Here is the simplest case, reading text from every page:

python

import PyPDF2

with open(“report.pdf”, “rb”) as file:

reader = PyPDF2.PdfReader(file)

for page in reader.pages:

print(page.extract_text())

One caveat: scanned PDFs are simply images of text so there’s nothing you can extract directly. You first need OCR (optical character recognition) to convert the image into text (e.g. with pytesseract). It’s a good idea to work out what type of PDF you’re dealing with before you pick a library.

CSV Files: The Everyday Workhorse

CSVs are the plain old data format of the world and pandas makes them trivial to work with. Filtering one file takes three lines:

python

import pandas as pd

df = pd.read_csv(“sales_data.csv”)

filtered = df[df[“region”] == “South”]

filtered.to_csv(“south_sales.csv”, index=False)

The real win comes when you scale it up. This script will read every CSV in a folder and merge them all into one file:

python

import os

import pandas as pd

all_data = []

for filename in os.listdir(“data_folder”):

if filename.endswith(“.csv”):

df = pd.read_csv(f”data_folder/{filename}”)

all_data.append(df)

combined = pd.concat(all_data, ignore_index=True)

combined.to_csv(“combined_output.csv”, index=False)

Merging dozens of files by hand would take an afternoon. Here it’s done in seconds and it’s as consistent every time you run it. That consistency is why pandas is in pretty much every reporting workflow and is the tool that learners on the data analytics training in Noida spend so much time with.

Web APIs: Live Data on Demand

An API allows your code to ask another system for up to date information (prices, weather, public data, social media numbers etc). Python’s requests library does the talking:

python

import requests

import pandas as pd

url = “https://api.example.com/data”

headers = {“Authorization”: “Bearer YOUR_API_KEY”}

response = requests.get(url, headers=headers)

data = response.json()

df = pd.DataFrame(data[“records”])

df.to_csv(“api_output.csv”, index=False)

The script makes an authenticated request, reads the JSON response, converts it to a table and saves it. Schedule it via cron on Linux or Task Scheduler on Windows and you’ve got a pipeline that refreshes itself.

Lots of APIs return their results in pages rather than all at once. In that situation you just loop, making a request for each page until there are no more records. The API documentation will tell you how it paginates and once you’ve seen it the pattern is straightforward enough.

Putting It All Together

The ideal scripts will incorporate all three. Picture one that pulls in the monthly sales from an API, compares those against the targets in a CSV, adds in the figures from a PDF and then writes out the full summary report. Nothing to copy and paste and no 6pm errors.

To build that you need to be comfortable with data structures, error handling (what happens if the file’s missing, what happens if the API goes down?) and file I/O. Those are the practical skills that differentiate a quick script from a robust pipeline.

Final Thoughts

Python takes the boring bit of data work and turns it into something you only need to build once. It doesn’t matter whether the data is tucked away in a PDF, scattered across a heap of CSVs or sitting behind an API, a small script can pull it out reliably every time. Start small: one file, one task, one working script. Then keep on layering on the pieces until the entire report is built for you, freeing you up to do the bit that only a human can do, which is working out what the numbers actually mean.

Business Name: ExcelR – Data Analyst, Data Science & Generative AI Course in Noida

Address: Myworx, A-5, 2nd Floor, near Noida Sector 16 Metro Station, Gautam Budh Nagar, Block A, Noida Sector 3, Noida, Uttar Pradesh 201301

Phone Number: 09187195453

Email ID: enquiry@excelr.com