Python Forum
Convert legacy print file to XLSX file
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Convert legacy print file to XLSX file
#1
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

Attached Files

Thumbnail(s)
       
Reply
#2
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.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Cannot get cmd to print Python file Schauster 11 463 May-16-2024, 04:40 PM
Last Post: xMaxrayx
  How to Randomly Print a Quote From a Text File When User Types a Command on Main Menu BillKochman 13 1,202 Apr-24-2024, 05:47 AM
Last Post: Bronjer
Question [SOLVED] Correct way to convert file from cp-1252 to utf-8? Winfried 8 1,209 Feb-29-2024, 12:30 AM
Last Post: Winfried
  file open "file not found error" shanoger 8 1,314 Dec-14-2023, 08:03 AM
Last Post: shanoger
  python Read each xlsx file and write it into csv with pipe delimiter mg24 4 1,638 Nov-09-2023, 10:56 AM
Last Post: mg24
  Need to replace a string with a file (HTML file) tester_V 1 815 Aug-30-2023, 03:42 AM
Last Post: Larz60+
  How can I change the uuid name of a file to his original file? MaddoxMB 2 1,008 Jul-17-2023, 10:15 PM
Last Post: Pedroski55
  Convert File to Data URL michaelnicol 3 1,302 Jul-08-2023, 11:35 AM
Last Post: DeaD_EyE
  Python Script to convert Json to CSV file chvsnarayana 8 2,640 Apr-26-2023, 10:31 PM
Last Post: DeaD_EyE
  Reading data from excel file –> process it >>then write to another excel output file Jennifer_Jone 0 1,168 Mar-14-2023, 07:59 PM
Last Post: Jennifer_Jone

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020