Convert legacy print file to XLSX file

[davidm]

Hi, I have a project to convert a print file (attached) to xlxs format. I've created the Excel headers (attached) and would like to append the columns A - N from the print file to the Excel header file - and save as new name.
Tried with xlsxWriter and

for line in lines:
print(line.split())

but without success, think I need re but can't get my head around how that works.
Any help much appreciated

[davidm]

Hi, I'm new to Python and have a project to create an excel file from a legacy print file.
After much Googling I've managed to tack together the following code:

import os, csv, re, xlsxwriter import pandas as pd from itertools import islice from pandas.io.excel import ExcelWriter # input legacy print file dir_name='/aria/prn2excel/' base_filename='s285ibud' filename_suffix='prn' # output file formatted as csv csvfile = open('s285ibud.csv', 'w') # input print line list of field/column widths n = [11, 12, 4, 14, 14, 4, 12, 12, 4, 12, 4, 14, 18, 7] with open(dir_name + base_filename + "." + filename_suffix, 'r', encoding='cp862') as f: lines = f.readlines() # proccess print lines # proccess only lines that terminate with 4 digits for line in lines: line = line.rstrip('\n') if not line[-4:].isdigit(): continue # split line into comma separated chunks it = iter(line) line = ','.join(''.join(islice(it, None, x)) for x in n) # output lines in csv format csvfile.write(line + '\n') csvfile.close() # open previously writen output as input csvfile = open('s285ibud.csv', 'r') # write xlsx file from above input with ExcelWriter('test_excel.xlsx') as ew: pd.read_csv(csvfile).to_excel(ew) csvfile.close()

I've also attached images of the input and output files.
What would be the correct version of the above code and how do I format the excel data so that decimal places are preserved and dates are formatted without error.
Any help much appreciated
       

buran write Oct-17-2021, 06:23 AM:
Please, don't start new threads unnecessarily. This time I merged the two threads.