write contents of CSV to body of email

jaykapalczynski · ‎12-16-2016

I am trying to write the contents of a csv to the body of an email and of course having issues.

I can list out the contents as such. But looking for a formatted HTML version that I can place in the body of an email...I have been through a dozen of forums and cant seem to find a simple solution and one that works..

        csv.register_dialect(
            'mydialect',
            delimiter = ',',
            quotechar = '"',
            doublequote = True,
            skipinitialspace = True,
            lineterminator = '\r\n',
            quoting = csv.QUOTE_MINIMAL)
        
        print("\n Now the output from a dictionary created from the csv file")
        with open('z_outFileRegion1.csv', 'rb') as mycsvfile:
            dictofdata = csv.DictReader(mycsvfile, dialect='mydialect')
            for row in dictofdata:
                print(row['region']+"\t "+row['county']+"\t "+row['ramp'])‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I tried this one but cant get it to work

python - Send the contents of a CSV as a table in an email? - Stack Overflow

Python send email from a csv list - Stack Overflow

Anyone have any easy solution to grab the CSV, format with HTML and attach it to the body of the email?

Below I am attaching the csv to the email but want to display the records in the email itself.

    filename = "z_outFileRegion1.csv"
    f = file(filename)

    #===BUILD EMAIL========================================
    def msgEmail():
        msg = MIMEMultipart()

        #body = "This is the message"
        #content = MIMEText(body, 'plain')
        #msg.attach(content)
        
        attachment = MIMEText(f.read())
        attachment.add_header('Content-Disposition', 'attachment', filename=filename)           
        msg.attach(attachment)

        msg['Subject'] = "Region Report " + CurrentDate

        server = smtplib.SMTP('smtp1.fff',25)
        server.set_debuglevel(1)
        server.ehlo()
        server.sendmail(FROMADDR, TOADDRS, msg.as_string())
        server.quit()
   
    #===PROCESS EMAIL======================================
    getMsgP = msgEmail()
    print getMsgP‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

jaykapalczynski · ‎12-16-2016

I can create an array, convert to string and put that in some HTML formatting and add it to the email

But I just get a huge jumbled mess of comma delimited list/string in the email...there is no formatting...

Is there a way to format the Array and add that to the email
Is there a way to parse/split the string and format that?
Is there a way to split the string on a specific "," (index) and then at least add a space between lines in the email?

date_list = mylist
date_list_string = "\n".join(date_list) # edit this to match the desired output

html = """<html>
<head></head>
<body>
<p>These are the available dates: </p>
""" + date_list_string + """"
</body>
</html>
"""

part3 = MIMEText(html, 'html')
msg.attach(part3)
‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I GET THIS:

1,King WIlliam,Lestor Manor,2016-09-21 14:41:07,yes,na,na,na,no,Human feces all over sign,Sprayed with cleaner and washed off with bucket of water,None fruther,yes,na,na,na,yes,na,na,na,no,Erosion starting from hill,None,Will continue to monitor and follow up,no,na,Mowed grass,na,yes,na,na,na,yes,na,na,na,no,Yes,na,na,na,no,na,na,na,no,na,na,na,no,na,na,na,yes,na,na,na,yes,na,na, na,yes,na,na,na,yes,na,na,na,no,No,na,na,na,Note: Fishing line recycling tube full of trash need to monitor and remove if continues to be problem ,2016-09-21 14:44:17,jkirk922,2016-11-08 18:28:54,jay.kai,3.5861196,-7.98457474 1,King & Queen,Waterfence,2016-09-21 15:46:31,no,Missing trail blazer from tree work,Replaced,None,yes,na,na,na,yes,na,na,na,yes,na,na,na,no,Pothole forming,None,Monitor and fill with cold patch,no,na,Mowed grass,na,yes,na,na,na,yes,na,na,na,no,Yes,na,na,na,no,na,na,na,no,na,na,na,no,na,na,na,yes,na,na,na,yes,na,na, na,yes,na,na,na,yes,na,na,na,no,No,na,na,na,na,2016-09-21 15:48:08,jkirk922,2016-11-08 18:28:54,jay.kai,3.59206276,-7.79871466

AT A MINIMUM WAN THIS:

1,King WIlliam,Lestor Manor,2016-09-21 14:41:07,yes,na,na,na,no,Human feces all over sign,Sprayed with cleaner and washed off with bucket of water,None fruther,yes,na,na,na,yes,na,na,na,no,Erosion starting from hill,None,Will continue to monitor and follow up,no,na,Mowed grass,na,yes,na,na,na,yes,na,na,na,no,Yes,na,na,na,no,na,na,na,no,na,na,na,no,na,na,na,yes,na,na,na,yes,na,na, na,yes,na,na,na,yes,na,na,na,no,No,na,na,na,Note: Fishing line recycling tube full of trash need to monitor and remove if continues to be problem ,2016-09-21 14:44:17,jkirk922,2016-11-08 18:28:54,jay.kapalczynski,3.5861196,-7.98457474

