Convert a TSV file to Excel using Python
Last Updated :
22 Oct, 2021
A tab-separated values (TSV) file is a simple text format for storing and exchanging data in a tabular structure, such as a database table or spreadsheet data. The table’s rows match the text file’s lines. Every field value in a record is separated from the next by a tab character. As a result, the TSV format is a subset of the larger (Delimiter-Separated Values) DSV format.
In this article, we will discuss how to convert a TSV file to Excel using Python. Here we will be using XlsxWriter python module. It is used to create XLSX files. This module doesn’t come in-built with python and hence has to be installed and loaded into the working space explicitly. To install this type the below command in the terminal.
pip install XlsxWriter
Approach
- Import the CSV module first, which is an inbuilt module that we won’t have to install. We’re using the csv module since tsv is quite similar to csv; the only difference is that tsv uses tab spaces instead of commas.
- We’ll also import our XlsxWriter module, which we just installed. The XlsxWriter module’s main purpose is to write Excel XLSX files.
- After importing both modules, we’ll create a variable with the path of the input file and its name, as well as the desired name and path of the output file.
- To construct a workbook, we’ll utilize the XlsxWriter module’s Workbook class. The Workbook class is the main class accessible by the XlsxWriter module, and it’s the only one you’ll need to instantiate directly. The Workbook class represents the full spreadsheet as it appears in Excel and the Excel file as it is written to disk internally.
- The variable we defined as output file will be passed to the class as a parameter.
Syntax:
workbook_object = WorkBook(output_filename)
- The name of the workbook object can be anything, just like the name of any variable. To add a worksheet to this workbook, we’ll use the method add_worksheet().
- The only thing left after this is to read the data from our tsv file and enter it into our spreadsheet. We’ll use our csv module, specifically its reader function for this, and keep in mind that we’ll be reading a file with the delimiter tab space(‘\t’).
Syntax:
with open(tsv_file, ‘r’) as csvfile:
… read_tsv = csv.reader(csvfile, delimiter =’\t’)
read_tsv = csv.reader(open(tsv_file, ‘r’), delimiter=’\t’)
- Now we’ll go through the tsv file row by row and write the data to the sheet using the write row() method. The method requires data to be written as a parameter, as well as the cell row and column number.
Syntax:
worksheet_object.write_row(row, col, data)
- Finally, we’ll use the close() method to close the workbook and write an XLSX file.
Syntax:
workbook_object.close()
Example 1: Convert TSV to XLS
TSV Used:
Code:
Python3
import csv
from xlsxwriter.workbook import Workbook
tsv_file = 'worldcup2014.tsv'
xlsx_file = 'worldcup2014.xlsx'
workbook = Workbook(xlsx_file)
worksheet = workbook.add_worksheet()
read_tsv = csv.reader(open(tsv_file, 'r', encoding='utf-8'), delimiter='\t')
for row, data in enumerate(read_tsv):
worksheet.write_row(row, 0, data)
workbook.close()
|
Output:
Example 2: Convert tsv to xls
TSV Used:
Code:
Python3
import csv
from xlsxwriter.workbook import Workbook
tsv_file = 'hospital.tsv'
xlsx_file = 'hospital.xlsx'
workbook = Workbook(xlsx_file)
worksheet = workbook.add_worksheet()
read_tsv = csv.reader(open(tsv_file, 'r', encoding='utf-8'), delimiter='\t')
for row, data in enumerate(read_tsv):
worksheet.write_row(row, 0, data)
workbook.close()
|
Output:
