Files and spreadsheets you already have · Working with Data

The first place to look for data is almost never the internet. It is the spreadsheet your colleague has been maintaining for a year, the bank statement PDF in your downloads folder, the Google Sheet of student attendance someone shared in a WhatsApp group, the dump of customer messages your boss exported last month. Most of the useful, locally-grounded, problem-specific datasets that make Nepali AI projects work begin life as files someone else has been keeping. This section is about loading them carefully.

The four file types you will meet most

In a year of building, four file types account for roughly 95% of what you will read:

CSV (.csv) — already covered in Course 01. The universal exchange format. Plain text, comma-separated.
Excel (.xlsx, occasionally .xls) — by far the most common in offices, ministries, and NGOs. Multiple sheets per file, embedded formatting, formulas.
JSON (.json) — what APIs return, what some tools export. Already covered in Course 01.
PDF (.pdf) — the format people send when they don’t want you to edit. Bank statements, government circulars, citizenship cards.

We covered CSV and JSON in Course 01. This section adds Excel and PDF — the two that show up constantly in Nepali offices and that beginners often get stuck on.

Reading Excel files

The library you want is openpyxl (installed as a dependency of pandas’s Excel reader):

pip install openpyxl

Then in your notebook, pandas does almost all the work:

import pandas as pd

df = pd.read_excel("data/attendance.xlsx")
print(df.head())

This reads the first sheet of the workbook into a DataFrame. If the file has multiple sheets — and most real ones do — you can name the sheet:

df = pd.read_excel("data/attendance.xlsx", sheet_name="Class 8")

Or read all sheets at once into a dictionary of DataFrames:

sheets = pd.read_excel("data/attendance.xlsx", sheet_name=None)
print(sheets.keys())   # dict_keys(['Class 6', 'Class 7', 'Class 8'])
print(sheets["Class 8"].head())

Three small gotchas worth knowing:

Hidden header rows. Many Excel files start with a few rows of title text ("District: Morang", "Year: 2025") before the real column headers. Use header=4 to skip ahead to the actual header row.
Merged cells. Cells merged for display become a value in the top-left and NaN everywhere else after reading. Use df.ffill() to forward-fill if a merged cell was conceptually a label for several rows.
Formulas as values. When you read an Excel file, formula results come through as values. If a cell shows 120 because =SUM(...), you get 120, not the formula. Usually what you want.

Reading Google Sheets

If the file lives in Google Sheets and you do not want to download it as Excel each time, there are two clean options:

Option 1: publish to web as CSV. In Google Sheets, File → Share → Publish to web → CSV. You get a URL that always reflects the current state of the sheet, and pandas reads it directly:

url = "https://docs.google.com/spreadsheets/d/.../pub?output=csv"
df = pd.read_csv(url)

This is the simplest path. The downside: anyone with the URL can read the sheet.

Option 2: use the Sheets API. For private sheets, install gspread and authenticate. More setup, more code; we cover the same pattern (API key, OAuth) in Chapter 2, Section 2 of this course.

For Course 02 we will use option 1 in examples — it is the path that gets you working in five minutes.

Reading PDFs

PDFs are the worst of the four formats by a wide margin, because they were designed to look the same on every screen, not to be machine-readable. There is no single perfect tool. The right approach depends on what kind of PDF you have:

Text-based PDFs (most government reports, bank statements): the text is selectable in your PDF reader. pdfplumber extracts it cleanly:

pip install pdfplumber

import pdfplumber

with pdfplumber.open("data/bank-statement.pdf") as pdf:
    for page in pdf.pages:
        print(page.extract_text())

Tabular PDFs (bank statements with rows of transactions, NEPSE daily reports): pdfplumber also extracts tables:

with pdfplumber.open("data/bank-statement.pdf") as pdf:
    rows = []
    for page in pdf.pages:
        for table in page.extract_tables():
            rows.extend(table)

You will spend more time cleaning these tables than extracting them — column alignment in PDFs is approximate and pdfplumber does its best.

Image-based PDFs (a PDF that is really just photos of pages): the text is not selectable. You need OCR — same as in Course 01, Chapter 5. Run each page image through Tesseract, then assemble the text.

What if the file is just chaotic text?

Sometimes you get a folder of .txt files (chat logs, scraped articles, free-form notes) or .docx files (typed reports from district offices). For these:

.txt: open and read directly. Cleaning is the work.
.docx: install python-docx and extract paragraph text:

pip install python-docx

from docx import Document
doc = Document("data/report.docx")
text = "\n".join(p.text for p in doc.paragraphs)

For most projects in this course, you will not need this — most useful local data lives in CSV, Excel, or PDF.

A small workflow that will save you hours

A discipline that pays for itself within a week:

Make a data/ folder. Every dataset for every project goes in here. Never download into Desktop or Downloads.
Make a data/raw/ and a data/clean/. Keep the original file you received in raw/ and never edit it. Cleaned versions go in clean/.
Add a small data/README.md with one line per file: where it came from, when you received it, and what you did to clean it.
Add data/raw/ to your .gitignore — raw data is often sensitive and almost never belongs in a git repo. (You may want data/clean/ ignored too, depending on size.)

These four steps cost five minutes per project. They prevent: “which version of the file was right?”, “what did I change?”, “wait, did I just push someone’s personal info to GitHub?”

Check your understanding

Quick check

—

An NGO sends you an Excel file with one sheet per district — 77 sheets in total. You want all rows in one DataFrame with a column tracking which district each row came from. Which is the most direct approach?

Open each sheet individually with `pd.read_excel(..., sheet_name='X')` 77 times in a loop.
Use `pd.read_excel(..., sheet_name=None)` to load all sheets into a dict, then concat them with a new district column added per DataFrame before concat.
Convert the Excel file to CSV manually first, then read with pd.read_csv.
Excel files cannot be read in Python; ask the NGO to send something else.