Python File Handling: How to Read Excel, CSV, JSON and Text Files

To read an Excel file in Python, install pandas and openpyxl with pip install pandas openpyxl, then run import pandas as pd and df = pd.read_excel("file.xlsx"). For CSV files use pd.read_csv(). For JSON, use Python’s built-in json.load(). Plain text files open with open() and a with statement.

• Key Takeaway 1: The with open() pattern is the safest way to handle any file in Python. It closes the file automatically, even if your code crashes mid-read.
• Key Takeaway 2: pandas handles CSV and Excel in one consistent API. The built-in csv module is lighter but gives you less power out of the box.
• Key Takeaway 3: json.load() reads from a file object. json.loads() reads from a string. Mixing them up is the single most common JSON beginner mistake.
• Key Takeaway 4: A UnicodeDecodeError on a CSV almost always means you need encoding="utf-8-sig" or encoding="latin-1" in your open() call.
• Key Takeaway 5: Python can run inside Excel through Microsoft’s Python in Excel feature, available in Microsoft 365 as of 2023.

Reading and Writing Files with open()

Before you touch any specific format, you need to understand how Python actually opens a file. The open() function takes a filename and a file mode. The three modes you will use constantly are r (read), w (write, overwrites the file) and a (append, adds to the end without deleting existing content).

Here is the pattern every Python developer uses:

with open("notes.txt", "r") as f:
    content = f.read()

The with statement creates a context manager. When the block ends, Python calls f.close() for you automatically. Without it, forgetting to close a file can corrupt data or hold a file lock on Windows systems, which is a genuinely painful bug to track down.

How to read a text file in Python

For a plain .txt file, f.read() loads the whole file as one string. If you want it line by line, use f.readlines(), which returns a list. Or just loop: for line in f: reads one line at a time without loading everything into memory, which matters when a file is several hundred megabytes.

Encoding trips up a lot of beginners, especially with files exported from Indian government portals, GST return systems, or legacy ERP platforms. If you hit a UnicodeDecodeError, add encoding="utf-8-sig" to your open() call. If that still fails, try encoding="latin-1". According to the Python Software Foundation’s official documentation, UTF-8 is the default on most modern systems, but Windows-generated files often ship as CP1252 or UTF-8 with a BOM marker.

How to create a file in Python

Creating a file is just opening it in write mode. with open("output.txt", "w") as f: f.write("Hello, India!") creates the file if it does not exist, or completely wipes it if it does. Use "a" mode instead when you want to keep existing content and just add new lines at the bottom, for example when you are logging daily sales figures to a running report.

Working with CSV, Excel and JSON Files

These three formats cover about 90% of the data files you will encounter in a corporate or analytics role. Each has its own tool, and the choice between them is usually about how much processing power you need.

What is a CSV file in Python and how do you import one

A CSV (comma-separated values) file is a plain text file where each row is a record and each column is separated by a comma. It is the most universal data exchange format in use today. Python ships with a built-in csv module, but pandas is almost always the better choice for real work.

import pandas as pd
df = pd.read_csv("sales_data.csv")

This gives you a full DataFrame in one line. You can immediately filter rows, calculate totals, or export to a different format. According to the 2023 Stack Overflow Developer Survey, pandas is used by over 51% of professional data practitioners globally, making it the dominant tool in this space.

The built-in csv module makes sense when you are writing a lightweight script that does not need pandas as a dependency. Use csv.reader() to iterate row by row, or csv.DictReader() to access columns by name instead of index.

How to read an Excel file in Python

Learning how to read an Excel file in Python is one of the most practical skills you can pick up for any data or finance role. Excel files (.xlsx) are not plain text, so you cannot use open() directly. You need either pandas or openpyxl. Pandas calls openpyxl under the hood for .xlsx files, so install both:

pip install pandas openpyxl

Here is the step-by-step process to read an Excel file in Python:

1. Install the required libraries: pip install pandas openpyxl
2. Import pandas: import pandas as pd
3. Load the file: df = pd.read_excel("report.xlsx")
4. Target a specific sheet: df = pd.read_excel("report.xlsx", sheet_name="Q2")
5. Inspect the data: print(df.head())

If you need to read an Excel file in Python without pandas, use openpyxl directly. It gives you cell-level control, so you can read font colours, borders and formulas programmatically. To read multiple sheets in Excel using Python, pass sheet_name=None to pd.read_excel(), which returns a dictionary of DataFrames keyed by sheet name.

This kind of automation is exactly where Python pays for itself fastest. A finance analyst at a mid-size Indian firm who manually copies data between Excel sheets for an hour each morning can replace that entire process with a 20-line Python script. If you want to build that kind of practical skill quickly, explore the Python and data skills bootcamp programs at 3.0 University, structured specifically around real-world use cases like this.

For a broader structured learning path, 3.0 University’s online certification courses in Data Analytics, AI and Python cover file handling as part of hands-on, project-based curricula.

How to read a JSON file in Python

JSON is the default format for APIs, configuration files and NoSQL database exports. Python’s built-in json module handles it without any installation.

Use json.load(f) when you have an open file object. Use json.loads(text_string) when you already have the JSON as a string in memory, for example, a response from a REST API call. The distinction sounds minor but causes real errors if you swap them.

import json
with open("config.json", "r") as f:
    data = json.load(f)

This returns a Python dictionary. From there, you access keys normally: data["api_key"]. Writing JSON back to a file uses json.dump(data, f, indent=4), where indent=4 formats the output so it is readable by humans, not just machines.

File format comparison at a glance

