How To Read Data From Text File And Write To Excel Using Python

In this tutorial, we will learn how to read data from a text file and write it into an Excel file using Python. This is a common task when dealing with data manipulation and organization. We will be using Python’s built-in library csv to read the text file and openpyxl library to write the data into an Excel file.

Step 1: Install the openpyxl library

To work with Excel files, we need the openpyxl library. If you do not have this library installed, run the following command to install it:
pip install openpyxl

Step 2: Create a sample text file

For this tutorial, let’s create a simple text file with the following data:

Name,Age,Occupation
Alice,30,Software Engineer
Bob,25,Data Analyst
Charlie,35,IT Manager

Save this data in a file named sample.txt. We will read this file and convert it into an Excel file.

Step 3: Read data from the text file

To read the data from our sample file, we will use the built-in csv library. The code below reads the data from the text file and stores it in a list named data.

import csv

data = []

with open('sample.txt', newline='') as csvfile:

reader = csv.reader(csvfile, delimiter=',')

for row in reader:

data.append(row)

print(data)

Output:

[['Name', 'Age', 'Occupation'],
['Alice', '30', 'Software Engineer'],
['Bob', '25', 'Data Analyst'],
['Charlie', '35', 'IT Manager']]

Step 4: Write data to an Excel file

Now that we have read the data from the text file, let’s write it to an Excel file using the openpyxl library. The following code creates a new Excel file named sample.xlsx and writes the data list to it.

from openpyxl import Workbook

wb = Workbook()

ws = wb.active

for row in data:

ws.append(row)

wb.save('sample.xlsx')

After running the code, an Excel file named sample.xlsx will be created in the same directory where your Python script is located. The contents of the file should be the same as the sample text file.

Full code: