# Python program to transform an Excel document, with some parsing

I wrote this to parse through an Excel doc, extract desired columns, parse certain cells and output another formatted Excel doc.

This is the original Excel doc I have condensed most of the columns for space

Here are the the results of the program that I am mostly happy with, it parses the name column and writes a unit identifier(i.e 83) along with a tower identifier(i.e 5) on a new line along with a completed date if the completed column is TRUE. I think the only thing I would change would be getting rid of the blank line under the row that has H 7

And here is the code

import xlrd
import xlwt
import re

# open and create workbook
workbookwrite = xlwt.Workbook('output.xlsx')

# open and create sheets

timelist = []
towerlist = []

i = 0
l = 0
t = 0

# iterate through rows in worksheet, get columns completed, completed_at and name.
# add to timelist[] and towerlist[] if completed == true

if cell0 == 1:
parsedvalue = re.split(', |/', cell2)
timelist.append(cell1)
towerlist.append(parsedvalue)

b = 2
g = 1
for rows in range(len(timelist)):
row = worksheetwrite.row(i)

# get current index from timelist and towerlist and assign to currenttime  and currentvalue

currenttime = timelist[t]
currentname = towerlist[t]

# get the length of the current index this will help us decide how to parse
length = len(towerlist[t])

# if length <= 2 the index is not formatted properly so we only need index 1 which is the cafe and towers together
if length <= 2:
row.write(0, currenttime)
row.write(1, currentname[1])
i += 1
l += 1

# if length = 4 index is formatted properly and we just grab the columns we need
elif length == 4:
a = 2

for values in range(0, 2):
row = worksheetwrite.row(l)
row.write(0, currenttime)
row.write(1, currentname[1])
row.write(2, currentname[a])
a += 1
i += 1
l += 1

# if length > 4 we need to iterate through each index and write them
else:

# define the length of the current cell of the excel sheet we have
lengthd = (len(currentname)+1)/2
length = int(lengthd)

for values in range(length):
# set variables that we will need to hold on to
currentcafe = currentname[g]
currenttower = currentname[b]

# if the value of the current tower is 2,4,5,6,7 we need to write the current cafe and current tower
# then move on to the next value and set that as the current cafe, we also need to jump to the
# value after the current cafe and set that as the current tower
if currenttower == "2" or currenttower == "4" or currenttower == "5" or currenttower == "6" or currenttower == "7":
row = worksheetwrite.row(l)
row.write(0, currenttime)
row.write(1, currentcafe)
row.write(2, currenttower)

b += 1
i += 1
g = b
b = g + 1
# otherwise we can just write the value fo the current cafe and current tower, advance to the next value
# which is another tower and set current tower to that
else:
row = worksheetwrite.row(l)
row.write(0, currenttime)
row.write(1, currentcafe)
row.write(2, currenttower)
b += 1
i += 1

l += 1
i += 1
t += 1
workbookwrite.save('output.xls')


This is the first time I have written anything with Python and I am fairly new to programming, any comments would be greatly appreciated.

• Could you expand the column width to fully show the "notes" column? Also, could you summarize what this transformation is for? – 200_success Jun 7 '18 at 20:55
• I updated the the pic to better show relevant columns – azryel6500 Jun 8 '18 at 14:27
• check out pandas – Maarten Fabré Jun 8 '18 at 14:30