# Web scraping many URLs and writing to Excel file

I am using requests and BeautifulSoup to scrape 20000 URLs, each web page containing a table of information. Essentially each web page is like a combo, and it has several items, each item having a description. I am scraping two elements - item, description from each row in the table for all the 20000+ combos.

I will then write this information to an excel file. Each row represents a combo, with the first cell of each row containing the URL of the combo. The header of the file contains the variables item . So for a particular combo (a particular row number) and a particular item (a particular column), there is a description. Any two combos could have some item in common; they could also have some item not in common. So I want to have an exhaustive list of all items available in my excel file header.

So for each row in a web page, I first check if the item already existed in my excel header.

have = False  #boolean to check if the header already contains the name
item_position = 1  #if found, find out its column number

for cell in list(ws1.rows)[0]:

value = cell.value
val = value.encode('utf-8')
if item == val:
have = True
break
else:
item_position += 1


If so then I will note down the column number and put description accordingly; if not I will append the item to the next empty cell in the header empty_header_cell and note down the column number and put description.

However, after like less than hundred URLs scraped, the speed becomes drastically slower. I think it is both due to web page requesting as well as finding existing item? I wonder if there is any improvement to the code to speed up the process. Here is my complete code:

from lxml import html
from bs4 import BeautifulSoup
import requests
import csv
import openpyxl
from openpyxl.workbook import Workbook

ws1=wb.get_sheet_by_name('Sheet1')
#maintains the column number of the next empty cell in the excel file header

with open ('urls.csv') as f:
row_number = 2 #maintains a row number which increments after each url is scraped

for row in f_csv:
url = row[0]

ws1.cell(row=row_number, column=1).value = url
wb.save(filename="Destination.xlsx")

try:
page = requests.get(url)
web = page.text
soup = BeautifulSoup(web, 'lxml')

table = soup.find('table', {'class': "tc_table"})  #find the table in each web page that I am goinf to scrape
trs = table.find_all('tr')

for tr in trs:

ls = []
for td in tr.find_all('td'):
ls.append(td.text)
ls = [x.encode('utf-8') for x in ls]

try:
item = ls[1]
description = ls[2]

have = False  #boolean to check if the header already contains the name
item_position = 1  #if found, find out its column number

for cell in list(ws1.rows)[0]:

value = cell.value
val = value.encode('utf-8')
if item == val:
have = True
break
else:
item_position += 1

if have == True:   #if item found
ws1.cell(row=row_number, column=item_position).value = description
wb.save(filename = 'Destination.xlsx')

ws1.cell(row=1, column=empty_header_cell).value = item       #append item to the next empty header cell
wb.save(filename = 'Destination.xlsx')

except IndexError:
print("i am an IndexError")

row_number += 1  #start scraping the next url

except IndexError:  #to skip those webpages that have slightly different format so data cannot be located
print("skipping this website")
row_number += 1
except AttributeError:
print("attribute error")
row_number += 1


Let me take it from the performance perspective.

### The main bottleneck

You are scraping the pages sequentially in the blocking manner - processing urls one at a time, not proceeding to the next url until you are done with the current one.

There are a number of tools that can help to switch to an asynchronous strategy. Look into Scrapy web-scraping framework. There is also aiohttp which is based on AsyncIO.

### Gathering scraping results

I think you don't actually need an Excel writer here since you are only writing simple text data - you are not concerned with advanced data types or workbook style and formatting. Use a CSV writer - Python has a built-in csv module.

Note that if you'll switch to Scrapy, you'll get the built-in CSV exporter "for free".