1,King & Queen,Waterfence,2016-09-21 15:46:31,no,Missing trail blazer from tree work,Replaced,None,yes,na,na,na,yes,na,na,na,yes,na,na,na,no,Pothole forming,None,Monitor and fill with cold patch,no,na,Mowed grass,na,yes,na,na,na,yes,na,na,na,no,Yes,na,na,na,no,na,na,na,no,na,na,na,no,na,na,na,yes,na,na,na,yes,na,na, na,yes,na,na,na,yes,na,na,na,no,No,na,na,na,na,2016-09-21 15:48:08,jkirk922,2016-11-08 18:28:54,jay.kai,3.59206276,-7.79871466

jaykapalczynski · ‎12-16-2016

Im getting close but this results in the below....

how can I make the fields widths wider so I can read it...

with open('z_outFileRegion1.csv', 'rb') as f:
    reader = csv.reader(f)
    your_list = list(reader) 
  
        #First, create the texts of the columns:
        cols = ["<td>{0}</td>". format( "</td><td>".join(t)  ) for t in your_list]

        #then use it to join the rows (tr)
        rows = "<tr>{0}</tr>".format( "</tr>\n<tr>".join(cols) )

        #finaly, inject it into the html...
        html1 = """<HTML> <body>
            <h1>Attendance list</h1>
                <table>  
                    {0}  
                </table>
            </body>  
            </HTML>""".format(rows)
        
        part3 = MIMEText(html1, 'html')
        msg.attach(part3)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Attendance list

region

county

ramp

date

trailblazers

TrailDes

TrailActionT

railActionN

regulations

RegDes

RegActionT

regActionN

safteygsign

safteygsignP

safteygsignT

safteygsignN

rutfree

rutfreeP

rutfreeT

rutfreeN

potholes

potholesP

potholesT

potholesN

grass

grassP

grassT

grassN

trash

trashP

trashT

trashN

portapotties

portapottiesP

portapottiesT

portapottiesN

lighting

lights

lightingP

lightingT

lightingN

courtesypier

courtesypierP

courtesypierT

courtesypierN

boatslide

boatslideP

boatslideTaken

boatslideN

fishingpier

fishingpierP

fishingpierT

fishingpierN

</>

debris

debrisP

debrisT

debrisN

potholesramp

potholesrampP

potholesrampT

potholesrampN

bulkhead

bulkheadPbulkheadTbulkheadNshorelineshorelinePshorelineTshorelineNcontractorcontractordutiescontractorPcontractorTcontractorNcommentsCreationDateCreatorEditDateEditorLatLong

1

King WIlliam

Lestor Manor

2016-09-21 14:41:07

yes

na

no

Human feces all over sign

Sprayed with cleaner and washed off with bucket of water

None fruther

yes

na

yes

na

no

Erosion starting from hill

None

Will continue to monitor and follow up

no

na

Mowed grass

na

yes

na

yes

na

no

Yes

na

no

na

no

na

no

na

yes

na

yes

na

yes

nananayesnanananoNo nananaNote: Fishing line recycling tube full of trash need to monitor and remove if continues to be problem 2016-09-21 14:44:17jkirk9222016-11-08 18:28:54jay.ka3.5861196-7.98457474%s

BlakeTerhune · ‎12-16-2016

This discussion is full of poop...

Anyway, since you've ventured out of printing in Python to crafting an HTML email (which is an adventure in its self), you should start looking up ways to solve your problem with HTML. For example, the col width table attribute might be something to try for setting the width of the columns in your table. Instead, I suspect you might actually want to just set the table width to 100%.

Ultimately though, you'll be limited by the screen size of the email client rendering the html message.

JamesCrandall · ‎12-19-2016

Format the list items into a merged html line then populate a complete html into a message body.

mylist = [[1, "blah", "blah2", "blah3", "blah4", "blah5"], [2, "blah33", "blah44", "blah55", "blah66", "blah77"], [3, "blah111", "blah222", "blah333", "blah444", "blah555"]]
mylistitem = (["""<a href=>""" + str(i) + """</a>""" for i in mylist])
merged = [item for sublist in zip(mylistitem) for item in sublist]
htmlline = '\n'.join(merged)
#print htmlline
html = """\
        <html>
          <head></head>
          <body>
            <p><br>
                Python Script Errors & Logging Reports<br> <br> <br>
                | Log Items | <br>
                """ + htmlline + """<br> <br>
            </p>
          </body>
        </html>
        """
