Scribd
Upload
8 views17 pages

File Handling and Excel Import in Python

The document provides an overview of file handling in Python, detailing the use of the open() function and its various modes for reading, writing, and appending files. It includes examples of reading files line by line, handling exceptions, and using libraries like Pandas and openpyxl for working with Excel files. Additionally, it emphasizes the importance of closing files after operations and demonstrates how to perform basic arithmetic operations within Excel spreadsheets using Python.

Uploaded by

24mu01
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
8 views17 pages

File Handling and Excel Import in Python

The document provides an overview of file handling in Python, detailing the use of the open() function and its various modes for reading, writing, and appending files. It includes examples of reading files line by line, handling exceptions, and using libraries like Pandas and openpyxl for working with Excel files. Additionally, it emphasizes the importance of closing files after operations and demonstrates how to perform basic arithmetic operations within Excel spreadsheets using Python.

Uploaded by

24mu01
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

File handling is an important part of any web application.

Python has several functions for creating, reading, updating, and deleting
files.

File Handling
The key function for working with files in Python is the open() function.

The open() function takes two parameters; filename, and mode.

There are four different methods (modes) for opening a file:

"r" - Read - Default value. Opens a file for reading, error if the file does not exist

"a" - Append - Opens a file for appending, creates the file if it does not exist

"w" - Write - Opens a file for writing, creates the file if it does not exist

"x" - Create - Creates the specified file, returns an error if the file exists

In addition you can specify if the file should be handled as binary or text mode

"t" - Text - Default value. Text mode

"b" - Binary - Binary mode (e.g. images)

Syntax
To open a file for reading it is enough to specify the name of the file:

f = open("[Link]")

The code above is the same as:

f = open("[Link]", "rt")

Because "r" for read, and "t" for text are the default values, you do not need to
specify them.

Note: Make sure the file exists, or else you will get an error.
Open a File on the Server
Assume we have the following file, located in the same folder as Python:

[Link]

Hello! Welcome to [Link]


This file is for testing purposes.
Good Luck!

To open the file, use the built-in open() function.

The open() function returns a file object, which has a read() method for reading
the content of the file:

f = open("[Link]", "r")
print([Link]())

If the file is located in a different location, you will have to specify the file path,
like this:

Open a file on a different location:

f = open("D:\\myfiles\[Link]", "r")
print([Link]())

Read Only Parts of the File


By default the read() method returns the whole text, but you can also specify
how many characters you want to return:

Example
Return the 5 first characters of the file:

f = open("[Link]", "r")
print([Link](5))

Read Lines
You can return one line by using the readline() method:

Example
Read one line of the file:

f = open("[Link]", "r")
print([Link]())

By calling readline() two times, you can read the two first lines:

Example
Read two lines of the file:

f = open("[Link]", "r")
print([Link]())
print([Link]())

By looping through the lines of the file, you can read the whole file, line by line:

Example
Loop through the file line by line:

f = open("[Link]", "r")
for x in f:
print(x)

Close Files
It is a good practice to always close the file when you are done with it.

Example
Close the file when you are finished with it:

f = open("[Link]", "r")
print([Link]())
[Link]()

Note: You should always close your files. In some cases, due to buffering,
changes made to a file may not show until you close the file

Local machine

file_path = 'C:/Users/Admin/Desktop/[Link]'
try:
with open(file_path, 'r') as file:
contents = [Link]()
print(contents)
except FileNotFoundError:
print(f"Error: File not found at {file_path}")
except Exception as e:
print(f"An error occurred: {e}")

Google colab
Upload the file, copy the path and run

# prompt: to read a text file in desktop

from [Link] import drive


[Link]('/content/drive')
file_path = '/content/drive/MyDrive/[Link]'

try:
with open(file_path, 'r') as file:
contents = [Link]()
print(contents)
except FileNotFoundError:
print(f"Error: File not found at {file_path}")
except Exception as e:
print(f"An error occurred: {e}")

