Python Code that reads a set of text file(.txt) and Converts it to an Excel File.

Given a set of .txt files, that contains something that looks like this:



 and you want to write a code to read the content of the file, and write it into an excel table,
you can simply follow the python code below and edit it to fit your work. 


import os
import re
import xlsxwriter
    
path = 'The path to where the .txt files are'
listing = os.listdir(path)
workbook = xlsxwriter.Workbook("Bank Validation.xlsx")
#bold = workbook.add_format({'bold' : True})
format = workbook.add_format()  #used to format a set of cells
format2 = workbook.add_format() #used to format a set of cells
format3 = workbook.add_format()
format4 = workbook.add_format()
for infile in listing:
  dir_item_path = os.path.join(path, infile)
  fh = open(dir_item_path,'r')
    Fname = infile
    Lname = Fname.split('.')[0]
    worksheet = workbook.add_worksheet(Lname)
    worksheet.set_column(0, 5, 22)
    '''
    worksheet.set_column is used to set the column cells from first  column 1,
    indexed as 0 to the 6th column, indexed as 5.
    '''
    #format.set_align('vcenter')
    format.set_indent(2)  #used to set the indent to 2
    #format2.set_indent(2)
    format2.set_align('center')
    format2.set_bold(1)
    format3.set_indent(6)
    format4.set_indent(4)
    col = 0
    row = 1
    worksheet.write('A1', 'ACCOUNT', format2)
    worksheet.write('B1', 'COUNTRY', format2)
    worksheet.write('C1', 'DIGITS', format2)
    worksheet.write('D1', 'BILLs', format2)
    worksheet.write('E1', 'MOUNT', format2)
    worksheet.write('F1', 'OTHER', format2)
    '''
    The above 6 columns writes into the first row of any current worksheet, and
    the row is indexed as 0, and it takes the formats assigned to it.
    Thus,worksheet.write is used to write into an excel sheet.
    '''
    for line in fh:
         space_remove = re.sub(r"\s+",",",line.rstrip())
         #Check for third argument of sub
         split_Line = space_remove.split(",") #ruturns a list split_Line
         worksheet.write(row, col, split_Line[0], format)
         worksheet.write(row, col + 1, split_Line[1], format3)
         worksheet.write(row, col + 2, split_Line[2], format)
         worksheet.write(row, col + 3, split_Line[3], format)
         worksheet.write(row, col + 4, split_Line[4], format4)
         worksheet.write(row, col + 5, split_Line[5], format3)
         row += 1
         '''
         The above 6 lines of worksheeet.write is used to columns of our worksheet
         and the row += takes the writing to the next row
         '''
       
workbook.close()

The above code will do the reading of the .txt files, and write it into an excel booklet for you.
I hope this tutorial was helpful, and understandable.
Note: this code can convert several .txt files in a folder, into an excel file once.
You also have to download and install the xlsxwriter before you can use it.   

For questions and other assistance, you can contact me via anikenneth333@gmail.com  .
I LOVE YOU.
Best Regards.