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.