print html‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

jaykapalczynski · ‎12-19-2016

When I use your code James I get this...I was looking for something that would look more formatted as line items...did I do something wrong?

with open('z_outFileRegion1.csv', 'rb') as f:
    reader = csv.reader(f)
    your_list = list(reader)

        mylistitem = (["""<a href=>""" + str(i) + """</a>""" for i in your_list])
        merged = [item for sublist in zip(mylistitem) for item in sublist]
        htmlline = '\n'.join(merged)
        #print htmlline
        html = """\
                <html>
                  <head></head>
                  <body>
                    <p><br>
                        Python Script Errors & Logging Reports<br> <br> <br>
                        | Log Items | <br>
                        """ + htmlline + """<br> <br>
                    </p>
                  </body>
                </html>
                """
        print html‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Python Script Errors & Logging Reports

| Log Items |
['region', 'county', 'ramp', 'date', 'trailblazers', 'TrailDes', 'TrailActionT', 'railActionN', 'regulations', 'RegDes', 'RegActionT', 'regActionN', 'safteygsign', 'safteygsignP', 'safteygsignT', 'safteygsignN', 'rutfree', 'rutfreeP', 'rutfreeT', 'rutfreeN', 'potholes', 'potholesP', 'potholesT', 'potholesN', 'grass', 'grassP', 'grassT', 'grassN', 'trash', 'trashP', 'trashT', 'trashN', 'portapotties', 'portapottiesP', 'portapottiesT', 'portapottiesN', 'lighting', 'lights', 'lightingP', 'lightingT', 'lightingN', 'courtesypier', 'courtesypierP', 'courtesypierT', 'courtesypierN', 'boatslide', 'boatslideP', 'boatslideTaken', 'boatslideN', 'fishingpier', 'fishingpierP', 'fishingpierT', 'fishingpierN', 'debris', 'debrisP', 'debrisT', 'debrisN', 'potholesramp', 'potholesrampP', 'potholesrampT', 'potholesrampN', 'bulkhead', 'bulkheadP', 'bulkheadT', 'bulkheadN', 'shoreline', 'shorelineP', 'shorelineT', 'shorelineN', 'contractor', 'contractorduties', 'c ontractorP', 'contractorT', 'contractorN', 'comments', 'CreationDate', 'Creator', 'EditDate', 'Editor', 'Lat', 'Long'] ['1', 'King WIlliam', 'Lestor Manor', '2016-09-21 14:41:07', 'yes', 'na', 'na', 'na', 'no', 'Human feces all over sign', 'Sprayed with cleaner and washed off with bucket of water', 'None fruther', 'yes', 'na', 'na', 'na', 'yes', 'na', 'na', 'na', 'no', 'Erosion starting from hill', 'None', 'Will continue to monitor and follow up', 'no', 'na', 'Mowed grass', 'na', 'yes', 'na', 'na', 'na', 'yes', 'na', 'na', 'na', 'no', 'Yes', 'na', 'na', 'na', 'no', 'na', 'na', 'na', 'no', 'na', 'na', 'na', 'no', 'na', 'na', 'na', 'yes', 'na', 'na', 'na', 'yes', 'na', 'na', ' na', 'yes', 'na', 'na', 'na', 'yes', 'na', 'na', 'na', 'no', 'No', 'na', 'na', 'na', 'Note: Fishing line recycling tube full of trash need to monitor and remove if continues to be problem ', '2016-09-21 14:44:17', 'jkirk922', '2016-11-08 18:28:54', 'jay.ki', '37.5861196', '-76.98457474%s'] ['1', 'King & Queen', 'Waterfence', '2016-09-21 15:46:31', 'no', 'Missing trail blazer from tree work', 'Replaced', 'None', 'yes', 'na', 'na', 'na', 'yes', 'na', 'na', 'na', 'yes', 'na', 'na', 'na', 'no', 'Pothole forming', 'None', 'Monitor and fill with cold patch', 'no', 'na', 'Mowed grass', 'na', 'yes', 'na', 'na', 'na', 'yes', 'na', 'na', 'na', 'no', 'Yes', 'na', 'na', 'na', 'no', 'na', 'na', 'na', 'no', 'na', 'na', 'na', 'no', 'na', 'na', 'na', 'yes', 'na', 'na', 'na', 'yes', 'na', 'na', ' na', 'yes', 'na', 'na', 'na', 'yes', 'na', 'na', 'na', 'no', 'No', 'na', 'na', 'na', 'na', '2016-09-21 15:48:08', 'jkirk922', '2016-11-08 18:28:54', 'jay.ki', '37.59206276', '-76.79871466%s']

JamesCrandall · ‎12-19-2016

