Reading data from Excel sheets and building SQL statements, writing to output file in Python

Question

I have an excel book with a couple of sheets. Each sheet has two columns with PersonID and LegacyID. We are basically trying to update some records in the database based on personid. This is relatively easy to do TSQL and I might even be able to get it done pretty quick in powershell but since I have been trying to learn Python, I thought I would try this in Python. I used xlrd module and I was able to print update statements. below is my code:

import xlrd

book = xlrd.open_workbook('D:\Scripts\UpdateID01.xls')
sheet = book.sheet_by_index(0)

myList = []
for i in range(sheet.nrows):
   myList.append(sheet.row_values(i))


outFile = open('D:\Scripts\update.txt', 'wb')

for i in myList:
    outFile.write("\nUPDATE PERSON SET LegacyID = " + "'" + str(i[1]) + "'" + " WHERE personid = " + "'" + str(i[0])
                  + "'")

Two problems - when I read the output file, I see the LegacyID printed as float. How do I get rid of .0 at the end of each id? Second problem, python doesn't print each update statement in a new line in the output text file. How to I format it?

Edit: Please ignore the format issue. It did print in new lines when I opened the output file in Notepad++. The float issue still remains.

chrisfs · Accepted Answer · 2013-06-05 07:36:51Z

1

Can you turn the LegacyID into ints ?

i[1] = int(i[1])

outFile.write("\nUPDATE PERSON SET LegacyID = " + "'" + str(i[1]) + "'" + " WHERE personid = " + "'" + str(i[0]) + "'")

answered Jun 5, 2013 at 7:36

chrisfs

6,3436 gold badges33 silver badges36 bronze badges

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

1 Comment

RK Kuppala Over a year ago

Thanks, chris. When I first tried the int conversion, it said invalid literal for int() with base 10: LegacyID. I realized that I need to skip the first row which has column names and adjusted range(1, sheet.nrows) and it worked. Thanks again!

abhishekgarg · Accepted Answer · 2013-06-05 07:33:58Z

1

try this..

# use 'a' if you want to append in your text file
outFile = open(r'D:\Scripts\update.txt', 'a')

for i in myList:
    outFile.write("\nUPDATE PERSON SET LegacyID = '%s' WHERE personid = '%s'" %( int(i[1]), str(i[0])))

answered Jun 5, 2013 at 7:33

abhishekgarg

1,4639 silver badges14 bronze badges

Comments

Ber · Accepted Answer · 2013-06-05 08:21:07Z

1

Since you are learning Python (which is very laudable!) you should start reading about string formatting in the Python docs. This is the best place to start whenever you have a question light this.

Hint: You may want to convert the float items to integers using int().

edited Jun 5, 2013 at 8:21

answered Jun 5, 2013 at 7:34

Ber

42k16 gold badges79 silver badges90 bronze badges

Collectives™ on Stack Overflow

Reading data from Excel sheets and building SQL statements, writing to output file in Python

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related