Python Forum
Convert legacy print file to XLSX file - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Convert legacy print file to XLSX file (/thread-35222.html)



Convert legacy print file to XLSX file - davidm - Oct-10-2021

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


read legacy print file output as xlsx excel file - davidm - Oct-17-2021

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
[attachment=1333][attachment=1332]