Python: Creating row breaks in a list for openpyxl to recognise in .xlsx

user_10345428 0
Viewed 743

I am scraping information from a URL

I can successfully get the information into a .xlsx

It isn't in the format I want it to be in.

element_rows = []
for table_row in Elements.findAll('tr'):
    columns = table_row.findAll('td')
    output_row = []
    for column in columns:
        sub_rows = column.findAll('p')
        output_row.append('\r\n'.join(row.text for row in sub_rows))
    element_rows.append(output_row)

I feel it's something simple, but can't place it.

As it iterates through, for every 'p' I want it to create a new row.

I've been trying to use Excel syntax '\r\n' but feel this just isn't right. I've tried to append(row) but that throws errors at me

Currently it's giving me something along the lines of;

 |A    |B
1|Apple|PearOrangeBanana
2|Grape|MandarinOliveTomato

I'd like it to be

 |A    |B
1|Apple|Pear
2|     |Orange
3|     |Banana
4|Grape|Mandarin
5|     |Olive
6|     |Tomato

OK. The full code is as follows.

from bs4 import BeautifulSoup
import requests
import csv
from subprocess import Popen
import webbrowser
import re
from openpyxl import *
import tkinter as tk
import openpyxl
from itertools import zip_longest


#Variables
#Name of course
CourseName = 'AURAFA008'#input("Input Course Code: ")
#Base URL
TGAURL = 'https://training.gov.au/Training/Details/'
#.csv filename
CourseCSV = CourseName + '.csv'
CourseXLSX = CourseName + '.xlsx'
#Total URL of course
CourseURL = TGAURL + CourseName
#URL get
website_url = requests.get(CourseURL).text
#Beautiful soup work
soup = BeautifulSoup(website_url,'html.parser')
table = soup.table
#Excel Frameworks
# wb = Workbook()
wb = openpyxl.Workbook()
ws = wb.active
output_row = 1

#Open URL in browser
#webbrowser.open(CourseURL, 2)
# Define the tables I want to grab
Elements = (soup.find("h2", string="Elements and Performance Criteria")).find_next('table')
Foundation = (soup.find("h2", string="Foundation Skills")).find_next('table')
#Extract the data
Element_rows = []
for table_row in Elements.findAll('tr'):
    columns = table_row.findAll('td')
    output_row = []
    for column in columns:
        sub_rows = column.findAll('p')
        for row in sub_rows:
            output_row.append(row.get_text(separator=' '))
    Element_rows.append(output_row)

Foundation_rows = []

for table_row in Foundation.findAll('tr'):
    columns = table_row.findAll('td')
    output_row = []
    for column in columns:
        sub_rows = column.findAll('p')
        for row in sub_rows:
            output_row.append(row.get_text(separator=' '))
    Foundation_rows.append(output_row)


# Write the tables to .xlsx
Tab0 = (CourseName + 'Elements')
Tab1 = (CourseName + 'Foundation')
ws1 = wb.create_sheet(Tab0)
ws2 = wb.create_sheet(Tab1)

for row in Element_rows:
    ws1.append(row)
for row in Foundation_rows:
    ws2.append(row)
wb.remove(wb['Sheet'])
wb.save(CourseXLSX)
p = Popen(CourseXLSX, shell=True)
openpyxl beautifulsoup python-3.x python
2 Answers

I would recommend you write to your excel file as you go along. For each table row, create a list of lists containing any sub rows present. You can then use Python's zip_longest() function to return a sub entry for each row with blanks where one list is shorter than another, for example:

from itertools import zip_longest
from bs4 import BeautifulSoup
import openpyxl


html = """
<table>
  <tr>
    <td><p>a</p><p>b</p></td>
    <td><p>1</p><p>2</p><p>3</p></td>
    <td><p>d</p></td>
  </tr>
  <tr>
    <td><p>a</p><p>b</p></td>
    <td><p>1</p><p>2</p><p>3</p></td>
    <td><p>d</p></td>
  </tr>
</table>
"""

soup = BeautifulSoup(html, "html.parser")
table = soup.table

wb = openpyxl.Workbook()
ws = wb.active
output_row = 1

for table_row in table.find_all('tr'):
    cells = table_row.find_all('td')
    row = [[row.text for row in cell.find_all('p')] for cell in cells]

    for row_number, cells in enumerate(zip_longest(*row, fillvalue=""), start=output_row):
        for col_number, value in enumerate(cells, start=1):
            ws.cell(column=col_number, row=row_number, value=value)

    output_row += len(cells)

wb.save('output.xlsx')

This would give you the following output:

Excel screenshot

The enumerate() function can be used to give you an incrementing number for each entry in a list. This can be used to give you suitable row and column numbers for openpyxl cells.

user_49857330

Currently you're still putting all values into the same cell even though you're adding line breaks.

What you need to do is add new rows for each word. These will need to be of the form [None, 'Pear'] if you want the values in the second column.

user_23851330
Related