How can I solve "ValueError: Cannot convert to Excel"? (Using Python & openpyxl)?

Question

I am scraping a price from a website using lxml and I'd like to insert that price into an existing Excel file using openpyxl. When I run the code, I get the error:

ValueError: Cannot convert ['$364'] to Excel

('$364' is the scraped price). How do I fix this? It appears that line 11 of code: sheet['A1'] = price is the problem. My entire code is below:

from lxml import html
import requests
page = requests.get('http://www.randomlengths.com/Woodwire/RL-Lbr-Pnl/')
tree = html.fromstring(page.content)
price = tree.xpath('//*[@id="main-frame"]/div/div[1]/table/tbody/tr[2]/td[2]/strong/text()')
print(price)

import openpyxl
xfile = openpyxl.load_workbook('C:/Users/noah.merkousko/randomlengthslumber.xlsx')
sheet = xfile.get_sheet_by_name('Framing Lumber')
sheet['A1'] = price
xfile.save('random lengths lumber test.xls')

Reedinationer · Accepted Answer · 2019-03-12 22:45:49Z

2

"ValueError: Cannot convert ['$364'] to Excel" is telling you the error. You are trying to place a list ['$364'] into a cell that contains values. You can fix this by either indexing when you put it in Excel or indexing when it's parsed from online.

Option 1:

price = tree.xpath('//*[@id="main-frame"]/div/div[1]/table/tbody/tr[2]/td[2]/strong/text()')[0] # index at 0

Option 2:

sheet['A1'] = price[0] # index when you put into Excel

It is worth noting that this may introduce an IndexValue error if the site is altered and the value is no longer located at '//*[@id="main-frame"]/div/div[1]/table/tbody/tr[2]/td[2]/strong/text()', but otherwise should solve your problem

answered Mar 12, 2019 at 22:45

Reedinationer

5,7841 gold badge14 silver badges33 bronze badges

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

3 Comments

Scooter Over a year ago

Hi @Reedinationer, thanks for the help here. It looks like if I combine both the options you laid out into my code, it solves the ValueError. However, it only puts "$" in the excel file. It does not put the full "$364" price into the excel file. Any suggestions?

Reedinationer Over a year ago

@Scooter You should not combine them. Using one will get the first item in the list (the string '$364'). Using the other option in conjunction with that will get the first item in the string (the '$' character). I meant you have two ways to fix your problem, and you should pick one of the options.

Scooter Over a year ago

when I only use one of the options you laid out, I still get the ValueError. Not sure why.

Jack · Accepted Answer · 2019-03-12 22:42:45Z

0

You can catch the error as an exception.

try:
    sheet['A1'] = price
except ValueError:
    print("Handling the error case")

answered Mar 12, 2019 at 22:42

Jack

544 bronze badges

1 Comment

Scooter Over a year ago

Hi @Jack, thanks for the help. Looks like that solved the ValueError, but it does not put any data into the excel file. Any idea why that might be happening?

Collectives™ on Stack Overflow

How can I solve "ValueError: Cannot convert to Excel"? (Using Python & openpyxl)?

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related