reading text file and bring data in excel/csv using Python

Question

I have a text file which looks like this image:

I would like to convert it to the excel format using python. once done it will look like this image

Please guide me how to proceed.

http://stackoverflow.com/questions/13437727/python-write-to-excel-spreadsheet — Astrom, Apr 14 '17 at 21:53
Paste examples here instead of showing pngs... and make them small, representative samples instead of a whole big thing. It looks like you could use a dictionary of lists, then parse the file by splitting lines and filling it in. Now, the items of the dictionary, with a little tweaking, are what you want. — tdelaney, Apr 14 '17 at 21:57
thank you guys...future questions will be better formatted..promise :)...thanks for the hint..i will give it a shot — Syed, Apr 14 '17 at 22:09

score 0 · Answer 1 · edited Jan 29 '20 at 21:11

The following should work:

import csv

directions = ["***LEFT", "***RIGHT", "***UPPER", "***BOTTOM", "***Average"]

with open(r"foo.txt", "r") as f:
    data = f.readlines()

with open(r"foo.csv", "w", newline="") as csvfile:        
    writer = csv.writer(csvfile)
    writer.writerow(["SN"] + ["Item"] + [line.split()[-1] for line in data[::6]])
    writer.writerow(["C23ABC"] + ["Position"] + [" ".join(line.split()[:-1]) for line in data[::6]])
    for i in range(5):
        writer.writerow([""] + [directions[i]] + [line.split()[0] for line in data[i+1::6]])

However, spaces or leading zeroes can cause problems.

reading text file and bring data in excel/csv using Python

1 Answers1