Writing to different columns in Excel from python

Question

I'm using Python Selenium webdriver to scrape a web table by iterating over the rows in the table, and CSV module to write to excel.

When printing in python my scraped web table shows
['PS208\n43:51\nOUTBOUND\nFDEX\n708135']
['PS207\n01:24\nINBOUND\nUPSS\n889058']
['PS206\n12:34\nOTHER\nFDEG\n506796']

when writing to excel csv

all the data goes into column A

1 PS208 2 43:51 3 OUTBOUND 4 FDEX 5 708135 6 7 PS207 8 01:24 9 INBOUND 10 UPSS 11 889058

I need each piece of data between the \n to be on a different column and each block of data between the ' to be on a different row.

 A     B      C      D      E

1 'PS208 43:51 OUTBOUND FDEX 708135' 2 'PS207 01:24 INBOUND UPSS 889058'

import csv
import time
from selenium import webdriver


# ****Logging****
# Current time
now = ("Run Time = " + time.ctime())
print(now)

# ****Visible Chrome Browser****
# Path to Chrome exe
chrome_path = r"C:\Users/userr\Desktop\chromedriver.exe"
# Chrome as browser
browser = webdriver.Chrome(chrome_path)
# URL to open
url = "https://somewebsite"
browser.maximize_window()
browser.get(url)
time.sleep(5)

# Open csv to write to
outfile = open("YMS.csv", "w")
# Parse by spaces
writer = csv.writer(outfile,  delimiter=" ")

# ******Query******
# Select "empty trailers"
browser.find_element_by_xpath("""//*[@id="empty_checkbox"]""").click()
time.sleep(5)
table = browser.find_element_by_xpath("""//*[@id="ship-clerk-   dashboard-      table"]""")
rows = table.find_elements_by_tag_name("""tr""")
for row in rows:
    foo = row.text
    print([foo])
    writer.writerow([foo])

# Close CSV file
outfile.close()
# Close browser
browser.close()

Put comma's instead of \n? (possibly quote the contents ') \n should be the row seperator... — ebyrob
– ebyrob, Commented Sep 26, 2016 at 20:22

Spiritz · Accepted Answer · 2016-09-26 20:58:11Z

1

import csv
output = ['PS208\n43:51\nOUTBOUND\nFDEX\n708135']
output = output[0].split('\n')
with open(output_file, 'ab') as csvfile:
    writer = csv.writer(csvfile)
    writer.writerow(output)

Each row can be written into coulmns like this. Adding a loop for each block of data and splitting each of them should work in your case.

answered Sep 26, 2016 at 20:58

Spiritz

831 silver badge7 bronze badges

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

2 Comments

Jason Baker Over a year ago

That format only appears in my python IDE when printing ['PS208\n43:51\nOUTBOUND\nFDEX\n708135']. Excel breaks it up as different rows I'm guessing due to the \n. I've added my code above is there a way to add the split(\n)

Jason Baker Over a year ago

Thank you Spiritz I changed bork = row.text to bork = row.text.split('\n')

Collectives™ on Stack Overflow

Writing to different columns in Excel from python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related