Using xlrd module to turn xls into Python array

user_2636579 0
Viewed 995

I have a column of dates formatted as strings in Excel. I need to use them in a Python script as an array, so I used this script to convert them into an array.

import xlrd
workbook = xlrd.open_workbook('/Users/reallymemorable/Documents/output.xlsx')
worksheet = workbook.sheet_by_name('Sheet1')
num_rows = worksheet.nrows
curr_row = 0

#creates an array to store all the rows
row_array = []

while curr_row < num_rows:
    row = worksheet.row(curr_row)
    row_array += row
    curr_row += 1

print(row_array[0])

But the output is

text:'09/30/2018-09/26/2018'

instead of

09/30/2018-09/26/2018

Is there a way to address this in xlrd? Or do I need to use another module?

Here is an example input column:

09/30/2018-09/26/2018
09/25/2018-09/21/2018
09/20/2018-09/16/2018
09/15/2018-09/11/2018
09/10/2018-09/06/2018
09/05/2018-09/01/2018
08/31/2018-08/27/2018

EDIT:

I have tried to use .value to get rid of the text in this way:

while curr_row < num_rows:
    row_array.append(worksheet.row(curr_row).value)
    curr_row += 1

But I get this error:

  File "xlrd.test.py", line 13, in <module>
    row_array.append(worksheet.row(curr_row).value)
AttributeError: 'list' object has no attribute 'value'

I also tried the list comprehension method mentioned below, and got a similar error.

xlrd python
1 Answers

Instead of:

print(row_array[0])
# text:'09/30/2018-09/26/2018

Specify the value:

print(row_array[0].value)
# 09/30/2018-09/26/2018

The issue here is that row_array[0] is an xlrd cell, and to access the content of this cell, you need to add value.

To apply this change to all the cells, you can use a list comprehension:

row_array = [row.value for row in row_array]

Or you can just ensure that the array appends the actual value to begin with:

row_array.append([row.value for row in worksheet.row(curr_row)])
user_52389150
Related