Notice that the open() function takes two input parameters: file path (or file name if the
file is in the current working directory) and the file access mode. There are many modes
for opening a file:
● open('path','r'): opens a file in read mode
● open('path',w'): opens or creates a text file in write mode
● open('path',a'): opens a file in append mode
● open('path','r+'): opens a file in both read and write mode
● open('path',w+'): opens a file in both read and write mode
● open('path',a+'): opens a file in both read and write mode

After opening the file with the read mode, you can also use the following function to
access or examine the Information stored in the file:
● .read(): This function reads the complete information from the file unless a
number is specified. Otherwise, it will read the first n bytes from the text files.
● .readline(): This function reads the information from the file but not more than
one line of information unless a number is specified. Otherwise, it will read the
first n bytes from the text files. It is usually used in loops
● .readlines() – This function reads the complete information in the file and prints
them as well in a list format

Example for uploading

import numpy as nm
import [Link] as mtp
import pandas as pd
from sklearn.model_selection import train_test_split
from [Link] import RandomForestClassifier
from [Link] import LabelEncoder
from [Link] import confusion_matrix
from [Link] import accuracy_score
from [Link] import classification_report
from [Link] import files
uploaded = [Link]()
import io
df = pd.read_excel([Link](uploaded['[Link]']))
[Link]

Reading Excel Files

Pandas library also has a function, read_excel(), to read Excel files:

dataframe = pd.read_excel('path-to-file') # Full path to the txt file,


or simply the file name if the file is in
# your current working
directory
It is often the case where Excel file can contain multiple sheets. You can read data from
any sheet by providing its name in the sheet_name parameter in the read_excel()
function:

dataframe = pd.read_excel('path-to-file', sheet_name='sheet-name')


Sometimes, you might want to use one column of data for Analysis. To do this, you can
get the column data and convert it into a list of values.

print(dataframe['column-name'].tolist())

Modes of Opening a File


When opening a file in Python, you need to specify the mode in which you want to open
it. The most common modes are:

- r: Opens the file for reading (default mode).


- w: Opens the file for writing. If the file exists, its contents will be deleted. If the file
doesn't exist, it will be created.
- a: Opens the file for appending. If the file exists, new data will be appended to the end
of the file. If the file doesn't exist, it will be created.
- x: Opens the file for exclusive creation. If the file exists, the operation will fail.
- b: Opens the file in binary mode.
- t: Opens the file in text mode (default).
- +: Opens the file for updating (reading and writing).

Reading a File
Here are a few ways to read a file in Python:

Method 1: Reading a File Line by Line

file = open('[Link]', 'r')


for line in file:
print([Link]())
[Link]()

Method 2: Reading a File into a List

file = open('[Link]', 'r')


lines = [Link]()
[Link]()
print(lines)

Method 3: Reading a File into a String


file = open('[Link]', 'r')
content = [Link]()
[Link]()
print(content)

Method 4: Using the with Statement


The with statement is a more Pythonic way to open and close files. It automatically
closes the file when you're done with it.

with open('[Link]', 'r') as file:


content = [Link]()
print(content)

Note: Make sure to replace '[Link]' with the actual path to your file.

Reading an excel file using Python using Pandas

In this method, We will first import the Pandas module then we will use
Pandas to read our excel file. You can read more operations using the excel
file using Pandas
# import pandas lib as pd
import pandas as pd
# read by default 1st sheet of an excel file
dataframe1 = pd.read_excel('C:\\Users\\Admin\\Desktop\\[Link]')
print(dataframe1)

You can read an Excel file in Python using the openpyxl library, which
supports both .xlsx and .xls formats:

import openpyxl
# Load workbook
wb = openpyxl.load_workbook('C:\\Users\\Admin\\Desktop\\[Link]')
# Select active sheet (optional)
sheet = [Link]
# Access cell values
cell_value = sheet['A1'].value
print(cell_value)
# Close workbook when done
[Link]()

Replace ‘path/to/your/excel_file.xlsx’ with the actual path to your Excel file.

How to open an Excel file from Python?

To open and manipulate Excel files, you can also use the xlrd library, which
supports .xls files (not .xlsx):

import xlrd
# Open Excel file
workbook = xlrd.open_workbook('path/to/your/excel_file.xls')
# Access sheet by index or name
sheet = workbook.sheet_by_index(0) # Access first sheet
# OR
# sheet = workbook.sheet_by_name('Sheet1') # Access sheet by
name
# Read cell value
cell_value = sheet.cell_value(0, 0) # Access cell A1
print(cell_value)
# Close workbook when done
[Link]()

How to read an xlsx file in Python using Pandas?

Pandas provides a convenient way to read Excel files directly into a


DataFrame:

import pandas as pd
# Read Excel file into DataFrame
df = pd.read_excel('path/to/your/excel_file.xlsx')
# Display DataFrame
print([Link]())

Pandas’ read_excel() function can handle both .xls and .xlsx formats.

How to read xlsx file in Python using CSV?

If you prefer working with CSV format data extracted from an Excel file, you
can convert an Excel file into CSV format using Pandas:

import pandas as pd

# Read Excel file into DataFrame

df = pd.read_excel('path/to/your/excel_file.xlsx')
# Export DataFrame to CSV

df.to_csv('output_file.csv', index=False)

This converts the Excel data into a CSV file named output_file.csv in the
current directory.

import openpyxl
# Load workbook
wb = openpyxl.load_workbook('C:\\Users\\Admin\\Desktop\\[Link]')
# Select active sheet (optional)
sheet = [Link]
# Access cell values
cell_value = sheet['A3'].value
print(cell_value)
# Close workbook when done
[Link]()

rose

In [8]:
# import pandas lib as pd
import pandas as pd
# read by default 1st sheet of an excel file
dataframe1 = pd.read_excel('C:\\Users\\Admin\\Desktop\\[Link]')
print(dataframe1)

flower colour
0 jasmine white
1 rose pink
2 marygold yellow

In [7]:
import pandas as pd
# Read Excel file into DataFrame
df = pd.read_excel('C:\\Users\\Admin\\Desktop\\[Link]')
# Display DataFrame
print([Link]())
flower colour
0 jasmine white
1 rose pink
2 marygold yellow

In [15]:
import pandas as pd
# Read Excel file into DataFrame
df = pd.read_excel('C:\\Users\\Admin\\Desktop\\[Link]')
# Export DataFrame to CSV
df.to_csv('C:\\Users\\Admin\\Desktop\\output_file.csv', index=False)

In [16]:
from openpyxl import Workbook
workbook = Workbook()
[Link](filename='C:\\Users\\Admin\\Desktop\\[Link]')

In [23]:
# import openpyxl module
import openpyxl
wb = openpyxl.load_workbook('C:\\Users\\Admin\\Desktop\\[Link]')
#sheet = [Link]
data = (
(1, 2, 3),
(4, 5, 6)
)
for row in data:
[Link](row)
[Link]('C:\\Users\\Admin\\Desktop\\[Link]')

In [ ]:

In this example, a new blank Excel workbook is generated using the


openpyxl library’s Workbook() function, and it is saved as “[Link]”
with the save() method. This code demonstrates the fundamental steps for
creating and saving an Excel file in Python.
from openpyxl import Workbook
workbook = Workbook()
[Link](filename="[Link]")

Output:

After creating an empty file, let’s see how to add some data to it using
Python. To add data first we need to select the active sheet and then using
the cell() method we can select any particular cell by passing the row and
column number as its parameter. We can also write using cell names. See
the below example for a better understanding.

Example:

In this example, the openpyxl module is used to create a new Excel


workbook and populate cells with values such as “Hello,” “World,”
“Welcome,” and “Everyone.” The workbook is then saved as “[Link],”
illustrating the process of writing data to specific cells and saving the
changes
# import openpyxl module
import openpyxl
wb = [Link]()
sheet = [Link]
c1 = [Link](row=1, column=1)
# writing values to cells
[Link] = "Hello"
c2 = [Link](row=1, column=2)
[Link] = "World"
c3 = sheet['A2']
[Link] = "Welcome"
# B2 means column = 2 & row = 2.
c4 = sheet['B2']
[Link] = "Everyone"
[Link]("[Link]")

Output:

Refer to the below article to get detailed information about writing to excel.

Append data in excel using Python


In the above example, you will see that every time you try to write to a
spreadsheet the existing data gets overwritten, and the file is saved as a
new file. This happens because the Workbook() method always creates a
new workbook file object. To write to an existing workbook you must open
the file with the load_workbook() method. We will use the above-created
workbook.

Example:

In this example, the openpyxl module is employed to load an existing Excel


workbook (“[Link]”). The program accesses cell ‘A3’ in the active sheet,
updates its value to “New Data,” and then saves the modified workbook back
to “[Link].”

# import openpyxl module


import openpyxl
wb = openpyxl.load_workbook("[Link]")
sheet = [Link]
c = sheet['A3']
[Link] = "New Data"
[Link]("[Link]")

Output:

We can also use the append() method to append multiple data at the end of
the sheet.

Example:

In this example, the openpyxl module is utilized to load an existing Excel


workbook (“[Link]”). A two-dimensional data structure (tuple of tuples)
is defined and iteratively appended to the active sheet, effectively adding
rows with values (1, 2, 3) and (4, 5, 6).

# import openpyxl module


import openpyxl
wb = openpyxl.load_workbook("[Link]")
sheet = [Link]
data = (
(1, 2, 3),
(4, 5, 6)
)
for row in data:
[Link](row)
[Link]('[Link]')

Output:

Arithmetic Operation on Spreadsheet


Arithmetic operations can be performed by typing the formula in a particular
cell of the spreadsheet. For example, if we want to find the sum then =Sum()
formula of the excel file is used.

Example:

In this example, the openpyxl module is used to create a new Excel


workbook and populate cells A1 to A5 with numeric values. Cell A7 is
assigned a formula to calculate the sum of the values in A1 to A5.

# import openpyxl module

import openpyxl

wb = [Link]()

sheet = [Link]

# writing to the cell of an excel sheet

sheet['A1'] = 200

sheet['A2'] = 300

sheet['A3'] = 400

sheet['A4'] = 500

sheet['A5'] = 600
sheet['A7'] = '= SUM(A1:A5)'

# save the file

[Link]("[Link]")

Output:

Refer to the below article to get detailed information about the Arithmetic
operations on Spreadsheet.

Adjusting Rows and Column


Worksheet objects have row_dimensions and column_dimensions attributes
that control row heights and column widths. A sheet’s row_dimensions and
column_dimensions are dictionary-like values; row_dimensions contains
RowDimension objects and column_dimensions contains ColumnDimension
objects. In row_dimensions, one can access one of the objects using the
number of the row (in this case, 1 or 2). In column_dimensions, one can
access one of the objects using the letter of the column (in this case, A or B).

Example:

In this example, the openpyxl module is used to create a new Excel


workbook and set values in specific cells. The content “hello” is placed in cell
A1, and “everyone” is placed in cell B2. Additionally, the height of the first
row is set to 70 units, and the width of column B is set to 20 units.

# import openpyxl module

import openpyxl

wb = [Link]()

sheet = [Link]

# writing to the specified cell

[Link](row=1, column=1).value = ' hello '


[Link](row=2, column=2).value = ' everyone '

# set the height of the row

sheet.row_dimensions[1].height = 70

# set the width of the column

sheet.column_dimensions['B'].width = 20

# save the file

[Link]('[Link]')

Output:

Merging Cells

A rectangular area of cells can be merged into a single cell with the
merge_cells() sheet method. The argument to merge_cells() is a single string
of the top-left and bottom-right cells of the rectangular area to be merged.

Example:

In this example, the openpyxl module is employed to create a new Excel


workbook. The program merges cells A2 to D4, creating a single cell
spanning multiple columns and rows, and sets its value to ‘Twelve cells join
together.’ Additionally, cells C6 and D6 are merged, and the text ‘Two merge
cells.’ is placed in the resulting merged cell.

import openpyxl

wb = [Link]()

sheet = [Link]

sheet.merge_cells('A2:D4')

[Link](row=2, column=1).value = 'Twelve cells join together.'


# merge cell C6 and D6

sheet.merge_cells('C6:D6')

[Link](row=6, column=6).value = 'Two merge cells.'

[Link]('[Link]’)

Common questions

Powered by AI

Python can manipulate Excel files using libraries such as Pandas and Openpyxl. Pandas is often used for reading and analyzing Excel files efficiently by loading data into dataframes. It uses the read_excel() function to read data, and you can specify sheet names and columns. Openpyxl is used for creating, modifying, and saving Excel files, allowing direct manipulation of cell values, adding data, and performing arithmetic operations. You can open an existing workbook with openpyxl, manipulate it, and save the changes using the load_workbook() and Workbook() functions .

The key differences between the various file opening modes in Python determine how the file will be accessed and manipulated. The 'r' mode opens the file for reading and is the default mode. If the file does not exist, it results in an error. The 'w' mode opens the file for writing; it creates a new file or truncates an existing file. The 'a' mode opens a file for appending, which allows you to write data to the end of the file without truncating it, creating the file if it doesn't exist. The 'x' mode is for creating a new file exclusively and will fail if the file already exists. There are also binary ('b') and text ('t') modes for dealing with different data types. Additionally, modes like 'r+' and 'w+' open files for both reading and writing, but with distinctions in their behaviors regarding file creation and truncation .

Not closing a file in Python can lead to resource leaks, increased memory usage, and data corruption, especially in buffered I/O operations where data may not be written until a file is closed. Unclosed files also consume file descriptors, which are limited by the operating system. To mitigate these risks, always use the 'with' statement for file operations, which ensures that files are properly closed once operations are concluded, even if an exception occurs .

Appending data to a file is advantageous when you want to preserve existing data and add new information without overwriting. This is useful for logging purposes, audit trails, or data collection where historical records need to be retained. In Python, this is implemented using the 'a' mode when opening a file: with open('file.txt', 'a') as file: file.write('Additional data\n'). This keeps the existing file content intact and adds new data to the end .

A developer might choose to merge cells in an Excel workbook when creating reports that require spanning multiple columns with a single header or highlighting specific data sections. This can enhance the visual presentation by clearly defining data groups. In Python, this is accomplished using openpyxl by calling the merge_cells() method on a sheet object, specifying the cell range: sheet.merge_cells('A1:D1'), and assigning a value to the merged cell: sheet['A1'].value = 'Merged Header'. Such formatting allows for improved readability of data .

To safely handle file not found errors in a Python script, you can use a try-except block. Wrap your file operation within a try block, and catch the FileNotFoundError exception in the except block. For example: try: with open('file_path', 'r') as file: contents = file.read() print(contents) except FileNotFoundError: print('Error: File not found at the specified path.') This approach ensures that your program can gracefully report an error instead of crashing, providing a user-friendly message and possibly taking corrective actions .

To read specific lines or a set number of characters from a text file in Python, use the 'readline()' method for lines and 'read(n)' for characters. For lines, you can call readline() repeatedly to fetch each line. Example: f = open('file.txt', 'r'); print(f.readline()) to get the first line. For characters, specify how many you want to read with read(n). Example: f = open('file.txt', 'r'); print(f.read(5)) retrieves the first five characters. Both methods allow for precise data extraction from text files .

To set the width of a column and the height of a row in an Excel sheet using Python, use the openpyxl library. First, create or load a workbook and select a sheet: wb = openpyxl.Workbook(); sheet = wb.active. To set column width, access the column_dimensions attribute and assign a width: sheet.column_dimensions['A'].width = 20. To set row height, use the row_dimensions attribute and specify the height: sheet.row_dimensions[1].height = 70. Finally, save the workbook with wb.save('filename.xlsx'). This process allows for customizing the appearance of Excel sheets programmatically .

The 'with' statement in Python is advantageous for file operations because it ensures proper acquisition and release of resources. When opening files, it automatically handles the closing process, even if an error occurs, by implicitly calling .close() when the block is exited. This reduces the risk of file-related errors, like leaving a file open which can lead to resource leaks. It makes the code cleaner and reduces the need for explicit exception handling for closing files .

Python can convert an Excel file to a CSV file using the Pandas library. First, read the Excel file into a DataFrame using pd.read_excel('path_to_excel.xlsx'). Ensure the correct sheet is specified if necessary. Then, use the to_csv() function on the DataFrame, specifying the desired output file path: df.to_csv('output_file.csv', index=False). This method facilitates exporting Excel data to CSV format, which is widely used for data interchange due to its simplicity and compatibility .

You might also like