topic Re: write contents of CSV to body of email in Python Questions

write contents of CSV to body of email

jaykapalczynski — Sun, 12 Dec 2021 02:22:44 GMT

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

Re: write contents of CSV to body of email

jaykapalczynski — Sun, 12 Dec 2021 02:22:46 GMT

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

Re: write contents of CSV to body of email

jaykapalczynski — Sun, 12 Dec 2021 02:22:49 GMT

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

King WIlliam

Lestor Manor

2016-09-21 14:41:07

yes

Human feces all over sign

Sprayed with cleaner and washed off with bucket of water

None fruther

yes

Erosion starting from hill

None

Will continue to monitor and follow up

Mowed grass

yes

Yes

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

Re: write contents of CSV to body of email

BlakeTerhune — Fri, 16 Dec 2016 23:22:00 GMT

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.

Re: write contents of CSV to body of email

jaykapalczynski — Mon, 19 Dec 2016 14:09:57 GMT

Geeze thanks Blake...you are so kind. I simple suggestion steering me towards printing in python would have been nice.

Is there anyone out there, someone that choose not to insult people, that have any idea of how to do this. I dont care if its HTML just thought that might be an easier option formatting wise.

Re: write contents of CSV to body of email

RonnieRichards — Mon, 19 Dec 2016 14:54:09 GMT

It takes a some time to work with but I would recommend trying the jinja2 library or alternative templating library. It has many options for looping over python objects. We have standardized our formatted email's using html and attachments. It works very well, it is much easier to copy and reuse existing templates than to understand inline print statements.

Re: write contents of CSV to body of email

BlakeTerhune — Mon, 19 Dec 2016 15:16:33 GMT

Haha, sorry! I was making a joke about the repeated occurrences of "Human feces all over sign" in your posts. My apologies for any insult taken. My suggestions are honest though, I really think you should try adding some HTML formatting to style the email.

Re: write contents of CSV to body of email

JamesCrandall — Sun, 12 Dec 2021 02:22:52 GMT

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

Re: write contents of CSV to body of email

jaykapalczynski — Sun, 12 Dec 2021 02:22:54 GMT

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

Re: write contents of CSV to body of email

JamesCrandall — Mon, 19 Dec 2016 17:53:08 GMT

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.

Re: write contents of CSV to body of email

jaykapalczynski — Mon, 19 Dec 2016 18:54:19 GMT

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

Re: write contents of CSV to body of email

JamesCrandall — Sun, 12 Dec 2021 02:22:57 GMT

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()‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: write contents of CSV to body of email

jaykapalczynski — Mon, 19 Dec 2016 19:20:55 GMT

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

Re: write contents of CSV to body of email

jaykapalczynski — Sun, 12 Dec 2021 02:22:59 GMT

Im also trying this approach...

BUT NO MATTER what I do to try and format the column widths and table widths nothing changes...

thoughts?

        ##First, create the texts of the columns:
        cols = ["<td style='width:100px'>{0}</td>".format( "<td style='width:100px'></td>".join(t)) for t in your_list]
        ##then use it to join the rows (tr)
        rows = "<tr style='width:100px'>{0}</tr>".format( "<tr style='width:100px'>\n</tr><br><br><br>".join(cols))
        ##finaly, inject it into the html...
        html1 = """<HTML> <body>
            <h1>Attendance list</h1>
                <table style="width:100%">  
                   {0}
                </table>
            </body>  
            </HTML>""".format(rows)

        part4 = MIMEText(html1, 'html')
        msg.attach(part4)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: write contents of CSV to body of email

jaykapalczynski — Mon, 19 Dec 2016 19:59:28 GMT

ok my last post seems to stem from too many fields....I narrows down to 10 adn I was able to add Style to my table being built.

Were my results from your code what you were expecting?

THANKS for all your help....much appreciated.

Re: write contents of CSV to body of email

JamesCrandall — Sun, 12 Dec 2021 02:23:02 GMT

My mistake, yes I was testing with "mylist" not "mylist2". The result I get when print hmtl:

       <html>
          <head></head>
          <body>
            <p><br>
                Python Script Errors & Logging Reports<br> <br> <br>
                | Log Items | <br>
                <a href=>[1, 'blah', 'blah2', 'blah3', 'blah4', 'blah5']</a>
                <a href=>[2, 'blah33', 'blah44', 'blah55', 'blah66', 'blah77']</a>
                <a href=>[3, 'blah111', 'blah222', 'blah333', 'blah444', 'blah555']</a><br> <br>
            </p>
          </body>
        </html>‍‍‍‍‍‍‍‍‍‍‍‍

Re: write contents of CSV to body of email

jaykapalczynski — Mon, 19 Dec 2016 21:05:46 GMT

Is there a way to add the headers in there?

Re: write contents of CSV to body of email

JamesCrandall — Tue, 20 Dec 2016 13:44:27 GMT

Is there a way to add the headers in there?

The headers as in the first row of your csv file? Just make sure your list is correctly populated from the csv.

mylist = [[0, "Header1", "Header2", "Header3", "Header4", "Header5"],[1, "blah", "blah2", "blah3", "blah4", "blah5"], [2, "blah33", "blah44", "blah55", "blah66", "blah77"], [3, "blah111", "blah222", "blah333", "blah444", "blah555"]]‍

Or do you mean attach the csv file to the message?

Re: write contents of CSV to body of email

jaykapalczynski — Sun, 12 Dec 2021 02:23:05 GMT

OK...I was able to get this working....

But my question is how can I get a bit more effective with the html formatting...Maybe a vertical table? Any ideas on how to modify this to be a vertical table?

Im not sure how to target the specific fields to put in the html formatting. Thoughts?

DESIRED_COLUMNS = ('county','ramp','date','trailblazers','regulations','safteygsign','rutfree','potholes','grass','trash','portapotties','lighting',
           'courtesypier','boatslide','fishingpier','debris','potholesramp','bulkhead','shoreline','contractor','comments','CreationDate','Creator','EditDate','Editor')

f2 = open("z_outFileRegion3.csv")
reader = csv.reader(f2)

headers2 = [1,2,3,4,8,12,16,20,24,28,32,36,41,45,49,53,57,61,65,69,74,75,76,77,78]
results2 = []
for row in reader:
    if not headers2:
        headers2 = []
        for i, col in enumerate(row):
            if col in DESIRED_COLUMNS:
                # Store the index of the cols of interest
                headers2.append(i)
    else:
        results2.append(tuple([row for i in headers2]))

#...SNIP
        cols1 = ["<td>{0}</td>".format( "<td><td/>".join(t)) for t in results2]
        rows1 = "<tr>{0}</tr>".format( "<tr><tr/><br>".join(cols1))
        html1 = """<HTML>
            <body>
            <h4>Attendance list</h4>
                <table>  
                   {0}
                </table>
            </body>  
            </HTML>""".format(rows1)

        part4 = MIMEText(html1, 'html')
        msg.attach(part4)
#...SNIP‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