Convert a TSV file to Excel using Python
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
# Importing modules import csv from xlsxwriter.workbook import Workbook # Input file path tsv_file = 'worldcup2014.tsv' # Output file path xlsx_file = 'worldcup2014.xlsx' # Creating an XlsxWriter workbook object and adding # a worksheet. workbook = Workbook(xlsx_file) worksheet = workbook.add_worksheet() # Reading the tsv file. read_tsv = csv.reader( open (tsv_file, 'r' , encoding = 'utf-8' ), delimiter = '\t' ) # We'll use a loop with enumerate to pass the data # together with its row position number, which we'll # use as the cell number in the write_row() function. for row, data in enumerate (read_tsv): worksheet.write_row(row, 0 , data) # Closing the xlsx file. workbook.close() |
Output:
Example 2: Convert tsv to xls
TSV Used:
Code:
Python3
# Importing modules import csv from xlsxwriter.workbook import Workbook # Input file path tsv_file = 'hospital.tsv' # Output file path xlsx_file = 'hospital.xlsx' # Creating an XlsxWriter workbook object and # adding a worksheet. workbook = Workbook(xlsx_file) worksheet = workbook.add_worksheet() # Reading the tsv file. read_tsv = csv.reader( open (tsv_file, 'r' , encoding = 'utf-8' ), delimiter = '\t' ) # We'll use a loop with enumerate to pass the # data together with its row position number, which # we'll use as the cell number in the write_row() # function. for row, data in enumerate (read_tsv): worksheet.write_row(row, 0 , data) # Closing the xlsx file. workbook.close() |
Output:
Contact Us