Convert a TSV file to Excel using Python

The full form of a TSV file is a tab separated values file. TSV files are widely used with spreadsheet applications for transferring data across databases. A TSV file usually stores a data table by separating columns using tabs. Also, each record that is under the TSV file will have a separate line. Using Python, we can easily convert the TSV files to Excel files. This article goes over the specific steps for converting a TSV file to an Excel file. Read further to find out more.

How To Convert TSV File To Excel?

A tab-separated values (TSV) file is a basic text format for storing data in a tabular structure, such as a database table or spreadsheet data, as well as a method of sharing data between databases. Each record in the table corresponds to one line in the text file.

The XlsxWriter python module will be used in this case. It is employed in the creation of XLSX files. This module is not included with Python and must be explicitly installed and loaded into the working environment. To install it, run the following command in the terminal.

pip install XlsxWriter

Tsv File:

sample tsv file

Given a TSV file, the task is to convert the given TSV file to excel using Pandas in Python.

Python Program to Convert a TSV file to Excel

Approach:

  • Import the CSV and Workbook using from, import Keywords(Here from is used to import only a specific function from the module)
  • Give the sample tsv file path name and store it in a variable
  • Give the output excel file path name and store it in a variable
  • Creating an XlsxWriter Workbook object by passing the excel file to the Workbook() function
  • Apply the add_worksheet() function to the above workbook such that it will become as worksheet
  • Read the tsv file using the reader() , open() functions and store it in a variable
  • We’ll use an enumerate loop to send the data along with the row position number, which will be used as the cell number in the write row() method.
  • Close the excel(xlsx) file using the close() function
  • The Exit of the Program.

Below is the Implementation:

# import the csv and Workbook using from,import Keywords(Here from is used to import only a specific function from the module) 
import csv
from xlsxwriter.workbook import Workbook

# Give the sample tsv file path name and store it in a variable
given_tsv_file_path = 'SampleTsvFile.tsv'
# Give the output excel file path name and store it in a variable
output_excel_file = 'OutputExcelFile.xlsx'
#Creating an XlsxWriter Workbook object by passing the excel file to the Workbook() function
workbook_file = Workbook(output_excel_file)
#apply the add_worksheet() function to the above workbook such that it will become as worksheet 
worksheet_file = workbook_file.add_worksheet()

# Read the tsv file using the reader() , open() functions and store it in a variable
tsv_file = csv.reader(open(given_tsv_file_path, 'r', encoding='utf-8'), delimiter='\t')

#We'll use an enumerate loop to send the data along with the row position number, 
# which will be used as the cell number in the write row() method.
for rownum, dataVal in enumerate(tsv_file):
    worksheet_file.write_row(rownum, 0, dataVal)

# Close the excel(xlsx) file using the close() function
workbook_file.close()

Output:

output excel file image after converting it from tsv file

A TSV file is a tab-separated values file that spreadsheet applications typically use to transfer data across databases. It stores a data table with tabs separating data columns and each record in the database on a separate line. So, if you have a TSV file and want to convert it into Excel using Python, then simply follow the steps outlined on this page.

Leave a Comment