Converting Excel to TXT and TXT to Excel in Python

#python #txttoexcel #exceltotxt

In data processing and automation tasks, it's often necessary to convert between various file formats, especially between Excel files and text files. Whether you need to convert Excel data to a plain text format for easy sharing or you want to import data from a text file into Excel, Python provides a straightforward way to handle these conversions.

This guide demonstrates how to convert data between Excel and txt files in Python.

Installing the Required Library

To work with Excel files in Python, you'll need a library that can handle Excel file manipulation. One such library is Spire.XLS, which allows you to read from and write to Excel files in Python. You can install it using pip:

pip install spire.xls

Once installed, you can use the library to perform various operations on Excel files, including converting them to and from text files.

Converting Excel to TXT

When you need to convert an Excel worksheet into a text file, the process involves extracting the data from the Excel sheet and saving it in a plain text format. Here's how you can do this:

Load the Excel File: Open the Excel file and read the data from the sheet.
Save the Data as TXT: Write the data to a text file, separating the values with a delimiter, such as space or tab.

Here’s an example of converting an Excel file into a text file:

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

inputFile = "Inventories.xlsx"
outputFile = "ExceltoTxt.txt"

# Create a Workbook instance
workbook = Workbook()

# Load the Excel document from disk
workbook.LoadFromFile(inputFile)

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

# Save the worksheet as a txt file
sheet.SaveToFile(outputFile, " ", Encoding.get_UTF8())
workbook.Dispose()

In this code:

The Excel file is loaded using LoadFromFile.
The content is saved as a .txt file using SaveToFile, with a space as the delimiter and UTF-8 encoding.

Converting TXT to Excel

If you have a text file containing data and you need to convert it into an Excel file, you can read the content of the text file, split it by a delimiter, and then write the data into an Excel sheet.

Here's an example of how to convert a text file into an Excel file:

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()

In this code:

The text file is read, and each line is split by the tab delimiter (\t).
The data is written into an Excel workbook.
The header row is formatted to be bold, and the column widths are autofitted for better presentation.

Key Considerations

Delimiter Choice: Be sure to choose the correct delimiter (such as tabs or commas) when reading and writing data. Consistency in delimiters ensures that the data is correctly parsed and aligned.
Data Formatting: When importing data from a text file into Excel, make sure the data types are correctly handled, especially for dates and numeric values.
Error Handling: It's a good practice to add error handling to manage potential issues, such as incorrect file paths or malformed data.

Conclusion

Converting between Excel and text file formats is a common task in data processing. With the right tools and a few lines of code, you can easily perform these conversions in Python. Whether you're exporting Excel data to a text file or importing data from a text file into Excel, Python provides an efficient way to automate the process.

DEV Community