Scrape table from email and write to CSV (Removing \r\n) - Python

user_14061299 0
Viewed 214

I'm trying to scrape the table from an email and remove any special characters (\r\n etc) before writing to a csv file.

I've managed to scrape the data however the columns are wrapped in '\r\n' which I cannot remove (I'm new to this)

Table attempting to scrape:

Table - Image

Python Code:

for emailid in items:
# getting the mail content
resp, data = m.fetch(emailid, '(UID BODY[TEXT])')
text = str(data[0][1])

tree = BeautifulSoup(text, "lxml")
table_tag = tree.select("table")[0]
tab_data = [[item.text for item in row_data.select("td")]
            for row_data in table_tag.select("tr")]
print(table_tag)
for data in tab_data:
    writer.writerow(data)
    print(' '.join(data))

Results:

\r\nQuick No.\r\n \r\nOrder No=\r\n\r\n \r\nPart Number\r\n \r\nDescription\r\n \r\nUOM=\r\n\r\n \r\nOrder Qty\r\n \r\nQty Received\r\n \r\nReceived Date\r\n(dd/mm/yyyy)\r\n \r\nAdditional Information\r\n \r\nE03B1A\r\n \r\nE0015130\r\n \r\nYK71114105=\r\np>\r\n \r\nCOLOUR TOP ASSY (R)=\r\n\r\n \r\nPIECE\r\n \r\n1\r\n \r\n1\r\n \r\n06/10/2020=\r\np>\r\n \r\n \r\nE03B1E\r\n \r\nE0015134\r\n \r\nYK78804497=\r\np>\r\n \r\nDIE BUTTON=\r\np>\r\n \r\nPIECE\r\n \r\n4\r\n \r\n4\r\n \r\n06/10/2020=\r\np>\r\n \r\n

Expected Result

  • Quick No. Order No Part Number
  • nE03B1A nE0015130 nYK71114105
  • nE03B1E nE0015134 nYK78804497

Thanks in advance (This is my first post so please be gentle)

beautifulsoup python
3 Answers

to remove those, you'd want to use .strip() on those strings. So try:

tab_data = [[item.text.strip() for item in row_data.select("td")]
            for row_data in table_tag.select("tr")]

But could I suggest, just let pandas parse the table from the html:

import pandas as pd

for emailid in items:
# getting the mail content
    resp, data = m.fetch(emailid, '(UID BODY[TEXT])')
    text = str(data[0][1])
    
    table = pd.read_html(text)[0]
    df_obj = table.select_dtypes(['object'])
    table[df_obj.columns] = df_obj.apply(lambda x: x.str.strip())
    print(table)
    table.to_csv('file.csv', index=False)
user_77522960

import re

removedData = re.sub("^[a-zA-Z0-9]", "", dataForRemoveSlashNandR)

print(removedData)

user_143139660

Credit to: chitown88

for emailid in items:
# getting the mail content
resp, data = m.fetch(emailid, '(UID BODY[TEXT])')
text = str(data[0][1])

table = pd.read_html(text)[0]
df_obj = table.select_dtypes(['object'])
table[df_obj.columns] = df_obj.apply(lambda x: x.str.strip("\\r\\n"))
print(table)
table.to_csv(outfile, index=False)
user_140612990
Related