How to Convert TXT to Excel in Python

Text files (.txt) are a common way to store data due to their simplicity, but they lack the structure and analytical power of Excel spreadsheets. Converting TXT files to Excel allows for better data organization, visualization, and manipulation.

While manual import text file to Excel works for small datasets, automating this process saves time and reduces errors. Python, with its powerful libraries, offers an efficient solution. In this guide, you’ll learn how to convert TXT to Excel in Python using Spire.XLS for Python, a robust API for Excel file manipulation.

• Install Python and Spire.XLS
• Convert Text File to Excel with Python
• Conclusion
• FAQ

Prerequisites

Install Python and Spire.XLS

• Install Python on your machine from python.org.
• Install the Spire.XLS for Python library via PyPI. Open your terminal and run the following command:

pip install Spire.XLS

Prepare a TXT File

Ensure your TXT file follows a consistent structure, typically with rows separated by newlines and columns separated by delimiters (e.g., commas, tabs, or spaces). For example, a sample text file might look like this:

Step-by-Step Guide to Convert Text File to Excel

Step 1: Import Required Modules

In your Python script, import the necessary classes from Spire.XLS:

from spire.xls import *
from spire.xls.common import *

Step 2: Read and Parse the TXT File

Read the text file and split it into rows and columns using Python’s built-in functions. Define your delimiter (tab, in this case):

with open("Data.txt", "r") as file:
    lines = file.readlines()
data = [line.strip().split("\t") for line in lines]

Note: If different delimiter was used, replace the parameter "\t" of the split() method (e.g., spaces: split(" ")).

Step 3: Create an Excel Workbook

Initialize a workbook object and access the first worksheet:

workbook = Workbook()
sheet = workbook.Worksheets[0]

Step 4: Write Data to the Worksheet

Loop through the parsed data and populate the Excel cells.

for row_num, row_data in enumerate(data):
    for col_num, cell_data in enumerate(row_data):
        sheet.Range[row_num + 1, col_num + 1].Value = cell_data
        sheet.Range[1, col_num + 1].Style.Font.IsBold = True

Step 5: Save the Excel File

Export the workbook as an XLSX file (you can also use .xls for older formats):

workbook.SaveToFile("TXTtoExcel.xlsx", ExcelVersion.Version2016)

TXT to Excel Full Code Example

from spire.xls import *
from spire.xls.common import *

# Read TXT data 
with open("Data.txt", "r") as file:
    lines = file.readlines()

# Split data by delimiter 
data = [line.strip().split("\t") for line in lines]

# Create an Excel workbook
workbook = Workbook()

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Iterate through each row and column in the list 
for row_num, row_data in enumerate(data):
    for col_num, cell_data in enumerate(row_data):

        # Write the data into the corresponding Excel cells
        sheet.Range[row_num + 1, col_num + 1].Value = cell_data

        # Set the header row to bold
        sheet.Range[1, col_num + 1].Style.Font.IsBold = True

# Autofit column width
sheet.AllocatedRange.AutoFitColumns()

# Save as Excel (.xlsx or.xls) file
workbook.SaveToFile("TXTtoExcel.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

The Excel workbook converted from a text file:

Conclusion

Converting TXT files to Excel in Python using Spire.XLS automates data workflows, saving time and reducing manual effort. Whether you’re processing logs, survey results, or financial records, this method ensures structured, formatted outputs ready for analysis.

Pro Tip: Explore Spire.XLS’s advanced features—such as charts, pivot tables, and encryption—to further enhance your Excel files.

FAQs

Q1: Can Spire.XLS convert large TXT files?

Yes, the Python Excel library is optimized for performance and can process large files efficiently. However, ensure your system has sufficient memory for very large datasets (e.g., millions of rows). For optimal results, process data in chunks or use batch operations.

Q2: Can I convert Excel back to TXT using Spire.XLS?

Yes, Spire.XLS allows to read Excel cells and write their values to a text file. A comprehensive guide is available at: Convert Excel to TXT in Python

Q3: How to handle the encoding issues during conversion?

Specify encoding if the text file uses non-standard characters (e.g., utf-8):

with open("Data.txt", "r", encoding='utf-8') as file:
    lines = file.readlines()

Get a Free License

To fully experience the capabilities of Spire.XLS for Python without any evaluation limitations, you can request a free 30-day trial license.