CODEWITHSUNDEEP
pythonexcelopenpyxl
Zeeebass
Zeeebass

Reputation: 37

How to check for each column if row is empty?

I created an simple program that retrieves data from a .txt file. A small part of the .txt file:

screenshot

The 4 digit number between the ( and ) and the hexadecimal between < and > is the data, I am referring to. After I sliced it, I want to write the data into an excel file.

I wrote (an ugly) code that will remember the row number and write the data into a cell below for each column (9 columns in total)

Is there a better way to get the same result? I would like to improve.

import openpyxl

infile = open('test.txt')

s = infile.readlines()

wb = openpyxl.load_workbook(filename='Main.xlsx')
ws = wb.active
VariableRow1 = 2
VariableRow2 = 2
VariableRow3 = 2
VariableRow4 = 2
VariableRow5 = 2
VariableRow6 = 2
VariableRow7 = 2
VariableRow8 = 2
VariableRow9 = 2
for i in range(len(s)):
    if '<' in s[i] and '(' in s[i]:

        PijlOpen = s[i].find('<')
        PijlDicht = s[i].find('>')
        HaakjeOpen = s[i].find('(')
        HaakjeDicht = s[i].find(')')
        Number = (s[i])[HaakjeOpen + 1:HaakjeDicht]
        Number = Number.replace('0', '')

        Value = (s[i])[PijlOpen + 1:PijlDicht]

        Value = Value.replace(' ', '')
        Value = bytes.fromhex(Value).decode('utf-8')
        Number = int(Number)
        
        if Number == 1:
            if ws.cell(row=VariableRow1, column=Number).value != None:
                VariableRow1 += 1
            cell = ws.cell(row=VariableRow1, column=Number)
            cell.value = Value
        if Number == 2:
            if ws.cell(row=VariableRow2, column=Number).value != None:
                VariableRow2 += 1
            cell = ws.cell(row=VariableRow2, column=Number)
            cell.value = Value
        if Number == 3:
            if ws.cell(row=VariableRow3, column=Number).value != None:
                VariableRow3 += 1
            cell = ws.cell(row=VariableRow3, column=Number)
            cell.value = Value
            
        if Number == 4:
            if ws.cell(row=VariableRow4, column=Number).value != None:
                VariableRow4 += 1
            cell = ws.cell(row=VariableRow4, column=Number)
            cell.value = Value
        if Number == 5:
            if ws.cell(row=VariableRow5, column=Number).value != None:
                VariableRow5 += 1
            cell = ws.cell(row=VariableRow5, column=Number)
            cell.value = Value
        if Number == 6:
            if ws.cell(row=VariableRow6, column=Number).value != None:
                VariableRow6 += 1
            cell = ws.cell(row=VariableRow6, column=Number)
            cell.value = Value
            
        if Number == 7:
            if ws.cell(row=VariableRow7, column=Number).value != None:
                VariableRow7 += 1
            cell = ws.cell(row=VariableRow7, column=Number)
            cell.value = Value
        if Number == 8:
            if ws.cell(row=VariableRow8, column=Number).value != None:
                VariableRow8 += 1
            cell = ws.cell(row=VariableRow8, column=Number)
            cell.value = Value
        if Number == 9:
            if ws.cell(row=VariableRow9, column=Number).value != None:
                VariableRow9 += 1
            cell = ws.cell(row=VariableRow9, column=Number)
            cell.value = Value

        wb.save(filename='Main.xlsx')
print ('Finished')

Upvotes: 0

Views: 676

Answers (1)

Mike67
Mike67

Reputation: 11342

You code can be reduced by using a list instead of the 9 variables:

Try this code:

import openpyxl

infile = open('test.txt')

s = infile.readlines()

wb = openpyxl.load_workbook(filename='Main.xlsx')
ws = wb.active
VariableRow = [2]*9  # index 0-8

for i in range(len(s)):
    if '<' in s[i] and '(' in s[i]:

        PijlOpen = s[i].find('<')
        PijlDicht = s[i].find('>')
        HaakjeOpen = s[i].find('(')
        HaakjeDicht = s[i].find(')')
        Number = (s[i])[HaakjeOpen + 1:HaakjeDicht]
        Number = Number.replace('0', '')

        Value = (s[i])[PijlOpen + 1:PijlDicht]

        Value = Value.replace(' ', '')
        Value = bytes.fromhex(Value).decode('utf-8')
        Number = int(Number)
        
        if number in range(1,10):  # 1-9
            if ws.cell(row=VariableRow[number-1], column=Number).value != None:
                VariableRow[number-1] += 1
            cell = ws.cell(row=VariableRow[number-1], column=Number)
            cell.value = Value
        
        wb.save(filename='Main.xlsx')
print ('Finished')

Upvotes: 1

Related Questions