Python Forum
Reading data from excel file –> process it >>then write to another excel output file
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Reading data from excel file –> process it >>then write to another excel output file
#1
Hello dear pythonians ,

I have an excel file which containers qr code each in separate line ( see photo attached)
I need to do some processing to extract the data from these QR codes then write the results in another excel file which container the final data after processing.

I have written most of the code except the final stage( write and append to final excel file) where I got stuck :woozy_face: :cold_sweat: -

Here is the code
import pandas as pd
import openpyxl
import tlv8
import base64
  
#create new excel file with header for final output
df = pd.DataFrame(
                   columns=["Seller Name","Vat Number","Invoice Date","Total Amount","Vat Amount"])
df.to_excel("final.xlsx",index=False)

# load excel containing qr code with its path for reading
wrkbk = openpyxl.load_workbook("G:\\qr.xlsx")
  
sh = wrkbk.active
  
# iterate through QR code excel file to input the data for processing
for row in sh.iter_rows():
    for cell in row:
        base64QR=cell.value
        rawByte = base64.b64decode(base64QR)
        str1 = rawByte.decode('UTF-8')  
        structure1 = {
            1: tlv8.DataType.STRING,
            2: tlv8.DataType.STRING,
            3: tlv8.DataType.STRING,
            4: tlv8.DataType.STRING,
            5: tlv8.DataType.STRING
        }
        final_output = tlv8.decode(rawByte, structure1)
        Data_List = [entry.data for entry in final_output]
        final_list = []
        for ele in Data_List:
                final_list.append(ele.strip())
        print(final_list) # final output is a list 
        # final output looks like below : 
        # ['Arnon Plastic Industries Co. Ltd.', '300498609900003', '2023-02-14', 4600.0, 600.0]
My question is :–

I want to loop over all QR codes and append data in a new excel file which contain the header details I already wrote at the beginning.

Here is an example of QR code for testing :–
ASFBcm5vbiBQbGFzdGljIEluZHVzdHJpZXMgQ28uIEx0ZC4CDzMwMDQ5ODYwOTkwMDAwMwMTMjAyMy0wMi0xNCAxMjo0ODoxMwQHNDYwMC4wMAUGNjAwLjAw

Rgds,
Jenni

Attached Files

Thumbnail(s)
   
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  "[Errno 2] No such file or directory" (.py file) IbrahimBennani 13 644 Jun-17-2024, 12:26 AM
Last Post: AdamHensley
  Reading an ASCII text file and parsing data... oradba4u 2 224 Jun-08-2024, 12:41 AM
Last Post: oradba4u
  Extracting the correct data from a CSV file S2G 6 373 Jun-03-2024, 04:50 PM
Last Post: snippsat
  Delete file with read-only permission, but write permission to parent folder cubei 6 22,418 Jun-01-2024, 07:22 AM
Last Post: Eleanorreo
  Excel isnt working properly after python function is started IchNar 2 446 May-01-2024, 06:43 PM
Last Post: IchNar
  What does .flush do? How can I change this to write to the file? Pedroski55 3 423 Apr-22-2024, 01:15 PM
Last Post: snippsat
  encrypt data in json file help jacksfrustration 1 468 Mar-28-2024, 05:16 PM
Last Post: deanhystad
  Can rich.logging output to file? pyfoo 1 443 Mar-21-2024, 10:30 AM
Last Post: pyfoo
  Updating formulas in Excel - best solution MasterOfDestr 4 867 Mar-11-2024, 01:02 PM
Last Post: MasterOfDestr
  Python openyxl not updating Excel file MrBean12 1 508 Mar-03-2024, 12:16 AM
Last Post: MrBean12

Forum Jump:

User Panel Messages

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