Format	Best Python Tool	Readable by Humans	Supports Multiple Sheets	Typical Use Case
TXT	open() built-in	Yes	No	Logs, raw notes, config
CSV	pandas / csv module	Yes	No	Tabular data, GST exports, HR reports
XLSX (Excel)	pandas + openpyxl	No (binary)	Yes	Finance reports, SEBI filings, MIS data
JSON	json module	Yes	No	APIs, config, NoSQL output

Automating Routine File Tasks

Reading a file once is fine. The real value comes when you automate a process that runs daily, weekly or on a schedule. Think of monthly MIS reports, bank statement reconciliations, or attendance data exports from HR tools like Darwinbox or Keka, which are widely used across Indian enterprises.

A typical automation script reads a CSV export, filters rows by date or department, computes a summary, and writes the result to a new Excel file:

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

The whole thing runs in under five seconds. That same task done manually in Excel takes 30 to 60 minutes and introduces human error every single time.

According to the McKinsey Global Institute report The Future of Work After COVID-19 (2021), data collection and processing tasks account for roughly 60 to 70% of time spent in most analyst roles. Automating file handling is one of the most direct ways to reclaim that time.

If you are moving from a data-heavy role into AI or machine learning, file handling is non-negotiable groundwork. You will be loading training datasets, saving model checkpoints, and reading configuration files constantly. The guide on how to shift from data science to AI and ML covers exactly what skills bridge that gap.

Can you use Python inside Excel?

Yes, and it is a genuinely significant development. Microsoft launched Python in Excel in August 2023 for Microsoft 365 subscribers. It runs Python directly in a cell using Anaconda’s cloud environment. You can call pd.read_excel(), build matplotlib charts, and run statistical models without leaving the spreadsheet. As of early 2025, Microsoft confirmed the feature is rolling out broadly across commercial Microsoft 365 plans.

This does not replace knowing how to read an Excel file in Python through standalone scripts. But it does mean Excel users can start learning Python in a familiar interface, which lowers the barrier considerably for finance and operations professionals across India’s IT and BFSI sectors.

For a broader view of how Python fits into data-at-scale work, the Big Data Analytics notes on 3.0 University’s learning hub are a solid next read.

Python’s file handling capabilities also connect directly to larger automation pipelines. According to JetBrains’ 2023 State of Developer Ecosystem report, 59% of Python developers use the language primarily for data analysis, and file I/O is a core part of every data pipeline regardless of size or stack.

If you want to connect with other learners working through these same concepts, the REACH learner community is a good place to ask questions, share scripts and get feedback on your projects.

Frequently Asked Questions

How do I read an Excel file in Python?

Install pandas and openpyxl with pip install pandas openpyxl. Then use import pandas as pd and df = pd.read_excel("yourfile.xlsx"). To target a specific sheet, add sheet_name="SheetName". Pandas returns a DataFrame you can filter, sort and export immediately. For cell-level formatting control, use openpyxl directly instead.

How do I read an Excel file in Python without pandas?

Use the openpyxl library directly: pip install openpyxl, then from openpyxl import load_workbook and wb = load_workbook("file.xlsx"). Access sheets with ws = wb["Sheet1"] and iterate rows with for row in ws.iter_rows(values_only=True):. This approach gives you full control over cell formatting, formulas and styles without loading pandas.

How do I read multiple sheets in Excel using Python?

Pass sheet_name=None to pd.read_excel(). This returns a dictionary where each key is a sheet name and each value is a DataFrame. For example: sheets = pd.read_excel("report.xlsx", sheet_name=None), then access individual sheets with sheets["Q1"] or sheets["Q2"].

How do I read a JSON file in Python?

Use Python’s built-in json module. Open the file with with open("data.json", "r") as f: and then call data = json.load(f). This returns a Python dictionary or list, depending on the JSON structure. If you have a JSON string rather than a file, use json.loads(your_string) instead. No installation required.

How do I create and write to a file in Python?

Use open() in write mode: with open("output.txt", "w") as f: f.write("your content"). The "w" mode creates the file if it does not exist and overwrites it if it does. Use "a" (append mode) to add content without deleting what is already there. Always use the with statement so Python closes the file automatically.

How do I import a CSV file in Python?

The fastest way is import pandas as pd then df = pd.read_csv("file.csv"). For a lightweight option without pandas, use import csv and iterate with csv.reader() or csv.DictReader(). If you get a UnicodeDecodeError, add encoding="utf-8-sig" or encoding="latin-1" to your function call.

Can I use Python inside Excel?

Yes. Microsoft introduced Python in Excel in August 2023 for Microsoft 365 users. It runs Python in cells using an Anaconda cloud environment, so you can use pandas, matplotlib and other libraries directly inside a spreadsheet. It is rolling out broadly across commercial plans as of 2025. You still need to learn Python separately to use it effectively.

File handling is where Python stops being a syntax exercise and starts being genuinely useful in a real job. Once you can pull data from an Excel sheet, transform it, and write the result to a JSON file or a new CSV, you have built a reusable skill that applies to finance, HR, operations, cybersecurity log analysis and almost every other domain.

The logical next step is putting these skills inside a structured learning path. Check the 3.0 University blog for regular updates on Python tools, techniques and career paths in tech. When you are ready to go deeper, 3.0 University’s online certification courses in Cybersecurity, Ethical Hacking, Artificial Intelligence, Blockchain and Web3 are built around hands-on labs and real-world projects, so you are not just reading about file I/O, you are using it to solve actual problems.

Last updated: June 2025. Reviewed by the 3University editorial team.