CODEWITHSUNDEEP
pythonexcel
McD
McD

Reputation: 21

Convert .txt to .xls from nested folders

I found a Python script to convert .txt to .xls and it is working: Converting multiple tab-delimited .txt files into multiple .xls files : 

import glob
    import csv
    import xlwt
    import win32com.client as win32
for filename in glob.glob("C:\Users\MSI\Desktop\Python Lab\AGR\\*.txt"):
    spamReader = csv.reader((open(filename, 'rb')), delimiter='|',quotechar='"')
    encoding = 'latin1'
    wb = xlwt.Workbook(encoding=encoding)

    sheet=xlwt.Workbook()
    sheet = wb.add_sheet('sheet 1')
    newName = filename
    for rowx, row in enumerate(spamReader):
        for colx, value in enumerate(row):
            sheet.write(rowx, colx, value)

    wb.save(newName.replace('.txt','.xls'))

print "Done"

However, it can only convert the file path but could not convert any files in nested folders.

How I may modify it to include nested folders?

Upvotes: 0

Views: 189

Answers (1)

Martin Evans
Martin Evans

Reputation: 46779

Something like the following should work. It uses Python's os.walk function to traverse all sub folders:

import xlwt
import os

path = r'C:\Users\MSI\Desktop\Python Lab\AGR'

for root, dirs, files in os.walk(path):
    for filename in files:
        name, ext = os.path.splitext(filename)
        if ext.lower() == '.txt':
            source = os.path.join(root, filename)
            dest = os.path.join(root, name + '.xls')

            with open(source, 'rb') as f_input:
                spamReader = csv.reader(f_input, delimiter='|',quotechar='"')
                wb = xlwt.Workbook(encoding='latin1')
                sheet = xlwt.Workbook()
                sheet = wb.add_sheet('sheet 1')

                for rowx, row in enumerate(spamReader):
                    for colx, value in enumerate(row):
                        sheet.write(rowx, colx, value)

                wb.save(dest)

print "Done"

Upvotes: 1

Related Questions