It's really hard to tell but I'd suspect that there is some mismatch in the number of items within the arrays in the lists. I'd strip the down to make sure there are the same number of columns in each array.

jaykapalczynski · ‎12-19-2016

I tweaked this line and got the lines to separate...added 2 <br>

mylistitem = (["""<a href=>""" + str(i) + """</a><br><br>""" for i in your_list])

Questions:

Should I be expecting the Headers to show up before each value in each of the 2 record returns?
What result should I be expecting from your code?

Python Script Errors & Logging Reports

| Log Items |
region , county , ramp , date , trailblazers , TrailDes , TrailActionT , railActionN , regulations , RegDes , RegActionT , regActionN , safteygsign , safteygsignP , safteygsignT , safteygsignN , rutfree , rutfreeP , rutfreeT , rutfreeN , potholes , potholesP , potholesT , potholesN , grass , grassP , grassT , grassN , trash , trashP , trashT , trashN , portapotties , portapottiesP , portapottiesT , portapottiesN , lighting , lights , lightingP , lightingT , lightingN , courtesypier , courtesypierP , courtesypierT , courtesypierN , boatslide , boatslideP , boatslideTaken , boatslideN , fishingpier , fishingpierP , fishingpierT , fishingpierN , debris , debrisP , debrisT , debrisN , potholesramp , potholesrampP , potholesrampT , potholesrampN , bulkhead , bulkheadP , bulkheadT , bulkheadN , shoreline , shorelineP , shorelineT , shorelineN , contractor , contractorduties , co ntractorP , contractorT , contractorN , comments , CreationDate , Creator , EditDate , Editor , Lat , Long

3 , Grayson , Independence , 2016-11-23 16:31:59 , yes , None , None , None , yes , None , None , None , yes , None , None , None , yes , None , None , None , yes , None , None , None , yes , None , None , None , yes , None , None , None , yes , None , None , None , no , None , None , None , None , no , None , None , None , no , None , None , None , no , None , None , None , yes , None , None , None , yes , None , None , None , yes , None , None , None , yes , None , None , None , no , None , None , None , None , JUNK RECORD , 2016-11-23 16:32:22 , BoatGuest , 2016-11-23 16:32:22 , BoatGuest , 36.57220761 , -81.1524287%s

3 , Grayson , Bridle Creek , 2016-11-23 16:44:01 , yes , None , None , None , yes , None , None , None , yes , None , None , None , yes , None , None , None , yes , None , None , None , yes , None , None , None , yes , None , None , None , yes , None , None , None , no , None , None , None , None , no , None , None , None , yes , fdff , fggv , g b v , no , None , None , None , yes , None , None , None , yes , None , None , None , yes , None , None , None , yes , None , None , None , no , None , None , None , None , JUNK RECORD , 2016-11-23 16:44:28 , BoatGuest , 2016-11-23 16:44:28 , BoatGuest , 36.59588241 , -81.24224305%s

JamesCrandall · ‎12-19-2016

import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
mylist2 = [[1, "blah", "blah2", "blah3", "blah4", "blah5"], [2, "blah33", "blah44", "blah55", "blah66", "blah77"], [3, "blah111", "blah222", "blah333", "blah444", "blah555"]]
mylistitem = (["""<a href=>""" + str(i) + """</a>""" for i in mylist])
merged = [item for sublist in zip(mylistitem) for item in sublist]
htmlline = '\n'.join(merged)
me = "email1@myemail.com"
you = "email2@myemail.com"
# Create message container - the correct MIME type is multipart/alternative.
msg = MIMEMultipart('alternative')
msg['Subject'] = "Some Subject Line"
msg['From'] = me
msg['To'] = you
html = """\
<html>
  <head></head>
  <body>
    <p><br>
 Python Script Errors & Logging Reports<br> <br> <br>
 | Errors Items | <br>
 | Log Items | <br>
 """ + htmlline + """<br> <br>
    </p>
  </body>
</html>
"""
part2 = MIMEText(html, 'html')
msg.attach(part2)
s = smtplib.SMTP('mail.mydomain.com', 25)
s.sendmail(me, you, msg.as_string())
print "msg sent"
s.quit()‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

jaykapalczynski · ‎12-19-2016

I used your code in a separate .py file and this is what I got....Is this what you are expecting

I needed to modify the "mylist2" variable in this line, because it just said "mylist"

mylistitem = (["""<a href=>""" + str(i) + """</a>""" for i in mylist2])

RESULTS:

Python Script Errors & Logging Reports

| Errors Items |
| Log Items |
[1, 'blah', 'blah2', 'blah3', 'blah4', 'blah5'] [2, 'blah33', 'blah44', 'blah55', 'blah66', 'blah77'] [3, 'blah111', 'blah222', 'blah333', 'blah444', 'blah555']