Python overwriting data in Excel

Question

I am not using Python on a daily basis and I have one error which you might help me with. I have the following code:

import xlrd
import xlwt
import xlsxwriter

string1="Last password change"
string2="Password expires"
string3="Password inactive"
string4="Account expires"
string5="Minimum number of days between password change"
string6="Maximum number of days between password change"
string7="Number of days of warning before password expires"

workbook=xlsxwriter.Workbook('/media/sf_vboxshared/sample.xlsx')
worksheet1=workbook.add_worksheet('Sheet1')

with open('sample.txt','r') as dump:
   for line in dump:
       if string1 in line:
            tail1=line.split(string1)[1]
            tail1=tail1.strip()
            tail1=tail1.lstrip(":")
 for num, line in enumerate(dump, 1):
                if string1 in line:
                     worksheet1.write(num,1,string1)
                     worksheet1.write(num,2,tail1)
with open('sample.txt', 'r') as dump2:
   for line2 in dump2:
       if string2 in line2:
            tail2=line2.split(string2)[1]
            tail2=tail2.strip()
            tail2=tail2.lstrip(":")
            for num, line2 in enumerate(dump2, 1):
                if string2 in line2:
                     worksheet1.write(num,1,string2)
                     worksheet1.write(num,2,tail2)

workbook.close()

The input looks like this(sample.txt):

rick
Last password change                                    : Feb 26, 2018
Password expires                                        : never
Password inactive                                       : never
Account expires                                         : never
Minimum number of days between password change          : 0
Maximum number of days between password change          : 99999
Number of days of warning before password expires       : 7

john
Last password change                                    : Feb 26, 2018
Password expires                                        : never
Password inactive                                       : never
Account expires                                         : never
Minimum number of days between password change          : 0
Maximum number of days between password change          : 99999
Number of days of warning before password expires       : 7

frank
Last password change                                    : Feb 26, 2018
Password expires                                        : never
Password inactive                                       : never
Account expires                                         : never
Minimum number of days between password change          : 0
Maximum number of days between password change          : 99999
Number of days of warning before password expires       : 7

romeo
Last password change                                    : Feb 26, 2018
Password expires                                        : never
Password inactive                                       : never
Account expires                                         : never
Minimum number of days between password change          : 0
Maximum number of days between password change          : 99999
Number of days of warning before password expires       : 7

The idea of this code is that I want to export the exact tabs into the Excel from the sample.txt file, but formatted properly. Basically, the : delimitates the columns. When you run this code, the second "with" is going to overwrite the info. Can someone explain me why is it this behavior and how can be fixed?

Many thanks in advance, Romain.

What is the idea of having 2 with open('sample.txt', 'r') as? — Vityata
– Vityata, Commented May 4, 2018 at 9:44
Wanted to have them separated, but not necessarily. We can do it in just one "with" section. — Romain
– Romain, Commented May 4, 2018 at 10:09

Rafał Gajda · Accepted Answer · 2018-05-04 12:01:57Z

1

The problem You have is caused by the way Python reads the files. If you open a file and start reading from it Python will return the data at the cursor location (when first opening the file cursor will be at 0, so the beginning of the file). Reading line by line will cause the cursor to jump to next line every time:

>>> with open('sample.txt','r') as dump:
    print dump.readline()
    print dump.readline()

will return:

rick
Last password change                                    : Feb 24, 2018

In your example you open the file and start reading from it line by line:

with open('sample.txt', 'r') as dump2:
   for line2 in dump2:

until you get a match with if string2 in line2:. At that point you start to enumerate through the remaining of the file but that cursor was already moved so the counter doesn't match with line actual number.

Try something like that instead:

with open('sample.txt','r') as dump:
    for num, line in enumerate(dump):
        if string1 in line:
            tail1=line.split(string1)[1]
            tail1=tail1.strip()
            tail1=tail1.lstrip(":")
            worksheet1.write(num,1,string1)
            worksheet1.write(num,2,tail1)
        elif string2 in line:
            tail2=line.split(string2)[1]
            tail2=tail2.strip()
            tail2=tail2.lstrip(":")
            worksheet1.write(num,1,string2)
            worksheet1.write(num,2,tail2)

Here is how the xslx file looks like (Last password change for rick is in row 2, Password expiration date in row 3 etc...)

edited May 4, 2018 at 12:01

answered May 4, 2018 at 11:53

Rafał Gajda

1516 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Romain Over a year ago

Gajda: Thanks a lot for your explanation and for the code. It really worked :)

Collectives™ on Stack Overflow

Python overwriting data in Excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related