write contents of CSV to body of email

14735
18
12-16-2016 08:04 AM
jaykapalczynski
Frequent Contributor

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‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
0 Kudos
18 Replies
jaykapalczynski
Frequent Contributor

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...

  1. Is there a way to format the Array and add that to the email
  2. Is there a way to parse/split the string and format that?
  3. 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

0 Kudos
jaykapalczynski
Frequent Contributor

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

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

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

0 Kudos
BlakeTerhune
MVP Regular Contributor

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.

0 Kudos
JamesCrandall
MVP Frequent Contributor

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
0 Kudos
jaykapalczynski
Frequent Contributor

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'] 

0 Kudos
JamesCrandall
MVP Frequent Contributor

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.

0 Kudos
jaykapalczynski
Frequent Contributor

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

0 Kudos
JamesCrandall
MVP Frequent Contributor
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()
0 Kudos
jaykapalczynski
Frequent Contributor

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']

0 Kudos